《数据库基础语法》16. 数据库的常见数据类型以及DDL

楔子

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

今天我们就来了解一下如何为表中的字段选择合适的数据类型。

常见的数据类型

字段的数据类型定义了该字段能够存储的数据值，以及允许执行的操作。我们对常见的数据类型分门别类的介绍一下，这里以 MySQL 和 PostgreSQL 为例。

字符串类型

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

MySQL 和 PostgreSQL 在字符串方面，支持的类型名称是一样的。

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

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

对于定长字符串，如果输入的字符串长度不够，将会使用空格进行填充。例如类型为 CHAR(5) 的字段，如果输入值为 "A"，实际存储的内容为 "A####"；# 代表空格，也就是一个字符 "A" 加上 4 个空格。通常来说，只有存储固定长度的数据时，才会考虑使用定长字符串类型。例如 18 位身份证，6 位邮政编码等。

VARCHAR(n) 表示长度不固定的字符串，其中 n 表示允许存储的最大长度。对于变长字符串，如果输入的字符串长度不够，那么不会用空格填充，而是只存储实际的内容。例如类型为 VARCHAR(5) 的字段，如果输入值为 "A"，实际存储的内容为 "A"。变长字符串类型一般用于存储长度不固定的内容，例如名字、电子邮箱、产品描述等。

字符串大对象，通常用于存储普通字符串类型无法支持的更长的字符串数据，例如整篇文章、备注、评论等。MySQL 提供了 TINYTEXT、TEXT、MEDIUMTEXT 以及 LONGTEXT 分别用于存储不同长度的文本数据；PostgreSQL 提供了 TEXT 类型存储任意长度的字符串。

数值类型

数值类型主要分为两类：精确数值和近似数值。

精确数值类型用于存储整数或者包含固定小数位的数值，其中 SMALLINT、INTEGER 和 BIGINT 都可以表示整数，并且 MySQL 和 PostgreSQL 都是支持的。

• SMALLINT 支持 \(-32768\) ~ \(32767\)
• INTEGER 支持 \(-2147483648\) ~ \(2147483647\)
• BIGINT 支持 \(-2^{63}\) ~ \(2^{63}-1\)

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。

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

然后是近似数值，它是主流编程语言上的浮点数。MySQL 支持 FLOAT 和 DOUBLE，分别表示单精度浮点数和双精度浮点数，单精度浮点数具有 4 字节的存储空间，并具有小数点后大约 7 个数字的精度；双精度浮点数具有 8 字节的存储空间，并具有小数点后大约 15 个数字的精度。

而 PostgreSQL 支持的浮点数类型有 FLOAT4（REAL） 和 FLOAT8（DOUBLE PRECISION），它对应 MySQL 的 FLOAT 和 DOUBLE。

需要注意的是，由于浮点数是近似值，所以它们可能不会准确表示所有的数字。因此对于需要精确值的数据，如货币，通常建议使用固定点类型如 DECIMAL 或 NUMERIC。

日期时间类型

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

以上三种，MySQL 和 PostgreSQL 都是支持的。如果存储日期信息，例如生日，可以使用 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'

然后需要注意的是，PostgreSQL 还支持 INTERVAL 类型，表示时间段，例如 "2 days" 或 "3 months"，但 MySQL 没有一个与此直接对应的数据类型。

二进制类型

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

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

MySQL 支持 BINARY、VARBINARY 以及 TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB 二进制类型；PostgreSQL 支持 BYTEA 二进制类型。

选择合适的数据类型

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

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

接下来需要进一步确定具体的数据类型。在满足数据存储和扩展的前提下，尽量使用更小的数据类型，可以节省一些存储，通常性能也会更好。例如，对于一个小型公司而言，员工编号通常不会超过几百，使用 SMALLINT 已经足够。对于 MySQL 而言，不需要支持负数的话可以考虑 UNSIGNED 类型。

如果需要存储精确的数字，不要使用浮点数类型。对于金额，可以使用 NUMERIC(p, s)；或者将数据乘以 10 的 N 次方，例如将 10.35 元存储为整数 103500，然后在应用程序中进行处理和前端显示转换。

对于字符数据，一般使用 VARCHAR 类型；如果数据长度能够确保一致，可以使用 CHAR；指定最大长度时，满足存储需求的前提下尽量使用更小的值。只有在普通字符串类型长度无法满足时才考虑大字段类型。

不要使用字符串存储日期时间数据，它们无法支持数据的运算。例如获得两个日期之间的间隔，需要依赖应用程序进行转换和处理。最好也不要使用整数类型存储当前时间距离 1970 年 1 月 1 日的毫秒数来表示时间，这种方式在显示时需要进行转换，不是很方便。

