外观
03 - 表该怎么设计:主键、数据类型与字段约束的真实代价
摘要与阅读定位
订单金额不能为负,同一租户的订单号不能重复,已支付订单必须有支付时间:这些规则可以写进表结构,让数据库在写入时检查。表设计首先要明确一行代表什么、字段允许什么值、哪些字段共同标识业务对象,再考虑存储和修改结构的成本。本文从一张只有主键和金额的表开始,逐步扩展到订单;主键的物理成本在 11 B+ Tree中深入展开。
- 前置:01 SQL 完整链路、A 最小验证。本文先解释约束基础;NULL 的完整语义在后续 10展开,物理解释在 02 页与存储,关联与访问路径进入 05、12。
- 基线:MySQL 8.0.16+ / 8.4、InnoDB(负责数据存取和事务的存储引擎)、严格 SQL 模式。
CHECK从 8.0.16 起执行;DDL(建表、修改表结构等操作)的行为需按小版本核验。遵循 A,示例给出推导预期,实际行为以目标版本和配置为准。
初次先读第 1 节的基础表,再用第 2 节检查一条合法订单;第一次失败验证只选择一个用例。主键物理布局、行长限制、时区切换与在线 DDL 是第二层内容,不必同时完成。
1. 现象:同样的字符串,不同的数据契约
VARCHAR 保存可变长度字符串。把金额存成字符串后,按字符串比较时可能出现 '100' < '20',因为比较的是字符顺序,而不是数值大小;参与数值运算时,还可能发生隐式转换,即数据库自动把一种类型转成另一种类型。把日期存成字符串,也会把日期是否合法的检查交给应用。
格式严格一致的日期字符串并不必然无法使用索引。问题在于:文本类型本身不保证金额运算和日期语义,应用及驱动需要自行补齐。字段的值域就是它允许保存的值范围;选类型和加约束,是把这部分规则明确交给数据库。
先列业务规则:订单号是在整个系统内唯一,还是只在租户内唯一?金额的单位和上限是什么?已支付状态是否要求支付时间?时间代表一个确定时刻,还是门店当地的日期和钟表读数?这些答案决定字段类型和约束。用于并发更新的版本号,则还需要明确由哪些写入口递增。
1.1 约束分别回答什么问题
| 规则 | 数据库机制 | 例子 |
|---|---|---|
| 怎样唯一定位一行 | 主键 PRIMARY KEY | 订单内部 id 不重复且不能为空 |
| 哪个业务身份不能重复 | 唯一键 UNIQUE | 同一租户内订单号不能重复 |
| 哪个字段必须存在 | NOT NULL | 金额必须有值,不能用未知代替 |
| 值是否合法 | 类型与 CHECK | 金额用十进制数,且不能为负 |
| 引用对象是否存在 | 外键 FOREIGN KEY | 订单客户必须是本租户已登记客户 |
主键和业务唯一键可能不同:内部 id 方便定位,订单号用于业务识别。租户是使用同一系统的不同组织或客户,租户号进入唯一键和外键,是为了明确各自身份范围,不是实现了访问权限控制。
先用最少字段看一条规则:在隔离实例、旧事务已明确结束的连接中运行,表已存在时停止并核对归属。
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET autocommit = 1;
CREATE TABLE design_basic (
id BIGINT PRIMARY KEY,
amount DECIMAL(10, 2) NOT NULL,
CHECK (amount >= 0)
) ENGINE = InnoDB;
INSERT INTO design_basic VALUES (1, 10.00), (2, 0.00);
SELECT id, amount FROM design_basic ORDER BY id;预期输出:两行,金额分别为 10.00 和 0.00。DECIMAL(10, 2) 保存最多 10 位十进制数字,其中 2 位是小数;NOT NULL 要求金额存在,CHECK (amount >= 0) 要求金额非负。0 表示明确的零金额,NULL 表示缺失或未知;是否允许免费订单由业务决定。
这两条语句是预期失败用例,分别执行,不要把它们与初始化连在一起批量忽略错误:
sql
INSERT INTO design_basic VALUES (3, -1.00);sql
INSERT INTO design_basic VALUES (3, NULL);第一条语句预期因负金额被拒绝,第二条预期因金额为 NULL 被拒绝;分别失败后,原来的两行不变。CHECK 和 NOT NULL 分别约束值是否合法、值是否存在,不能相互替代。更复杂的未知值比较见 10。
1.2 从简单规则扩展到订单
进入下一张表前,先明确金额、币种、身份和状态,逐项增加规则:客户必须存在 → 订单号在租户内唯一 → 状态有枚举范围 → 已支付状态需要支付时间。这样读建表 SQL 时,每个约束都有业务理由。
2. 实现:独立、带租户边界的综合订单表
下面一行代表一笔订单,客户身份由 (tenant_id, id) 共同确定。唯一键 (tenant_id, order_no) 表示只有两列的组合不能重复;外键 (tenant_id, customer_id) 则要求这个组合在客户表中存在。这样,即使两个租户都有客户 1001,订单也必须引用自己租户的客户。
结束其他实验事务后,在隔离库初始化 design_* 教学表。脚本中的 DROP 会删除已有的订单表和客户表,执行前确认数据可丢弃。DDL 不放入业务订正事务,以免隐式提交破坏事务边界。先检查会话的 sql_mode 包含 STRICT_TRANS_TABLES 或 STRICT_ALL_TABLES,不满足则停止;以下约束验证不使用会改变错误处理方式的 INSERT IGNORE。
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET autocommit=1;
SELECT VERSION(),@@session.sql_mode,@@session.time_zone;
DROP TABLE IF EXISTS design_order;
DROP TABLE IF EXISTS design_customer;
CREATE TABLE design_customer (
tenant_id BIGINT NOT NULL,id BIGINT NOT NULL,
PRIMARY KEY (tenant_id,id)
) ENGINE=InnoDB;
CREATE TABLE design_order (
id BIGINT NOT NULL AUTO_INCREMENT,
tenant_id BIGINT NOT NULL,
order_no VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs NOT NULL,
customer_id BIGINT NOT NULL,
status VARCHAR(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
amount DECIMAL(18,2) NOT NULL,
currency CHAR(3) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
paid_at DATETIME(3) NULL,
created_at DATETIME(3) NOT NULL,
updated_at DATETIME(3) NOT NULL,
version BIGINT NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY uk_design_order_no (tenant_id,order_no),
KEY idx_design_customer_time (tenant_id,customer_id,created_at,id),
CONSTRAINT fk_design_customer FOREIGN KEY (tenant_id,customer_id)
REFERENCES design_customer(tenant_id,id),
CONSTRAINT chk_design_amount CHECK (amount>=0),
CONSTRAINT chk_design_version CHECK (version>=0),
CONSTRAINT chk_design_status CHECK (status IN ('CREATED','PAID','SHIPPED','CANCELLED')),
CONSTRAINT chk_design_paid CHECK (status NOT IN ('PAID','SHIPPED') OR paid_at IS NOT NULL),
CONSTRAINT chk_design_currency CHECK (currency IN ('CNY','USD'))
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4;
INSERT INTO design_customer VALUES (1,1001),(2,1001);
INSERT INTO design_order
(id,tenant_id,order_no,customer_id,status,amount,currency,paid_at,created_at,updated_at)
VALUES
(1,1,'Order-A',1001,'CREATED',100.00,'CNY',NULL,'2026-09-01','2026-09-01'),
(2,2,'Order-A',1001,'PAID',20.00,'USD','2026-09-02','2026-09-01','2026-09-02'),
(3,1,'order-a',1001,'CREATED',0.00,'CNY',NULL,'2026-09-03','2026-09-03');
SELECT id,tenant_id,order_no,amount,version FROM design_order ORDER BY id;预期输出:三行,version 均为默认值 0。前两行租户不同,因此可以使用相同订单号;第一行和第三行属于同一租户,但排序规则 utf8mb4_0900_as_cs 区分大小写,因此 Order-A 与 order-a 可以同时存在。
排序规则规定文本如何比较,as_cs 表示区分重音和大小写,不等于逐字节比较。Unicode 字符的比较、尾部空格,以及是否把不同表示形式统一为同一形式(规范化),仍需业务确定。协议请求 ID 若要求字节一致,可使用保存二进制字节的 VARBINARY 并检查长度,见 16。
本模板只允许 CNY/USD、无退款负金额、PAID/SHIPPED 要求 paid_at;CANCELLED 可保留既有支付时间。这些是教学值域,不是所有订单业务事实。外键包含 tenant_id,防止跨租户引用,但不替代查询授权和行级租户过滤。
3. 验证:一次只击穿一项规则
以下失败用例分别执行,不忽略错误继续跑;每次核对表仍为三行。
sql
INSERT INTO design_order
SELECT 11,1,'Order-A',1001,'CREATED',1,'CNY',NULL,'2026-09-01','2026-09-01',0;
INSERT INTO design_order
SELECT 12,1,'negative',1001,'CREATED',-1,'CNY',NULL,'2026-09-01','2026-09-01',0;
INSERT INTO design_order
SELECT 13,1,'bad-status',1001,'UNKNOWN',1,'CNY',NULL,'2026-09-01','2026-09-01',0;
INSERT INTO design_order
SELECT 14,1,'missing-paid-at',1001,'PAID',1,'CNY',NULL,'2026-09-01','2026-09-01',0;
INSERT INTO design_order
SELECT 15,1,'missing-customer',9999,'CREATED',1,'CNY',NULL,'2026-09-01','2026-09-01',0;
INSERT INTO design_order
SELECT 16,1,'missing-amount',1001,'CREATED',NULL,'CNY',NULL,'2026-09-01','2026-09-01',0;六条语句预期依次被唯一键、金额 CHECK、状态 CHECK、支付状态与时间的跨列 CHECK、外键、NOT NULL 拒绝。
CHECK 拒绝结果为假的条件;条件涉及 NULL 时,结果可能是 UNKNOWN(未知),此时可以通过检查。因此,必填金额需要同时声明 NOT NULL,见 10。这里的 SQL 字符串 'UNKNOWN' 则是一个不在允许列表内的状态,与逻辑结果 UNKNOWN 不同。
外键检查还会访问和锁定关联索引。如果配置删除级联,即删除被引用记录时自动删除关联记录,写入范围可能进一步扩大。是否采用外键、如何处理删除,需要结合吞吐和删除策略验证,不能把规则全部交给应用,也不能无条件配置级联。
chk_design_paid 的条件可以读成:状态不是 PAID 或 SHIPPED,或者支付时间已存在。它约束当前行的字段组合,却不记忆历史,所以不能完整判断 CANCELLED 能否再变为 PAID。同样,DEFAULT 0 只提供版本号初值,不会自动递增;所有写入口都必须遵守 09 安全订正协议。
4. 主键:逻辑身份与聚簇布局分开决定
主键在逻辑上用于唯一定位一行,在 InnoDB 中还影响行的存储组织。InnoDB 用聚簇索引组织行数据:树末端的叶子记录承载行内数据,大值可能另存于页外。其他索引称为二级索引,其记录携带定位聚簇行的键。因此,聚簇键越宽,多个二级索引也要承担更大的定位键开销。
没有显式主键时,InnoDB 选择首个所有列都为 NOT NULL 的唯一索引作为聚簇索引;如果没有这样的索引,则使用隐藏聚簇键。所以未声明主键不等于没有索引。11进一步解释这套布局。
下表中的局部性指相邻插入集中访问较少页;工作集指一段时间内经常访问的页集合。UUID 是一种可分散生成的标识符,其表示方式和生成规则都会影响索引成本。
| 选择 | 可能收益 | 必须验证的成本 |
|---|---|---|
| 紧凑自增 BIGINT | 8 字节键、插入局部性 | 最右页并发、发号边界、跨库身份冲突 |
| 随机 UUID 字符串 | 分散生成身份 | 字符编码、比较、索引宽度及随机页工作集 |
| BINARY(16) UUID | 降低字符表示宽度 | 字节序、驱动编码、仍可能随机插入 |
| 有序 ID/UUID | 改善局部性 | 时间位规则、时钟回拨、热点与泄露信息 |
| 可变业务号作聚簇键 | 一次索引定位业务号 | 更新主键传播二级索引及关联引用、迁移成本 |
UUID_TO_BIN 的交换参数针对 UUID 的时间字段布局,不能把所有 UUID 版本自动变为有序键。自增 ID 也不是提交顺序:先取得 ID 的事务可能后提交;并发提交、回滚和重启可产生空洞。BIGINT UNSIGNED 的最大值超出 Java long 的正数范围;本例使用有符号 BIGINT,应用仍须约束正 ID,并评估可用编号容量。
5. 金额与字符串:精度、范围和字节上限
DECIMAL(18,2) 最多保存 18 位十进制数字,其中 2 位小数,整数部分最多 16 位。它适合保存约定为两位小数的金额,可避免二进制浮点表示带来的误差;但除法和税率计算可能产生更多小数位,仍需业务规定何时舍入、怎样舍入。
sql
SELECT CAST(0.10 AS DECIMAL(18,2))+CAST(0.20 AS DECIMAL(18,2)) AS exact_sum;预期输出:0.30。输入超过两位小数时,可能先按字段精度转换或舍入,CHECK 检查的已是转换后的值,不一定会拒绝原始输入。因此,若业务要求拒绝超过两位的小数,必须在转换发生前验证输入。
多币种金额需按币种的小数单位,或采用约定的最小单位整数设计,不能假设所有币种都保留两位小数。Java 中建议使用十进制数类型 BigDecimal 并显式指定舍入规则;避免用 new BigDecimal(double) 把已有的二进制浮点误差带入金额。
VARCHAR(n) 的 n 是字符数,不是字节数。utf8mb4 中一个字符最多占 4 字节,记录还需要长度等管理开销;实际内容并非总占 n × 4 字节,但建表和索引限制需要考虑声明上限。
MySQL 的逻辑行长限制与 InnoDB 的页内记录限制是两项检查:前者决定字段声明能否组成合法表,后者限制一页中能容纳的记录。页是组织和缓存数据的基本单位。TEXT、BLOB 等长值可以涉及页外存储,但这不意味着任意大的 VARCHAR 声明都能合法建表。
独立失败用例:
sql
CREATE TABLE design_too_wide (
id BIGINT PRIMARY KEY,
payload VARCHAR(20000) CHARACTER SET utf8mb4
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;预期结果:建表因最大逻辑行长超限失败。这验证的是声明上限,而不是宽行查询性能。如果意外成功,应先记录版本和完整 DDL,排查条件差异。需要保存长文本时,可考虑 TEXT,再按 02验证实际访问的页集合。索引键的字节限制还受页大小、行格式和前缀索引(只索引字段开头的一部分)影响,不能把“所有索引都是 3072 字节”当作通则。
字符集规定字符如何编码,排序规则(collation)规定如何比较。下面用 _utf8mb4 明确字符串的字符集,避免连接设置与指定排序规则不匹配:
sql
SELECT _utf8mb4'A' COLLATE utf8mb4_0900_ai_ci = _utf8mb4'a' AS insensitive,
_utf8mb4'A' COLLATE utf8mb4_0900_as_cs = _utf8mb4'a' AS sensitive;预期输出:insensitive 为 1,sensitive 为 0,分别表示相等、不相等。迁移排序规则前,要检查规范化处理和新规则下的重复业务键:原来不同的字符串可能变为相等,从而使唯一索引重建失败。
6. 时间:同一时刻和同一墙上时间不同
支付时间通常代表一个确定时刻;生日、门店营业时间则可能代表当地日历值。UTC 是统一的时间基准,同一时刻在不同时区可以显示为不同钟表读数。TIMESTAMP 会按会话时区转换,DATETIME 保存日历字段,不自动附带时区。
下面继续在隔离库中运行,会删除已有的 design_clock。先以 UTC 写入相同时间文本,再把会话时区改为 +08:00,观察两种类型的显示差异,最后恢复会话时区:
sql
DROP TABLE IF EXISTS design_clock;
CREATE TABLE design_clock (
id BIGINT PRIMARY KEY,instant TIMESTAMP(3) NOT NULL,wall_time DATETIME(3) NOT NULL
) ENGINE=InnoDB;
SET @design_old_time_zone=@@session.time_zone;
SET SESSION time_zone='+00:00';
INSERT INTO design_clock VALUES (1,'2026-09-01 00:00:00','2026-09-01 00:00:00');
SET SESSION time_zone='+08:00';
SELECT instant,wall_time FROM design_clock;
SET SESSION time_zone=@design_old_time_zone;预期输出:instant 显示当天 08:00,wall_time 仍为 00:00。design_order 的 DATETIME 字段约定存 UTC,这个约定必须由写入口兑现,类型本身不会替应用转换。生日、营业时间等本地日历值不应机械转换为绝对时刻。
MySQL 8.4 的 TIMESTAMP 秒级边界在 1970—2038,DATETIME 范围更宽,为 1000—9999;小数秒最多 6 位,示例中的 (3) 表示毫秒精度。精确端点和舍入行为需按官方类型文档核验,远期预约不能照搬 TIMESTAMP。查询一个业务自然日时,先按地区时区计算边界,再查询 UTC 半开区间,即包含起点、不包含终点,见 06。CURRENT_TIMESTAMP 默认值或 ON UPDATE 自动更新时间也不等于业务事件发生时间,更不能替代完整变更审计。
7. DDL 验证:INSTANT 不等于无锁无风险
DDL 改变表结构,DML 则插入、更新或删除数据。结构变更是否重写数据、能否同时读写,取决于操作、算法和版本。INSTANT 是一种不重写既有全部行的结构变更算法,但修改表定义仍需元数据锁(MDL):它协调其他会话使用表定义与当前会话修改表定义。
在隔离实验表上明确指定算法,可以在不支持时得到失败,而不是自动退化成昂贵操作:
sql
SET @design_old_lock_timeout=@@session.lock_wait_timeout;
SET SESSION lock_wait_timeout=5;
ALTER TABLE design_order ADD COLUMN source VARCHAR(16) NULL,ALGORITHM=INSTANT;
SHOW CREATE TABLE design_order;
SET SESSION lock_wait_timeout=@design_old_lock_timeout;本例表结构下预期支持 ADD COLUMN;8.0 与 8.4 对新增列位置的支持、限制和默认算法不同,必须在目标小版本验证。长事务若仍持有相关元数据锁,INSTANT 也可能等待;上述脚本把会话的锁等待超时设为 5 秒,并在结束时恢复。两会话验证方法见 19 MDL。
另一种算法 INPLACE 不等于不重建:修改聚簇主键可以使用它重组数据,仍然昂贵。LOCK=NONE 允许支持场景中的并发 DML,也不保证整个操作完全没有 MDL。
上线前需单独审批结构变更,评估 DDL 隐式提交、记录并发修改的在线日志、磁盘空间、复制延迟和取消操作的代价。失败后不要把算法改为 DEFAULT 在高峰重跑;恢复列结构也不能仅靠 ROLLBACK。
8. 排障、取舍与清理
- 唯一键冲突:查租户边界、字符比较和并发请求身份,不关闭约束。
- CHECK 看似无效:查版本、SHOW CREATE TABLE、约束是否 ENFORCED、NULL 与转换前后值。
- 宽行/索引过大:区分声明上限错误和实际工作集,检查聚簇键传播及冷热访问。
- 时间偏移:同时采样会话时区、数据库类型和驱动绑定,不只改显示格式。
- ALTER 阻塞:关联 MDL 等待者与长事务,按 19处理,不仅看数据行锁。
基础验收是合法金额能写入、负数和缺失被拒绝;综合验收再覆盖三条合法订单、六类非法输入、大小写与时区差异。DDL 部分需要核对目标版本的支持与等待边界,单连接执行成功不等于已验证锁等待。生产还需审查所有写入口。
清理先结束事务,在隔离库先删除 design_order,再删除 design_customer;另外删除 design_basic、design_clock。design_too_wide 若意外建成也仅在隔离库删除。自然后续语义走 10 NULL → 05 JOIN,物理成本走 11 → 12。
9. 名词解释
| 名词 | 含义 |
|---|---|
| 值域 | 一个字段允许的值范围,如非负金额或约定状态 |
| 主键 / 唯一键 / 外键 | 定位一行 / 约束身份不重复 / 检查关联对象存在 |
| 租户 | 共享系统但拥有各自业务范围的组织或客户 |
| DECIMAL(p,s) | 总计最多 p 位十进制数字,其中 s 位小数 |
| 字符集 / 排序规则 | 字符如何编码 / 文本如何比较与排序 |
| 聚簇 / 二级索引 | 叶子组织行数据的索引 / 携带定位键以访问行的其他索引 |
| UTC / 本地日历值 | 统一时刻基准 / 某地区墙上时间或日期,不自带时区 |
| DDL / DML | 改变结构的建表和 ALTER 等 / 插入、更新、删除等数据操作;有些分类也把 SELECT 归入 DML |
| INSTANT / INPLACE / MDL | 结构变更算法名 / 另一种算法名,不等于不重建 / 协调表定义使用与修改的元数据锁 |
参考资料
- MySQL 8.4 Reference Manual:Clustered and Secondary Indexes、InnoDB Row Formats、CHECK Constraints、Date and Time Data Types、Online DDL Operations。
- 适用条件:约束错误、字符比较、驱动映射和 ALTER 实例行为应通过实例验证,错误号与耗时以实际执行为准。