SQL 数据类型|极客笔记

本文介绍 SQL 中的基本数据类型以及它们在各种数据库中的实现，同时分析了选择数据类型时的一些通用的原则。需要注意的是，同一数据类型在不同数据库中支持的范围大小和精确度可能不同；因此，使用任何数据类型之前都应该查看相关的数据库文档。

常见数据类型

字段的数据类型定义了该字段能够存储的数据值，以及允许执行的操作。下图列出了 SQL 中常见的数据类型，包括字符串类型、数字类型、日期时间类型以及二进制类型。

SQL 数据类型

我们分别在第 8 篇、第 9 篇以及第 10 篇介绍了与这些数据类型相关的函数和类型转换函数。

字符串类型

字符串类型用于存储字符和字符串数据，主要包含三种具体的类型：定长字符串、变长字符串以及字符串大对象。各种数据库对于字符串类型的支持如下：

数据类型	Oracle	MySQL	SQL Server	PostgreSQL
定长字符串	CHAR(n)	CHAR(n)	CHAR(n)	CHAR(n)
变长字符串	VARCHAR2(n)	VARCHAR(n)	VARCHAR(n)	VARCHAR(n)
字符串大对象	CLOB	TEXT	VARCHAR( MAX )	TEXT

定长字符串

CHAR(n) 表示长度固定的字符串，其中 n 表示字符串的长度。常见的定义方式包括：

CHAR，长度为 1 的字符串，只能存储 1 个字符；
CHAR(5)，长度为 5 的字符串。

对于定长字符串，如果输入的字符串长度不够，将会使用空格进行填充。例如类型为 CHAR(5) 的字段，如果输入值为“A”，实际存储的内容为“A####”；# 代表空格，也就是一个字符“A”加上 4 个空格。

CHARACTER 和 CHAR 是同义词，可以通用。

通常来说，只有存储固定长度的数据时，才会考虑使用定长字符串类型。例如 18 位身份证，6 位邮政编码等。

变长字符串

VARCHAR(n) 表示长度不固定的字符串，其中 n 表示允许存储的最大长度。

对于变长字符串，如果输入的字符串长度不够，存储实际的内容。例如类型为 VARCHAR(5) 的字段，如果输入值为“A”，实际存储的内容为“A”。

CHAR VARYING 和 CHARACTER VARYING 是 VARCHAR 的同义词，可以通用。 Oracle 中使用 VARCHAR2 表示变长字符串类型。

变长字符串类型一般用于存储长度不固定的内容，例如名字、电子邮箱、产品描述等。

字符串大对象

CLOB 表示字符串大对象，通常用于存储普通字符串类型无法支持的更长的字符串数据。例如整篇文章、备注、评论等。

Oracle 使用 CLOB 类型存储大型字符串；MySQL 提供了 TINYTEXT、TEXT、MEDIUMTEXT 以及 LONGTEXT 分别用于存储不同长度的文本数据；SQL Server 使用 VARCHAR( MAX ) 存储大文本数据；PostgreSQL 提供了 TEXT 类型存储任意长度的字符串。

CHARACTER LARGE OBJECT 和 CHAR LARGE OBJECT 是 CLOB 的同义词，可以通用。

在 SQL 中，输入字符串类型的常量和数据时，需要使用单引号引用：

'S001'
'张飞'
'13512345678'

数字类型

数字类型主要分为两类：精确数字和近似数字。各种数据库对于数字类型的支持如下：

数据类型	Oracle	MySQL	SQL Server	PostgreSQL
精确数字	SMALLINT INTEGER NUMERIC(p, s)	SMALLINT INTEGER BIGINT NUMERIC(p, s)	SMALLINT INTEGER BIGINT NUMERIC(p, s)	SMALLINT INTEGER BIGINT NUMERIC(p, s)
近似数字	BINARYFLOAT BINARYDOUBLE	FLOAT DOUBLE PRECISION	REAL DOUBLE PRECISION	REAL DOUBLE PRECISION

精确数字

精确数字类型用于存储整数或者包含固定小数位的数字。