另外，如果一个字段同时出现在多个表中，使用相同的数据类型。例如，员工表中的部门编号（dept_id）字段与部门表的编号（dept_id）字段保持类型一致。

使用 DDL 管理数据库中的对象

我们上面讨论了如何为字段选择合适的数据类型，选定了字段的数据类型之后，我们就可以开始创建和管理数据库中的表了。

数据库对象

数据库（Database）由一组相关的对象组成，主要包括表、索引、视图、存储过程等。为了方便对象的管理和访问，数据库通常使用模式（Schema）来组织这些对象；模式是一个逻辑单元，或者一个存储对象的容器；它们之间的关系如下图所示：

一个数据库由多个模式组成，一个模式由许多对象组成；在不同模式中可以创建同名的对象。

MySQL 中的模式和数据库是相同的概念，一个数据库对应一个同名的模式。

管理数据库

当我们连接到数据库服务器时，需要指定一个目标数据库。如果需要创建一个新的数据库，可以使用 CREATE DATABASE 语句：

CREATE DATABASE mydb;

我们可以使用命令或语句查看已有的数据库。

-- MySQL 实现
SHOW DATABASES;

SELECT schema_name AS database_name
  FROM information_schema.schemata;
-- information_schema 系统数据库存储了 MySQL 服务器中所有数据库的信息，例如数据库名称、表的结构以及访问权限等。


-- PostgreSQL 实现
SELECT datname AS database_name
  FROM pg_database;
-- pg_database 是 PostgreSQL 中的一个系统表，存储了关于数据库的信息。

如果确认不再需要，可以使用 DROP DATABASE 语句删除数据库：

DROP DATABASE mydb;

DROP DATABASE 命令将会删除该数据库中的所有对象，而且操作无法恢复，使用时千万小心！如果有用户正在连接，无法删除数据库；可以等待用户断开连接，或者强制断开连接后删除。

管理模式

CREATE SCHEMA 命令用于创建一个新的模式，PostgreSQL 支持：

CREATE SCHEMA xxx [AUTHORIZATION some_user]

以上语句创建一个名为 xxx 的模式，可选的 AUTHORIZATION 表示为该模式指定一个拥有者 some_user，拥有者是一个已经存在的数据库用户。然后 PostgreSQL 在创建完数据时，会自动在该库下面创建一个名为 public 的模式。

MySQL 中的模式等价于数据库，因此 CREATE SCHEMA 等价于 CREATE DATABASE。所以 MySQL 的数据库下是数据表，PostgreSQL 的数据库下面是模式，模式下面是表。等于说 PostgreSQL 更加细化了，如果不指定模式，那么默认会访问 public 模式。

不需要的模式可以使用 DROP SCHEMA 命令删除：

DROP SCHEMA xxx;

MySQL 中的模式等价于数据库，因此 DROP SCHEMA 等价于 DROP DATABASE。

如果模式中存在对象，则无法删除该模式；可以先删除其中的对象，再删除模式。某些数据库支持级联删除：

-- PostgreSQL 实现
DROP SCHEMA xxx CASCADE;

创建表

然后我们来看看如何创建表，先看 MySQL。

-- MySQL
CREATE TABLE sample_data (
    id INT AUTO_INCREMENT PRIMARY KEY,                -- 整数类型与主键约束
    name VARCHAR(100) NOT NULL COMMENT '',            -- 字符串类型与非空约束
    age TINYINT UNSIGNED,                             -- 小整数类型与无符号约束
    salary DECIMAL(10, 2),                            -- 小数点固定类型
    birthdate DATE,                                   -- 日期类型
    appointment_time TIME,                            -- 时间类型
    registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,-- 时间戳类型与默认值约束
    memo TEXT,                                        -- 文本类型
    profile_picture BLOB,                             -- 二进制大对象类型
    is_active BOOLEAN,                                -- 布尔类型
    email VARCHAR(255) UNIQUE,                        -- 字符串类型与唯一约束
    phone_number VARCHAR(15) DEFAULT 'UNKNOWN',       -- 字符串类型与默认值约束
    floating_point FLOAT,                             -- 单精度浮点数类型
    double_precision DOUBLE,                          -- 双精度浮点数类型
    office_location ENUM('NORTH', 'SOUTH', 'EAST', 'WEST'), -- 枚举类型
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,    -- 日期时间类型与默认值约束
    last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 时间戳类型与自动更新约束
    CONSTRAINT check_age CHECK (age >= 0 AND age <= 100)  -- 检查约束
);

