上一篇我们讨论了如何进行数据库的结构设计，并具体介绍了实体关系图和规范化的技术。
在设计 ERD 时，首先需要定义实体以及实体的属性，也就是定义表的结构。定义表结构时，首先需要确认表中包含哪些字段以及字段的数据类型。今天我们就来了解一下如何为表中的字段选择合适的数据类型。

常见数据类型

字段的数据类型定义了该字段能够存储的数据值，以及允许执行的操作。下图列出了 SQL 中常见的数据类型，包括字符串类型、数字类型、日期时间类型以及二进制类型。
image.png
我们分别在第 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) | | 近似数字 | BINARY_FLOAT
BINARY_DOUBLE | 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 使用 BINARYFLOAT 和 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 二进制类型。

选择合适的数据类型

最后我们来看看如何选择合适的数据类型。首先，应该满足存储业务数据的需求；其次，还需要考虑性能和使用方便。一般来说，先确定基本的类型：

  • 文本数据，只能使用字符串类型；
  • 数值数据，尤其是需要进行数学运算的数据，选择数字类型；
  • 日期和时间信息，最好使用原生的日期时间类型；
  • 文档、图片、音频和视频等，使用二进制类型；或者可以考虑存储在文件服务器上，然后在数据库中存储文件的路径。

接下来需要进一步确定具体的数据类型。在满足数据存储和扩展的前提下，尽量使用更小的数据类型，可以节省一些存储，通常性能也会更好。例如，对于一个小型公司而言，员工编号通常不会超过几百，使用 SMALLINT 已经足够。对于 MySQL 而言，不需要支持负数的话可以考虑 UNSIGNED 类型。
如果需要存储精确的数字，不要使用浮点数类型。对于金额，可以使用 NUMERIC(p, s)；或者将数据乘以 10 的 N 次方，例如将 10.35 元存储为整数 103500，然后在应用程序中进行处理和前端显示转换。
对于字符数据，一般使用 VARCHAR 类型；如果数据长度能够确保一致，可以使用 CHAR；指定最大长度时，满足存储需求的前提下尽量使用更小的值。只有在普通字符串类型长度无法满足时才考虑大字段类型。
不要使用字符串存储日期时间数据，它们无法支持数据的运算。例如获得两个日期之间的间隔，需要依赖应用程序进行转换和处理。最好也不要使用整数类型存储当前时间距离 1970 年 1 月 1 日的毫秒数来表示时间，这种方式在显示时需要进行转换，不是很方便。
另外，如果一个字段同时出现在多个表中，使用相同的数据类型。例如，员工表中的部门编号（dept_id）字段与部门表的编号（dept_id）字段保持类型一致。

小结

今天我们介绍了 SQL 中的基本数据类型以及它们在各种数据库中的实现，同时分析了选择数据类型时的一些通用的原则。需要注意的是，同一数据类型在不同数据库中支持的范围大小和精确度可能不同；因此，使用任何数据类型之前都应该查看相关的数据库文档。
讨论题：数据库还支持许多其他的数据类型，例如 XML、JSON 等。说说你使用的数据库中还有哪些常用的数据类型，有哪些应用场景和注意事项。