其中，SMALLINT、INTEGER 和 BIGINT 都可以表示整数。对于 MySQL、SQL Server 以及 PostgreSQL，SMALLINT 支持 -32768 ~ 32767；INTEGER 支持 -2147483648 ~ 2147483647；BIGINT 支持 -2^63^ ~ 2^63^-1。Oracle 中的 SMALLINT 和 INTEGER 都是NUMBER(38,0) 的同义词；Oracle 不支持 BIGINT 关键字。

INT 是 INTEGER 的同义词，可以通用。 MySQL 中还提供了 TINYINT，支持 -128 ~ 127；MEDIUMINT 支持 -8388608 ~ 8388607。另外，MySQL 中的所有整型分为有符号类型（例如 INTEGER、INTEGER SIGNED）和无符号类型（例如 INTEGER UNSIGNED），无符号整型支持的正整数范围扩大了一倍。

NUMERIC(p, s) 和 DECIMAL(p, s) 可以看作相同的类型，用于存储包含小数的精确数字。

其中，精度 p 表示总的有效位数，刻度 s 表示小数点后允许的位数。例如，123.04 的精度为 5，刻度为 2。p 和 s 是可选的，s 为 0 表示整数。SQL 标准要求 p ≥ s ≥ 0 并且 p > 0。

DEC 是 DECIMAL 的同义词，可以通用。 Oracle中的 NUMERIC 和 DECIMAL 都是 NUMBER 的同义词。

整数类型通常用于存储数字 id、产品数量、课程得分等数字；NUMERIC 用于存储产品价格、销售金额等包含小数并且准确度要求高的数据。

近似数字

近似数字也称为浮点型数字，一般使用较少，主要用于科学计算领域。

REAL 表示单精度浮点数，通常精确到小数点后 6 位；DOUBLE PRECISION 表示双精度浮点数，通常精确到小数点后 15 位。浮点数运算更快，但是可能丢失精度；浮点数的比较运算可能会导致非预期的结果。

Oracle 使用 BINARY_FLOAT 和 BINARY_DOUBLE 表示浮点数。 MySQL 使用 FLOAT 表示单精度浮点数，同时区分有符号和无符号的浮点数。

在 SQL 中，输入数字类型的常量和数据时，常见的方法如下：

10
10.5
10.5E+3 -- 科学计数法，105000
10.5F -- 单精度浮点数
10.5D -- 双精度浮点数

日期时间类型

我们在第 10 篇中介绍了日期和时间数据的存储方式，在此简单回顾一下。与日期和时间相关的数据类型包括：DATE、TIME 以及 TIMESTAMP。

DATE 存储年、月、日；TIME 存储时、分、秒，以及秒的小数部分；TIMESTAMP 同时包含年、月、日、时、分、秒，以及秒的小数部分。

Oracle 中的 DATE 类型包含了额外的时、分、秒，不支持 TIME 类型。 SQL Server 使用 DATETIME2 和 DATETIMEOFFSET 表示时间戳。 MySQL 还支持 DATETIME 表示时间戳。

如果存储日期信息，例如生日，可以使用 DATE 类型；如果需要更高的时间精度，例如订单时间、发车时间等，可以使用 TIMESTAMP 类型；TIME 类型使用较少。

在 SQL 中，输入日期时间类型的常量和数据时，常见的方法如下：

'2019-12-25'
DATE '2019-12-25'
'13:30:15'
TIME '13:30:15'
'2019-12-25 13:30:15'
TIMESTAMP '2019-12-25 13:30:15'

二进制类型

二进制类型用于存储二进制数据，例如文档、图片，视频等。二进制类型具体包含以下三种：

BINARY(n)，固定长度的二进制数据，n 表示二进制字符数量；
VARBINARY(n)，可变长度的二进制数据，n 表示支持的最大二进制字符数量；
BLOB，二进制大对象。

Oracle 支持 BLOB 二进制类型；MySQL 支持 BINARY、VARBINARY 以及 TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB 二进制类型；SQL Server 支持 BINARY、VARBINARY 以及 VARBINARY ( MAX ) 二进制类型；PostgreSQL 支持 BYTEA 二进制类型。