需要注意的是 ON UPDATE CURRENT_TIMESTAMP 这个语法，它表示当某一行更新时，自动用当前时间对 last_modified 进行更新。

对了，还有外键，在创建表时，也可以指定外键。

CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE sample_data (
    -- ... 其他列定义 ...
    dept_id INT,                                       -- 添加用于存储部门ID的列
    -- ... 其他列定义 ...
    
    FOREIGN KEY (dept_id) REFERENCES departments(id),  -- 外键约束
    -- ... 其他约束 ...
);

sample_data 的 dept_id 作为外键关联 departments 表的主键，那么 dept_id 可以插入的值就受到了限制，它只能是 departments.id 中已经存在的值。并且 departments 表中的数据此时就不能直接删除了，如果删除的行对应 id 值被外键引用，那么就会报错。此时可以在建表的时候，增加一些子句。

FOREIGN KEY (dept_id) REFERENCES departments(id) ON DELETE CASCADE

此时在删除行数据时，如果有外键引用，那么会连同子数据一起级联删除。比如删除 departments 表中 id 为 1 的数据，而 dept_id 为 1 的数据在 sample_data 中有两条，那么此时这两条数据会被一起删除。如果你不想被删除，那么也可以设置为 NULL，此时只需要这么做。

FOREIGN KEY (dept_id) REFERENCES departments(id) ON DELETE SET NULL

这样的话，在删除 departments 的某一行时，与之相关联的 sample_data 表中的 dept_id 会被设置为空，而不是将整个行删掉。注意：这么做的前提是，该字段必须要允许为空。如果允许为空，那么设置为 NULL 是没有问题的，不会进行外键检查。

当然除了删除，还有更新。

-- 不指定 ON UPDATE CASCADE 或 ON UPDATE SET NULL,那么主键默认是不允许更新的
-- 同理不指定 ON DELETE CASCADE 或 ON DELETE SET NULL，那么主键默认不允许删除
FOREIGN KEY (dept_id) REFERENCES departments(id) ON UPDATE CASCADE;
FOREIGN KEY (dept_id) REFERENCES departments(id) ON UPDATE SET NULL;

ON DELETE 和 ON UPDATE 可以同时指定，分别控制当主键删除时，外键会有什么行为。

以上是 MySQL 创建表，我们再来看看 PostgreSQL。

CREATE TABLE sample_data_pg (
    id SERIAL PRIMARY KEY,                    -- 自动增量整数与主键约束
    name VARCHAR(100) NOT NULL,               -- 字符串类型与非空约束
    age SMALLINT CHECK (age >= 0 AND age <= 130), -- 小整数类型与检查约束
    salary NUMERIC(10, 2),                    -- 小数点固定类型
    birthdate DATE,                           -- 日期类型
    appointment_time TIME,  -- 时间类型
    registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 时间戳类型与默认值约束
    memo TEXT,                                -- 文本类型
    profile_picture BYTEA,                    -- 二进制大对象类型
    is_active BOOLEAN,                        -- 布尔类型
    email VARCHAR(255) UNIQUE,                -- 字符串类型与唯一约束
    floating_point REAL,                      -- 单精度浮点数类型
    double_precision DOUBLE PRECISION,        -- 双精度浮点数类型
    office_location VARCHAR(50) REFERENCES offices(name), -- 文本类型与外键约束
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 日期时间类型与默认值约束
    last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 时间戳类型与默认值约束
);

PostgreSQL 在创建表时，如果希望自增，那么将类型指定为 SERIAL 即可。然后是外键，PostgreSQL 和 MySQL 的表现是相同的。

如果某个字段不再需要，可以使用 DROP COLUMN 操作删除：

ALTER TABLE table_name drop 列名

 

删除表：

-- DROP TABLE 语句用于删除一个表。
DROP TABLE table_name

 

截断表：

SQL 还提供了一种特殊的操作：截断表（TRUNCATE），可以用于快速删除表中的所有数据。

TRUNCATE TABLE table_name

TRUNCATE 用于快速删除数据，回收表占用的空间，但会保留表的结构。MySQL 和 PostgreSQL 可以省略 TABLE 关键字。

小结

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

数据定义语言（Data Definition Language）用于定义数据库中各种对象的结构，例如表、视图、索引等。常见的 DDL 语句包括创建（CREATE）、修改（ALTER）和删除（DROP）。虽然各种对象的具体语法细节不同，但都遵循相同的模式；例如，创建索引可以使用 CREATE INDEX 语句；