一个注册接口上线一周,客服收到投诉:同一个手机号能注册两个账号,登录时随机进其中一个,订单记录分散在两个账号下。开发查了代码,说「注册前明明查过手机号存不存在」。问题出在并发:两个请求同时查,都发现不存在,然后都插入了。应用层的「先查再插」不是原子操作,中间有时间窗口。
同一周还发现库存表有负数、订单状态有空值、金额出现 59.999999。这些都不是查询写错,而是建表时没把规则交给数据库。这篇讲清楚:哪些约束该建、怎么建、它们各自的代价是什么,以及为什么「改表」在生产环境是件需要挑时间的事。
场景走查:三张表的约束清单
回到订单库的三张表,逐列问三个问题:这一列能不能为空?这一列能不能重复?这一列的值有没有范围?答案就对应 NOT NULL、UNIQUE、CHECK(或外键)。
主键:自增还是 UUID
InnoDB 的主键不只是「唯一身份证」,它还决定数据在磁盘上的组织方式——表就是按主键顺序存放的一棵 B+ 树。这带来两个结论:主键要尽量短,插入要尽量有序。
自增整数 BIGINT UNSIGNED AUTO_INCREMENT 同时满足这两点:8 字节、单调递增、新行总追加在末尾。UUID 是 36 个字符(存成二进制也要 16 字节),而且随机分布,新行会插到树的中间,引发页分裂和随机写。数据量大时,UUID 主键的写入吞吐和索引体积都会明显吃亏。
UUID 的优势是「不依赖数据库生成」,分库分表、客户端离线生成、避免主键泄露业务量时有用。折中方案是用有序 UUID(UUIDv7)或雪花 ID,既有全局唯一性,又保持趋势递增。
自增主键也有代价:id 会有空洞,删过的值不会复用;批量插入时可能一次跳很多号。这些都不影响正确性,但别拿 id 的连续性判断数据有没有丢。复合主键在关联表里仍然有用,比如 (order_id, product_id) 能天然防止同一订单里重复商品,比先查再插更可靠。
UNIQUE 索引,物理主键仍用自增 id。唯一、非空、默认值:最便宜的三道防线
唯一约束是解决重复注册的正解。给手机号加 UNIQUE KEY uk_phone (phone) 之后,第二个并发插入会直接失败:
INSERT INTO customers (phone, name, city)
VALUES ('13800000007', '重复手机号', '测试');
-- ERROR 1062 (23000): Duplicate entry '13800000007' for key 'uk_phone'
应用层捕获 1062,返回「手机号已注册」即可。这比「先查再插」可靠,因为唯一性检查发生在插入的同一时刻,没有时间窗口。
复合唯一约束能表达更细的规则,比如订单明细表用 UNIQUE(order_id, product_id) 防止同一订单里重复商品。单列唯一索引做不到这一点,它只能保证「商品 id 全局不重复」。设计时先问「唯一性是针对哪个组合」,再决定索引的列。唯一索引越多,写入时要维护的检查也越多,别为了「顺手」给每列都加一个。
但唯一索引有个容易误解的行为:它允许多个 NULL。实测一张带 UNIQUE(code) 的表,连续插入两条 code = NULL 都能成功,因为 SQL 认为「未知不等于未知」。如果业务上「空值也只能有一条」,就得把列设成 NOT NULL,用空字符串或特殊值表示「没有」,再靠唯一索引兜住。
CREATE TABLE t_uniq (
id INT PRIMARY KEY AUTO_INCREMENT,
code VARCHAR(10) NULL,
UNIQUE KEY uk_code (code)
) ENGINE=InnoDB;
INSERT INTO t_uniq (code) VALUES (NULL), (NULL), ('A');
-- 三行全部插入成功,COUNT(code) 只有 1
NOT NULL 的价值不只是「防止空值」,还能让优化器少考虑一种情况,让索引统计更准。能用 NOT NULL DEFAULT '' 表达的列,就别留 NULL。DEFAULT 则解决「插入时没给值」的问题,比如 status VARCHAR(16) NOT NULL DEFAULT 'created',但它只在「完全没提供该列」时生效,显式传 NULL 仍然会被 NOT NULL 拦下。
DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 很方便,但它们依赖数据库服务器的时区。应用和数据库时区不一致时,created_at 可能和业务预期差 8 小时。更稳的做法是应用生成时间,或统一用 UTC 存储、展示时再转换。
软删除和唯一索引会打架:手机号加了唯一索引,用户注销后逻辑删除,这个手机号就再也注册不了。要么在删除时改写手机号(加后缀),要么把唯一约束改成「未删除记录唯一」的部分索引;MySQL 没有部分索引,只能用生成列或应用层绕。
翻车现场:约束顺序错了,迁移卡死在半路
某次「账号治理」需求:用户表里有一批历史数据手机号为空,产品要求「手机号必须唯一且不能为空」。开发写了三步迁移脚本,第一步补空值,第二步加唯一索引,第三步改非空。执行到第一步就停了:
UPDATE users SET phone = '' WHERE phone IS NULL;
-- ERROR 1062 (23000): Duplicate entry '' for key 'uk_phone'
为什么?表上早就有一个 uk_phone 唯一索引(唯一索引允许存多个 NULL,所以历史数据一直没报错),但一旦把多行 NULL 都改成空字符串,第二个空串就撞了唯一约束,整条 UPDATE 语句回滚。于是变成一个死结:不补空值就加不了 NOT NULL,补空值又撞唯一索引。
如果第二步是先加唯一索引,同样会卡:实测对已有重复手机号的数据加约束,直接报 1062 Duplicate entry '13800000001' for key 'uk_phone'。ALTER TABLE 是原子的,表结构不会停在中间状态,但发布窗口已经耗掉了,而且下一次重跑还是同样的报错——这就是「迁移卡死」的典型形态:不是数据库坏了,是数据和约束的顺序不对。
正确顺序是「先看清数据,再动结构」:
-- 1. 先查重,确认到底有多少脏数据
SELECT phone, COUNT(*) AS cnt
FROM users
WHERE phone IS NOT NULL
GROUP BY phone
HAVING COUNT(*) > 1;
-- 2. 查空值
SELECT COUNT(*) FROM users WHERE phone IS NULL;
-- 3. 用能保证唯一的临时值回填,而不是统一成 ''
UPDATE users SET phone = CONCAT('legacy-', id) WHERE phone IS NULL;
-- 4. 处理完重复再改结构
ALTER TABLE users MODIFY phone VARCHAR(20) NOT NULL;
ALTER TABLE users ADD UNIQUE KEY uk_phone (phone);
回填用 CONCAT('legacy-', id) 而不是空串,是因为 id 唯一,回填结果天然不重复;业务上再约定「legacy- 开头视为未绑定」,登录时按规则拦截。如果直接改成 NOT NULL 而不回填,实测报 1138 Invalid use of NULL value,同样是一次失败的发布。
约束加错还有一种更隐蔽的翻车:约束加上了,但应用层没接住错误码。唯一索引上线后,并发注册从「偶发两个账号」变成「偶发 500」。数据库这边是正确的——第二条插入必须失败——但代码里只有 try/except 打印日志,没有把 1062 翻译成「手机号已注册」。这类问题不是约束的错,是「加了防线却没准备接住」。
SELECT 查重、查空值、估算影响行数,再在预发库上完整演练一遍,最后才在生产执行。只准备 ALTER 语句、不准备数据清理语句,是这类事故最常见的原因。外键:要便利还是要灵活
外键能保证「订单里的客户一定存在」。实测插入一个不存在的客户 id:
INSERT INTO payments (order_id, paid_amount) VALUES (999999, 10.00);
-- ERROR 1452 (23000): Cannot add or update a child row:
-- a foreign key constraint fails
它还能配置级联行为:ON DELETE CASCADE 删客户时连带删订单,ON DELETE SET NULL 把订单的客户 id 置空,ON DELETE RESTRICT(默认)直接拒绝。级联删除很危险,删一个客户可能连带删掉几年的订单,业务上通常不允许。
ON DELETE CASCADE 看起来省事,但删除的连锁反应很难预估:删一个客户可能带走订单、订单又带走明细,一条语句删掉几万行,锁和 binlog 都会暴涨。生产环境更常见的做法是 RESTRICT,强制应用层显式处理关联数据,删什么、留什么都能审计。
不用外键的理由也很实际:分库分表后父子表可能在不同库,数据库层面没法约束;批量导入时要按依赖顺序处理,否则一直报 1452;高频写入时每次插入都要检查父表,增加锁和开销。替代方案是应用层校验加定时对账,比如每天跑一次「孤儿订单」检查:
SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;
结论不是「必须用」或「必须不用」,而是:单库、数据一致性优先、写入量不大,用外键省心;分片、海量写入、频繁导入,应用层兜底加对账。两种做法都要有,最怕的是「以为有约束,其实没有」。
外键还要求两边类型、字符集、排序规则完全一致,否则建表时就会报错;父表的被引用列必须有索引,否则 MySQL 会自动帮你建一个。这些细节在迁移或改表时最容易漏,报错信息也往往不直接指向真正原因。
CHECK:版本差一个,行为差很多
库存不能为负、金额必须大于 0,这类范围规则可以用 CHECK:
CREATE TABLE stock (
id INT PRIMARY KEY,
qty INT NOT NULL,
CONSTRAINT ck_qty CHECK (qty >= 0)
) ENGINE=InnoDB;
注意版本:MySQL 8.0.16 起 CHECK 才真正执行;8.0.16 之前只解析语法、直接忽略。我在本机 8.0.12 上实测,插入 qty = -1 成功了,SHOW CREATE TABLE 里也能看到约束定义,但它不生效。如果你的环境是 8.0.12 到 8.0.15,别指望它。
UPDATE stock SET qty = qty - ? WHERE id = ? AND qty >= ?,再看受影响行数是否为 0。这个写法在事务篇会展开,它同时也是防超卖的关键。类型选错,约束也救不回来
金额用 DECIMAL(10,2),不要用 FLOAT。实测把 0.1 插入一百行再求和:FLOAT 得到 10.000000149011612,DECIMAL 得到 10.00。单笔看不出来,几十万笔对账时就是实打实的差额。汇率、税率这类需要精确小数的场景,同理。
时间类型要分清。DATETIME 存字面时间,范围到 9999 年,不随时区转换;TIMESTAMP 存 UTC 时间戳,显示时按会话时区转换,范围只到 2038-01-19 03:14:08。实测插入 2040-01-01 到 TIMESTAMP 列,直接报 ERROR 1292;同样的值插 DATETIME 没问题。需要跨时区统一时间线用 TIMESTAMP,需要存「当地营业时间」这类字面时间用 DATETIME。
字符集统一用 utf8mb4。MySQL 的 utf8 是三字节实现,存不下 emoji 和部分生僻字,表现为插入报错或截断。库、表、列、连接四处的字符集都要一致,任何一处是 utf8 都可能出问题。长度也别乱给:VARCHAR(255) 不是万能,按实际需要留余量,过长的索引会变大甚至建不出来。
整数类型也别乱选。INT 上限约 21 亿,订单量大的表几年就可能碰到,主键和计数列优先 BIGINT UNSIGNED;TINYINT 适合状态位、布尔值,比 INT 省空间,索引也更小。INT(11) 里的 11 只是早期客户端的显示宽度,不影响取值范围,别以为写了 11 就能存 11 位数字。
改表为什么要挑时间
建表时想清楚,比上线后改表便宜得多。加索引、改列类型、加非空约束,往往要重建表或扫描全表。MySQL 8.0 支持 Online DDL,很多操作可以在线进行,但仍有代价:占磁盘、占 IO、持有元数据锁,大表上可能持续几分钟到几小时。
经验做法:大表变更放在低峰期;先用 ALGORITHM=INPLACE 或 INSTANT 试探,失败再退回;变更前确认磁盘余量和主从延迟;加索引用 CREATE INDEX 而不是 ALTER TABLE 混合多个操作。线上库不要执行没有回滚方案的变更。
MySQL 8.0 的 ADD COLUMN 在很多情况下支持 ALGORITHM=INSTANT,只改元数据,不重建表;但改列类型、加非空约束、加索引通常仍要重建或扫描。执行前先查官方文档的 Online DDL 矩阵,别凭经验。大表变更还要关注 lock_wait_timeout 和元数据锁,避免把业务查询堵住。
翻车现场:一条 ALTER 把整张表堵住
另一个故事发生在上线窗口:给订单表加一个索引,DBA 在控制台敲下 ALTER TABLE orders ADD INDEX idx_status_amount (status, amount),然后业务告警开始响——订单查询全部超时,但数据库 CPU 并不高。原因不在 SQL 慢,而在元数据锁:ALTER 需要独占的 MDL,它先等所有正在执行的查询结束,而后续新来的查询又排在它后面,形成队列。一条 DDL 把整张表「堵」住了,堵的不是数据行,是表结构的访问权。
本机 1998 行的表上,这个操作很快:实测 ADD COLUMN tmp_col INT NULL DEFAULT 0 用了 41.36 ms,ADD INDEX idx_tmp_col 用了 76.21 ms。但这两个数字只能说明量级,不能外推。索引构建要读全表、排序、写新索引页,成本与行数、索引宽度、磁盘 IO 都相关;表到几千万行时,同样的语句就是分钟级,期间还要占额外磁盘。
写入侧的影响更持久。实测插入 20000 行:没有二级索引 193.8 ms,加 4 个二级索引后 561.9 ms,约 2.9 倍。索引不是「查得快」的免费午餐,每一列被索引,写入就要多维护一棵 B+ 树。所以「先加个索引试试」这种做法,需要同时看读和写两边。
线上做大表变更,几个动作能把风险降下来:
- 先确认能不能走
ALGORITHM=INSTANT或INPLACE,ALGORITHM=COPY会重建表,最重。 - 把
lock_wait_timeout调小(比如 5 秒),拿不到锁就失败重试,别让它无限排队堵住业务。 - 变更期间盯
Threads_running、慢日志和主从延迟;有异常立刻停。 - 大表优先用 gh-ost、pt-online-schema-change 这类工具,它们用影子表逐步拷贝,把锁的持有时间压到毫秒级。
- 加索引单独发一条语句,不要和改列类型、加约束混在一个
ALTER里,失败时不好定位。
本机是单机 8.0.12,上面这些耗时都是空载测出来的,没有主从、没有并发写入。真实环境里同样的 DDL 还要叠加复制开销:主库执行完,binlog 传到从库再执行一遍,从库延迟会在这段时间明显上涨。ALTER 期间业务写请求变慢,很多时候不是主库慢,是从库跟不上。
ADD COLUMN 的耗时(41.36 ms),ALGORITHM=INSTANT 在这个版本已经支持;8.0.29 之后对「行内位置」等场景又加了限制。想观察 MDL,可以开两个会话:A 执行 BEGIN; SELECT * FROM orders LIMIT 1; 不提交,B 执行 ALTER TABLE orders ADD COLUMN c INT NULL;,再用第三个会话查 performance_schema.metadata_locks,应该看到 ALTER 在等共享锁释放。具体输出和等待关系以你的版本为准,这段未逐项实测,标记为待确认。现象对照
| 现象 | 真实原因 | 第一步查什么 |
|---|---|---|
| 同一手机号注册出两个账号 | 没有唯一约束,应用层「先查再插」有并发窗口 | SHOW CREATE TABLE 看有没有 UNIQUE,再查重复数据 |
| 订单指向一个不存在的客户 | 没建外键,应用层校验漏了或被绕过 | LEFT JOIN 找孤儿数据,检查写入路径 |
| 库存出现负数 | 只有应用层判断,没有条件更新或 CHECK | 看更新语句有没有 AND qty >= ? |
| 金额累计差几分钱 | 用了 FLOAT/DOUBLE | 看列类型,改成 DECIMAL 并迁移历史数据 |
| 2040 年的到期时间存不进去 | TIMESTAMP 的 2038 上限 | 看列类型,远期时间改用 DATETIME |
| emoji 或生僻字写入失败 | 字符集是三字节 utf8 | 查库、表、列、连接四处的字符集 |
| 上线加索引后写入变慢 | 索引维护成本,或变更期间锁表 | 看变更时段的慢日志、锁等待和主从延迟 |
| 迁移脚本报 1062 或 1138 | 表里已有重复值或 NULL 值,数据没清理就改结构 | 先按列分组查重、查空值,用唯一临时值回填 |
| 执行 ALTER 期间整表查询排队 | 元数据锁被 DDL 独占,后续查询排在它后面 | 看 metadata_locks 与 Threads_running,调小 lock_wait_timeout |
边界与代价
约束不是越多越好。每个唯一索引都要在插入和更新时维护一棵 B+ 树,写多读少的表上,索引过多会明显拖慢写入。外键检查、CHECK 校验也都有 CPU 开销。约束应该建在「业务上真的不能违反」的规则上,而不是「最好别违反」的规则上。
还有一个现实边界:约束只能保证「经过数据库的写入」合法。批量导入、数据修复脚本、DBA 手工操作如果绕过了应用层,仍可能写进脏数据;分库分表后跨库的父子关系数据库也管不了。约束是最后一道防线,不是唯一一道。
约束的成本也要算清楚。唯一索引让每次插入多一次 B+ 树查找和写入,高并发注册场景下会带来锁竞争;外键检查在父表上加共享锁,父子表写入频繁时容易互相等待;NOT NULL 几乎没成本,但改历史数据要扫全表。把约束建在「违反会出事」的列上,剩下的靠应用层和对账兜底,比「每列都加约束」更现实。
最后提醒一句版本差异:MySQL 8.0.16 之前 CHECK 只解析不执行,PostgreSQL 则一直执行,且支持部分索引(CREATE INDEX ... WHERE)。同一份「软删除后手机号可复用」的设计,在 PostgreSQL 用部分唯一索引就能表达,在 MySQL 只能靠生成列或应用层绕。迁移前先确认目标库支持什么,比事后改表便宜。
小结
主键用短而有顺序的自增整数,业务唯一值交给 UNIQUE;能 NOT NULL 就别留 NULL,默认值只是兜底;外键按团队的数据一致性要求和分片策略决定;CHECK 注意 8.0.16 的版本线;金额用 DECIMAL,时间分清 DATETIME 和 TIMESTAMP,字符集统一 utf8mb4。
当天可以自己验证的练习:
- 建一张带
UNIQUE(phone)的表,并发插两条相同手机号,观察其中一条报 1062。 - 在唯一索引列上插两条 NULL,确认都能成功,再改成
NOT NULL重试。 - 用
FLOAT和DECIMAL各存 100 个 0.1 并求和,记录两个结果。 - 往
TIMESTAMP列插入 2040 年的值,记录报错;换DATETIME再试。 - 给一张有数据的表加外键,先插入孤儿行观察 1452,再删除父行观察限制。
下一篇讲索引怎么用:哪些查询走索引。约束保证数据对,索引保证查得快。但索引不是「加上就灵」:函数包住列、类型隐式转换、OR 混条件,都会让索引白建。接下来逐条验证哪些写法会让索引失效。