SQL从入门到出门 第2篇 数据类型

时间:2022-06-11 20:01:28

本篇介绍SQL:2016(ISO/IEC 9075:2016)标准中定义的数据类型,以及六种主流数据库中的数据类型实现及差异,包括:Oracle、MySQL、Microsoft SQL Server、PostgreSQL、Db2、SQLite。

数据类型概述

关系型数据库中,用于存储数据的主要对象是表(table),表由行(row)和列(column)组成。
SQL从入门到出门 第2篇 数据类型
数据行,也称为记录(record)、元组(tuple),是二维表格中的行。表中的一行对应现实世界中的一个实体,例如上表中的第一行(employee_id = 100)记录了员工 Steven King 的相关信息。

列,也称为属性(attribute)、字段(field),代表表中特定属性的所有信息,例如上表中的第二列表示所有员工的名(first_name)。

表中的每个列都拥有一个数据类型,数据类型定义了该列能够存储的数据值,以及允许执行的操作。常见的数据类型包括字符类型、数字类型、日期时间类型等。其中,数字类型可以进行加减乘除等数学运算,日期时间类型可以进行格式化显示。
SQL从入门到出门 第2篇 数据类型
上图是employees表的字段结构。

SQL标准中定义的数据类型可以分为三类(SQL:2016标准概念之数据类型):

  • 预定义数据类型(predefined data type)
  • 构造数据类型(constructed data type)
  • 自定义数据类型(user-defined data type)

预定义数据类型

预定义数据类型通常也称为内置数据类型,SQL:2016标准中规定了以下预定义数据类型:

  • C H A R A C T E R
  • C H A R A C T E R V A R Y I N G
  • C H A R A C T E R L A R G E O B J E C T
  • B I N A R Y
  • B I N A R Y V A R Y I N G
  • B I N A R Y L A R G E O B J E C T
  • N U M E R I C
  • D E C I M A L
  • D E C F L O A T
  • S M A L L I N T
  • I N T E G E R
  • B I G I N T
  • F L O A T
  • R E A L
  • D O U B L E   P R E C I S I O N
  • B O O L E A N
  • D A T E
  • T I M E
  • T I M E S T A M P
  • I N T E R V A L
  • D A T A L I N K
  • X M L

为了便于引用,将以上数据类型分为多个类别:

  • CHARACTERCHARACTER VARYING以及CHARACTER LARGE OBJECT统称为字符串类型,它们存储的值为字符串。
  • BINARYBINARY VARYING以及BINARY LARGE OBJECT统称为二进制串类型,它们存储的值为二进制串。
  • CHARACTER LARGE OBJECT以及BINARY LARGE OBJECT统称为大对象串类型,它们存储的值为大对象串。
  • NUMERICDECIMALDECFLOATSMALLINTINTEGER以及BIGINT统称为精确数字类型。
  • FLOATREAL以及DOUBLE PRECISION统称为近似数字类型。精确数字类型和近似数字类型统称为数字类型。
  • TIME WITHOUT TIME ZONE以及TIME WITH TIME ZONE统称为时间类型(不包含时区的时间和包含时区的时间)。
  • TIMESTAMP WITHOUT TIME ZONE以及TIMESTAMP WITH TIME ZONE统称为时间戳类型(不包含时区的时间戳和包含时区的时间戳)。
  • DATETIME以及TIMESTAMP统称为日期时间类型,它们存储的值为日期时间。
  • INTERVAL称为时间间隔类型,存储的值为时间间隔。
  • DATALINK称为数据链接类型,用于访问存储在数据库之外的资源。
  • XML称为XML类型,存储的值为XML值。

字符串类型

定长字符串

SQL标准中关于固定长度字符串的定义如下:

CHARACTER [ ( n ) ]

或者

CHAR [ ( n ) ]

其中,CHARACTERCHAR是同义词,表示固定长度字符串;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 不支持指定最大值,最大值为( 2 32 1 字节) * LOB存储参数CHUNK值;Oracle只能使用关键字CLOB
MySQL LONGTEXT 不支持指定最大值,最大值为 2 32 1 字节;MySQL还支持TINYTEXT、TEXT、MEDIUMTEXT
SQL Server VARCHAR ( MAX )       非Unicode字符,最大长度为 2 31 1 ,约2GB;目前还支持TEXT类型,但是不推荐使用,将来会被删除
PostgreSQL TEXT 可以存储任意长度字符串;参考不带长度限制的VARCHAR
Db2 CLOB [ ( n ) ] n表示字符个数,对于字符单元OCTETS,最大值为 2 31 1 ;对于CODEUNITS32,最大值为 2 29 1
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

数字类型

数字主要分为两类:精确数字,包括NUMERICDECIMALDECFLOATSMALLINTINTEGERBIGINT;以及近似数字,包括FLOATREALDOUBLE PRECISION

精确数字

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

NUMERIC(p, s)DECIMAL(p, s)可以看作相同的类型。精度p表示总的有效位数,刻度s表示小数点后允许的位数。例如,123.04的精度为5,刻度为2。

p和s是可选的,s为0表示整数。SQL标准要求 p ≥ s ≥ 0 并且 p > 0。

DECDECIMAL的同义词。

DECFLOAT是SQL:2016标准中新增的数据类型,称为十进制浮点数。它结合了 DECIMAL的准确性与FLOAT的某些性能优点,很适合在要处理货币值的应用程序中使用。目前只有Db2提供该数据类型。

NUMERIC [ ( p, [ s ] ) ]
DECIMAL [ ( p, [ s ] ) ]

DECFLOAT [ ( p ) ]

不同数据库对于精确数字类型的支持如下。

数据库 精确数字 描述
Oracle NUMERIC
DECIMAL
Oracle中的NUMERICDECIMAL都是NUMBER的同义词
MySQL NUMERIC
DECIMAL
SQL Server NUMERIC
DECIMAL
PostgreSQL NUMERIC
DECIMAL
Db2 NUMERIC
DECIMAL
DECFLOAT
SQLite NUMERIC
DECIMAL
SQLite采用动态类型系统,实际存储类型为NUMERIC

SMALLINTINTEGERBIGINT表示整数。SQL标准中没有规定它们的大小,只是要求INTEGER的范围大于SMALLINTBIGINT的范围大于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提供FLOATREALDOUBLE PRECISION支持,但建议使用BINARY_FLOATBINARY_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标准使用三值布尔逻辑:truefalse以及unknownunknown使用NULL值表示。

BOOLEAN

严格来讲,只有PostgreSQL完全遵循SQL标准,提供了BOOLEAN类型。

数据库 布尔类型 描述
Oracle N/A 只在PL/SQL中支持创建BOOLEAN类型的变量
MySQL TINYINT(1) MySQL中BOOLBOOLEANTINYINT(1)的同义词
SQL Server BIT
PostgreSQL BOOLEAN BOOLBOOLEAN的同义词
Db2 N/A 只在SQL PL中支持创建BOOLEAN类型的变量
SQLite BOOLEAN SQLite采用动态类型系统,存储类型为TEXT

日期时间类型

与日期和时间相关的数据类型包括:DATETIME以及TIMESTAMP

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

TIMETIMESTAMP还可以包含时区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 数据类型