本篇介绍SQL:2016(ISO/IEC 9075:2016)标准中定义的数据类型,以及六种主流数据库中的数据类型实现及差异,包括:Oracle、MySQL、Microsoft SQL Server、PostgreSQL、Db2、SQLite。
数据类型概述
关系型数据库中,用于存储数据的主要对象是表(table),表由行(row)和列(column)组成。
数据行,也称为记录(record)、元组(tuple),是二维表格中的行。表中的一行对应现实世界中的一个实体,例如上表中的第一行(employee_id = 100)记录了员工 Steven King 的相关信息。
列,也称为属性(attribute)、字段(field),代表表中特定属性的所有信息,例如上表中的第二列表示所有员工的名(first_name)。
表中的每个列都拥有一个数据类型,数据类型定义了该列能够存储的数据值,以及允许执行的操作。常见的数据类型包括字符类型、数字类型、日期时间类型等。其中,数字类型可以进行加减乘除等数学运算,日期时间类型可以进行格式化显示。
上图是employees表的字段结构。
SQL标准中定义的数据类型可以分为三类(SQL:2016标准概念之数据类型):
- 预定义数据类型(predefined data type)
- 构造数据类型(constructed data type)
- 自定义数据类型(user-defined data type)
预定义数据类型
预定义数据类型通常也称为内置数据类型,SQL:2016标准中规定了以下预定义数据类型:
为了便于引用,将以上数据类型分为多个类别:
-
CHARACTER
、CHARACTER VARYING
以及CHARACTER LARGE OBJECT
统称为字符串类型,它们存储的值为字符串。 -
BINARY
、BINARY VARYING
以及BINARY LARGE OBJECT
统称为二进制串类型,它们存储的值为二进制串。 -
CHARACTER LARGE OBJECT
以及BINARY LARGE OBJECT
统称为大对象串类型,它们存储的值为大对象串。 -
NUMERIC
、DECIMAL
、DECFLOAT
、SMALLINT
、INTEGER
以及BIGINT
统称为精确数字类型。 -
FLOAT
、REAL
以及DOUBLE PRECISION
统称为近似数字类型。精确数字类型和近似数字类型统称为数字类型。 -
TIME WITHOUT TIME ZONE
以及TIME WITH TIME ZONE
统称为时间类型(不包含时区的时间和包含时区的时间)。 -
TIMESTAMP WITHOUT TIME ZONE
以及TIMESTAMP WITH TIME ZONE
统称为时间戳类型(不包含时区的时间戳和包含时区的时间戳)。 -
DATE
、TIME
以及TIMESTAMP
统称为日期时间类型,它们存储的值为日期时间。 -
INTERVAL
称为时间间隔类型,存储的值为时间间隔。 -
DATALINK
称为数据链接类型,用于访问存储在数据库之外的资源。 -
XML
称为XML类型,存储的值为XML值。
字符串类型
定长字符串
SQL标准中关于固定长度字符串的定义如下:
CHARACTER [ ( n ) ]
或者
CHAR [ ( n ) ]
其中,CHARACTER
和CHAR
是同义词,表示固定长度字符串;n 表示字符串的长度。
常见的定义方式包括:
- CHARACTER,长度为1个字符的字符串
- CHAR(10),长度为10个字符的字符串
对于固定长度字符串,如果输入的字符串长度不够,将会使用空格进行填充。
例如,对于类型为CHAR(10)的字段,如果输入值为“12345”,实际存储的结果为“12345#####”,#代表空格。
六种主流数据库都在一定程度上兼容SQL标准,同时也存在细微差别,具体语法如下表(CHAR也可以写成CHARACTER)。
数据库 | 定长字符串 | 描述 |
---|---|---|
Oracle | CHAR [ ( n ) ] | n可以表示字节数或者字符数,最大值为2000,参考Oracle文档 |
MySQL | CHAR [ ( n ) ] | n表示字符数,取值范围为0~255;默认值为1 |
SQL Server | CHAR [ ( n ) ] | 非Unicode字符,n表示字节数,取值范围为1~8000 |
PostgreSQL | CHAR [ ( n ) ] | n表示字符个数,最大值为10485760,默认值为1 |
Db2 | CHAR [ ( n ) ] | n表示字符个数,对于字符单元OCTETS,取值为1~255;对于CODEUNITS32,取值为1~63 |
SQLite | CHAR [ ( n ) ] | SQLite采用动态类型系统,存储类型为TEXT,参考SQLite文档 |
在实际使用时,应该尽量避免使用定长字符串类型。
变长字符串
变长字符串类型用于存储长度不固定的字符串,例如名字。SQL标准中关于可变长度字符串的定义如下:
CHARACTER VARYING [ ( n ) ]
或者
CHAR VARYING [ ( n ) ]
或者
VARCHAR [ ( n ) ]
其中,n 表示字符串的最大长度。
常见的定义方式包括:
- CHARACTER VARYING(10),最多长度为10个字符的字符串
- CHAR VARYING(10),同上
- VARCHAR(10),同上
对于可变长度字符串,如果输入的字符串长度不够,存储实际的内容。
例如,对于类型为VARCHAR(10)的字段,如果输入值为“12345”,实际存储的结果为“12345”。
六种主流数据库都在一定程度上兼容SQL标准,同时也存在细微差别,具体语法如下表(只给出VARCHAR写法)。
数据库 | 变长字符串 | 描述 |
---|---|---|
Oracle | VARCHAR2 [ ( n ) ] | n可以表示字节数或者字符数,最大值为4000字节或者32767字节(Oracle 12C) VARCHAR目前是VARCHAR2的同义词,但是将来可能重定义为另一个数据类型 |
MySQL | VARCHAR [ ( n ) ] | n表示字符数,取值范围为0~65535;n的最大值和字符集有关,例如utf8,最大值为21844 |
SQL Server | VARCHAR [ ( n ) ] | 非Unicode字符,n表示字节数,取值范围为1~8000 |
PostgreSQL | VARCHAR [ ( n ) ] | n表示字符个数,最大值为10485760;如果不指定n,可以存储任意长度字符串 |
Db2 | VARCHAR [ ( n ) ] | n表示字符个数,对于字符单元OCTETS,最大值为32672;对于CODEUNITS32,最大值为8168 |
SQLite | VARCHAR[ ( n ) ] | SQLite采用动态类型系统,存储类型为TEXT,参考SQLite文档 |
字符串大对象
字符串大对象通常用于存储普通字符串类型无法支持的更长的字符串数据。
SQL标准中关于字符串大对象的定义如下:
CHARACTER LARGE OBJECT [ ( n ) ]
或者
CHAR LARGE OBJECT [ ( n ) ]
或者
CLOB [ ( n ) ]
其中,n 表示字符串的最大长度。
六种主流数据库的具体语法如下表。
数据库 | 字符串大对象 | 描述 |
---|---|---|
Oracle | CLOB | 不支持指定最大值,最大值为( 字节) * LOB存储参数CHUNK值;Oracle只能使用关键字CLOB |
MySQL | LONGTEXT | 不支持指定最大值,最大值为 字节;MySQL还支持TINYTEXT、TEXT、MEDIUMTEXT |
SQL Server | VARCHAR ( MAX ) | 非Unicode字符,最大长度为 ,约2GB;目前还支持TEXT类型,但是不推荐使用,将来会被删除 |
PostgreSQL | TEXT | 可以存储任意长度字符串;参考不带长度限制的VARCHAR |
Db2 | CLOB [ ( n ) ] | n表示字符个数,对于字符单元OCTETS,最大值为 ;对于CODEUNITS32,最大值为 |
SQLite | CLOB [ ( n ) ] | SQLite采用动态类型系统,存储类型为TEXT,参考SQLite文档 |
二进制类型
二进制类型用于存储二进制数据,例如图片,视频等。
二进制类型分为定长二进制串BINARY
、变长二进制串BINARY VARYING
、二进制大对象BINARY LARGE OBJECT
。
BINARY [ ( n ) ]
BINARY VARYING [ ( n ) ]
VARBINARY [ ( n ) ]
BINARY LARGE OBJECT [ ( n ) ]
BLOB [ ( n ) ]
不同厂商对于二进制数据类型的支持如下。
数据库 | 二进制类型 | 描述 |
---|---|---|
Oracle |
RAW ( n ) LONG RAW BLOB |
Oracle建议使用BLOB替换LONG RAW |
MySQL |
BINARY [ ( n ) ] VARBINARY ( n ) BLOB |
MySQL还支持TINYBLOB、MEDIUMBLOB、LONGBLOB |
SQL Server |
BINARY [ ( n ) ] VARBINARY [ ( n ) ] VARBINARY ( MAX ) |
SQL Server目前还支持IMAGE类型,但是不推荐使用,将来会被删除 |
PostgreSQL | BYTEA | |
Db2 |
BINARY [ ( n ) ] VARBINARY [ ( n ) ] BLOB [ ( n ) ] |
|
SQLite | BLOB |
数字类型
数字主要分为两类:精确数字,包括NUMERIC
、DECIMAL
、DECFLOAT
、SMALLINT
、INTEGER
和BIGINT
;以及近似数字,包括FLOAT
、REAL
和DOUBLE PRECISION
。
精确数字
精确数字类型用于存储整数或者包含固定小数位的数字。
NUMERIC(p, s)
和DECIMAL(p, s)
可以看作相同的类型。精度p表示总的有效位数,刻度s表示小数点后允许的位数。例如,123.04的精度为5,刻度为2。
p和s是可选的,s为0表示整数。SQL标准要求 p ≥ s ≥ 0 并且 p > 0。
DEC
是DECIMAL
的同义词。
DECFLOAT
是SQL:2016标准中新增的数据类型,称为十进制浮点数。它结合了 DECIMAL
的准确性与FLOAT
的某些性能优点,很适合在要处理货币值的应用程序中使用。目前只有Db2提供该数据类型。
NUMERIC [ ( p, [ s ] ) ]
DECIMAL [ ( p, [ s ] ) ]
DECFLOAT [ ( p ) ]
不同数据库对于精确数字类型的支持如下。
数据库 | 精确数字 | 描述 |
---|---|---|
Oracle |
NUMERIC DECIMAL |
Oracle中的NUMERIC 和DECIMAL 都是NUMBER 的同义词 |
MySQL |
NUMERIC DECIMAL |
|
SQL Server |
NUMERIC DECIMAL |
|
PostgreSQL |
NUMERIC DECIMAL |
|
Db2 |
NUMERIC DECIMAL DECFLOAT |
|
SQLite |
NUMERIC DECIMAL |
SQLite采用动态类型系统,实际存储类型为NUMERIC
|
SMALLINT
、INTEGER
和BIGINT
表示整数。SQL标准中没有规定它们的大小,只是要求INTEGER
的范围大于SMALLINT
,BIGINT
的范围大于INTEGER
。
SMALLINT
INTEGER
INT
BIGINT
不同的数据库几乎都遵循SQL标准中的整数类型定义。
数据库 | 整数类型 | 描述 |
---|---|---|
Oracle |
SMALLINT INTEGER |
Oracle中的SMALLINT和INTEGER都是NUMBER(38,0)的同义词;不支持BIGINT关键字 |
MySQL |
SMALLINT INTEGER BIGINT |
SMALLINT支持-32768~32767 INTEGER支持-8388608~8388607 BIGINT支持-2^63~2^63-1 |
SQL Server |
SMALLINT INTEGER BIGINT |
SMALLINT支持-32768~32767 INTEGER支持-8388608~8388607 BIGINT支持-2^63~2^63-1 |
PostgreSQL |
SMALLINT INTEGER BIGINT |
SMALLINT支持-32768~32767 INTEGER支持-8388608~8388607 BIGINT支持-2^63~2^63-1 |
Db2 |
SMALLINT INTEGER BIGINT |
SMALLINT支持-32768~32767 INTEGER支持-8388608~8388607 BIGINT支持-2^63~2^63-1 |
SQLite |
SMALLINT INTEGER BIGINT |
SQLite采用动态类型系统,实际存储类型为INTEGER
|
近似数字
近似数字也称为浮点型数字,主要用于科学计算。SQL标准中定义了3种浮点类型。
FLOAT [ ( p ) ]
REAL
DOUBLE PRECISION
不同数据库产品对于浮点数字类型的支持如下。
数据库 | 近似数字 | 描述 |
---|---|---|
Oracle |
BINARY_FLOAT BINARY_DOUBLE |
Oracle提供FLOAT 、REAL 、DOUBLE PRECISION 支持,但建议使用BINARY_FLOAT 和BINARY_DOUBLE
|
MySQL |
FLOAT REAL DOUBLE PRECISION |
|
SQL Server |
FLOAT REAL DOUBLE PRECISION |
|
PostgreSQL |
FLOAT REAL DOUBLE PRECISION |
|
Db2 |
FLOAT REAL DOUBLE PRECISION |
|
SQLite |
FLOAT REAL DOUBLE PRECISION |
SQLite采用动态类型系统,实际存储类型为REAL
|
布尔类型
SQL标准使用三值布尔逻辑:true
、false
以及unknown
。unknown
使用NULL
值表示。
BOOLEAN
严格来讲,只有PostgreSQL完全遵循SQL标准,提供了BOOLEAN
类型。
数据库 | 布尔类型 | 描述 |
---|---|---|
Oracle | N/A | 只在PL/SQL中支持创建BOOLEAN 类型的变量 |
MySQL | TINYINT(1) | MySQL中BOOL 和BOOLEAN 是TINYINT(1) 的同义词 |
SQL Server | BIT | |
PostgreSQL | BOOLEAN |
BOOL 是BOOLEAN 的同义词 |
Db2 | N/A | 只在SQL PL中支持创建BOOLEAN 类型的变量 |
SQLite | BOOLEAN | SQLite采用动态类型系统,存储类型为TEXT
|
日期时间类型
与日期和时间相关的数据类型包括:DATE
、TIME
以及TIMESTAMP
。
DATE
存储年、月、日;TIME
存储时、分、秒,以及秒的小数部分;TIMESTAMP
同时包含年、月、日、时、分、秒,以及秒的小数部分。
TIME
和TIMESTAMP
还可以包含时区TIME ZONE
。
DATE
SQLite中没有日期时间类型;Oracle中的日期类型与SQL标准不同。
数据库 | 日期类型 | 描述 |
---|---|---|
Oracle | DATE | Oracle中的DATE 包含了额外的时、分、秒 |
MySQL | DATE | |
SQL Server | DATE | |
PostgreSQL | DATE | |
Db2 | DATE | |
SQLite | N/A | 可以将日期转换为字符串或数字形式进行存储 |
SQL标准中的时间类型表示一天中的有效时间,可以包含时区信息。
TIME [ ( p ) ] [ WITH TIME ZONE | WITHOUT TIME ZONE ]
其中,p表示秒的小数部分的位数,不同数据库支持的精确程度不同;WITH TIME ZONE
表示包含时区信息,WITHOUT TIME ZONE
表示不包含时区,后者是默认值。
数据库 | 时间类型 | 描述 |
---|---|---|
Oracle | N/A | Oracle中没有TIME 类型 |
MySQL | TIME | MySQL支持秒的6位小数,即微秒;不支持带时区的时间 |
SQL Server | TIME | SQL Server支持秒的7位小数,即100纳秒;不支持带时区的时间 |
PostgreSQL | TIME | PostgreSQL支持秒的6位小数,即微秒 |
Db2 | TIME | Db2不支持秒的小数;不支持带时区的时间 |
SQLite | N/A | 可以将时间转换为字符串或数字形式进行存储 |
时间戳类型相当于日期加时间的组合。
TIMESTAMP [ ( p ) ] [ WITH TIME ZONE | WITHOUT TIME ZONE ]
其中,p表示秒的小数部分的位数,不同数据库支持的精确程度不同;WITH TIME ZONE
表示包含时区信息,WITHOUT TIME ZONE
表示不包含时区,后者是默认值。
数据库 | 时间戳类型 | 描述 |
---|---|---|
Oracle | TIMESTAMP | Oracle支持秒的9位小数,即纳秒 |
MySQL | TIMESTAMP | MySQL支持秒的6位小数,即微秒;不支持带时区的时间戳 |
SQL Server |
DATETIME2 DATETIMEOFFSET |
SQL Server支持秒的7位小数,即100纳秒 |
PostgreSQL | TIMESTAMP | PostgreSQL支持秒的6位小数,即微秒 |
Db2 | TIMESTAMP | Db2支持秒的12位小数;不支持带时区的时间 |
SQLite | N/A | 可以将时间戳转换为字符串或数字形式进行存储 |
时间间隔类型
时间间隔表示一段时间范围,SQL标准定义了两种时间间隔:第一种是以年和月度量的间隔,另一种是以天、时、分、秒指定的间隔。
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
目前,只有Oracle和PostgreSQL提供了时间间隔类型的支持。
数据库 | 时间间隔类型 | 描述 |
---|---|---|
Oracle | INTERVAL | |
MySQL | N/A | MySQL只支持INTERVAL 类型的常量值 |
SQL Server | N/A | |
PostgreSQL | INTERVAL | |
Db2 | N/A | |
SQLite | N/A |
数据链接类型
在SQL标准的第9部分定义了一个特殊的数据类型:DATALINK
,用于管理数据库外部的文件。它的值是对外部文件的一个逻辑引用或指针。
DATALINK
目前,只有Db2提供了DATALINK
类型;Oracle通过BFILE
类型支持类似的功能。
数据库 | 数据链接 | 描述 |
---|---|---|
Oracle | BFILE | 参考官方文档 |
MySQL | N/A | 不支持 |
SQL Server | N/A | 不支持 |
PostgreSQL | N/A | 不支持 |
Db2 | DATALINK | |
SQLite | N/A | 不支持 |
XML类型
SQL标准的第14部分定义了XML
类型,以及操作XML
数据的各种函数。
XML
目前,并非所有的数据库产品都支持XML
;即使支持,不同的产品也存在一定的差异,使用之前需要参考具体的官方文档。
数据库 | 字符串大对象 | 描述 |
---|---|---|
Oracle | XMLTYPE | |
MySQL | N/A | 不支持 |
SQL Server | XML | |
PostgreSQL | XML | |
Db2 | XML | |
SQLite | N/A | 不支持 |
其他
除了标准SQL中定义的数据类型之外,不同的数据库产品都实现了许多专用的数据类型,包括用户自定义数据类型。使用任何数据类型之前都应该查看对应数据库的参考文档。
Oracle database 18c SQL 数据类型
Oracle database 18c PL/SQL 数据类型
MySQL 8.0 数据类型
SQL Server 2017 数据类型
PostgreSQL 10 数据类型
Db2 11 数据类型
SQLite 3 数据类型