上周处理了一个线上事故模拟订单表里同一笔订单号出现了三次金额三成是 NULL状态字段五六个写法并存。排查之后发现建表语句里除了一个自增主键其他所有字段全靠应用层自觉。那一刻我特别想感慨MySQL 的表约束不是教科书考点而是生产环境里数据质量的最后一道闸门。这篇文章我想把 MySQL 的表约束彻底讲透。现有六种约束——NOT NULL非空、DEFAULT默认值、UNIQUE唯一、PRIMARY KEY主键、CHECK检查、FOREIGN KEY外键——分别解决什么问题、怎么写、有什么版本差异和隐藏坑以及表已经上线之后补约束该怎么操作都会覆盖到。适合刚把增删改查跑顺、准备设计真实表结构的开发者也适合被老系统的脏数据折磨过一遍又一遍的运维和开发。1. 约束不是附加功能它是数据的守门员1.1 一段没有约束的脏数据现场先还原一个我见过很多次的场景。某项目的订单表order_no 允许为 NULL于是有人直接插入了一条没有订单号的记录。起初没什么影响等到对账、导出、和支付系统做回调匹配的时候这条 NULL 记录让所有 join 和 count 全对不上前端显示undefined客服后台一片问号。这不是程序 bug而是表结构打一开始就没拦住非法输入。再看另一个经典场景订单表里的 user_id 没有外键某天运营在后台误删了一个测试用户这个用户名下几十条订单全部变成了孤儿数据。业务查询用户订单时自然少了一截但你在数据库里怎么数都数不出问题因为订单还在只是关联断了。数据不一致的这种脏比字段为 NULL 更隐蔽、更难排查。还有状态字段。没有 CHECK 约束的时候status 今天写 paid明天写 PAID后天写 已支付统计报表里 WHERE status paid 永远比实际少几行还得写一堆 CASE WHEN 来兜底。这类问题不是在某个时间点突然爆雷而是持续性地污染统计口径和分析结论。这些例子说明一个道理约束不是给开发添麻烦。数据库是数据落地的最后执行者应用层校验再严也架不住漏网之鱼、临时脚本、运营手工改库。把规则写在表结构里等于让数据库替每个人把关。1.2 MySQL 的六种约束速览MySQL 的约束可以分成两类一类管单个字段的值合不合法另一类管字段之间、表与表之间的关系对不对。先看一张速览表约束类型作用关键字声明位置非空约束字段值不允许为 NULLNOT NULL列级默认值约束未显式赋值时填充预置值DEFAULT列级唯一约束字段或字段组合的值不重复UNIQUE列级/表级主键约束行的唯一标识隐含非空加唯一PRIMARY KEY列级/表级检查约束字段值必须满足给定表达式CHECK列级/表级外键约束保证子表与父表引用关系完整FOREIGN KEY表级NOT NULL、DEFAULT、UNIQUE 这三板斧管的是这个字段能不能为空、默认变成什么、会不会重复。PRIMARY KEY 决定了一行数据在整个表里的身份。CHECK 管的是字段取值白名单或范围。FOREIGN KEY 管的是表和表之间的引用关系。后面几节我逐个拆解重点放在为什么和踩坑上。2. 最基础的三道防线NOT NULL、DEFAULT 与 UNIQUE2.1 NOT NULL 和 DEFAULT 是黄金搭档别拆开用先看一个常见误解字段加上 NOT NULL 就一定不会出现脏数据了只说对了一半。如果一个字段声明为 NOT NULL 但没有 DEFAULT插入数据时不显式给值在 MySQL 严格的 sql_mode 下会直接报 1364 错误Field doesnt have a default value但在非严格模式下MySQL 会悄悄按照字段类型的隐式默认值填充字符串填空串、数字填 0。很多老项目跑着跑着出现一堆 0 和空串原因就在这。所以 NOT NULL 几乎总是要和 DEFAULT 一起设计。比如时间字段created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPcreated_at 靠 DEFAULT 在插入时自动带上当前时间updated_at 靠 ON UPDATE 在行更新时自动刷新。这套组合是建表的基本功也是我审查别人表结构时第一个看的地方。这里有一个经验要强调DEFAULT 的取值必须语义明确。业务上没有填写年龄应该用 NULL 表示未知那就别写成 DEFAULT 0如果业务要求必须有值再考虑 0 或某个哨兵值。很多系统后来查不出到底是真 0 还是没填就是因为当年为了省事把 DEFAULT 写成了 0。另外提醒一句DATETIME 类型从某个版本开始才支持直接用 CURRENT_TIMESTAMP 做默认值老项目里那种靠应用层手动拼时间的写法在设计新表时完全可以退休了。2.2 UNIQUE 约束的 NULL 行为是个经典陷阱UNIQUE 约束保证字段或字段组合的值不重复但它有一个经常被忽略的特性MySQL 允许在 UNIQUE 列里插入多行 NULL。原因很简单在 MySQL 看来 NULL 表示未知NULL 不等于 NULL所以每一行 NULL 都是新的。这个特性对业务影响很大。假设 user 表的 email 字段加了 UNIQUE 约束注册时用户没有填邮箱应用层往库里写 NULL。第一个用户能注册第二个也能因为 NULL 不算重复。如果运营用 SELECT COUNT(DISTINCT email) 去判断邮箱重复结果会和直觉完全不同。从 SQL Server 转过来的同事最容易在这里踩坑——SQL Server 的唯一约束默认只允许一个 NULL。应对办法也简单如果业务上要求要么必填要么只有一个 NULL就把可空字段的 NULL 在应用层统一转成空字符串处理如果一个都不能重复那这个字段就不该允许 NULL。2.3 UNIQUE 和普通索引的分工别搞混UNIQUE 约束在 MySQL 里落地时会自动创建一个唯一索引既做约束又加速查询。普通索引INDEX只加速查找不限制重复。很多刚入门的同学会有疑问那我在登录账号上建普通索引不也能加快查询吗能但这意味着数据库默许了账号重复等于把去重的责任全部推给应用层。我的建议很直接凡是业务上不允许重复的字段——账号、邮箱、手机号、订单号——直接建 UNIQUE 约束只是需要加速查询、允许重复的字段比如订单表的用户 ID、文章表的状态建普通索引就够了。判断标准就一句话重复会不会造成业务事故。会就上 UNIQUE不会普通索引即可。3. 主键InnoDB 表的身份证到底怎么选3.1 自增主键为什么是默认选择主键约束合并了 NOT NULL 和 UNIQUE一行一个主键值不能为空也不能重复。在 InnoDB 里主键还有一个隐藏身份它是聚簇索引也就是说表数据的物理存储顺序就是主键顺序。这意味着主键生成方式直接影响写入性能。自增主键是单调递增的新行的主键值总是比已有的大写入时追加在索引末尾页分裂少、随机 IO 少。而如果把 UUID 字符串当主键值完全随机每次插入都可能落在索引中间的某个位置触发页分裂和碎片整理。同样是十万行数据UUID 主键的表写入耗时可能是自增主键的好几倍索引体积也更大。我见过不少项目选择 UUID 主键是为了防枚举或分布式环境不依赖自增这个理由存在但和性能的取舍要讲清楚。如果确实需要分布式唯一 ID优先考虑带时间序的生成算法类似雪花 ID、UUIDv7 这类既保持分布式的天然优势又把索引写入的随机性问题压到最低。3.2 业务主键和数据库主键是两回事很多表设计里会把订单号直接设为 PRIMARY KEY我觉得这通常是设计错误。订单号是业务上暴露给外部的编号要面向用户、要进日志、要参与对外查询它未来可能因为业务规则变化而需要调整格式或长度。而数据库主键只是行的内部标识最好是一个和业务完全无关、稳定不变的数字。让订单号做 UNIQUE 约束来保证业务唯一性让自增 id 做主键来管物理存储各司其职将来改订单号规则时你才会感谢当初的设计。复合主键则要想清楚查询模式再决定。比如选课表 PRIMARY KEY (class_id, student_no)如果业务主要按 student_no 反查学生选了哪些课这个复合主键帮不上忙还得额外建索引。复合主键遵循最左前缀原则最左边的列一定要是高区分度的列否则索引效率很低。我见过把 status 放在复合主键第一列的查询性能差到离谱后来还是拆了单列主键加普通索引。4. CHECK 与外键强约束的正确打开方式4.1 CHECK 约束在 8.0.16 前后是两个世界很多老资料会说 MySQL 不支持 CHECK 约束这个说法已经过时了。MySQL 8.0.16 开始CHECK 约束被真正强制执行在此之前MySQL 虽然在建表语法里接受 CHECK 子句但只是解析后直接忽略并不会拦截非法数据。所以如果你还在用 5.7 或更早的版本不要在表结构上指望 CHECK老老实实靠应用层校验。如果你已经用 8.0 及以上CHECK 是控制字段取值白名单的好工具。举两个例子status VARCHAR(16) NOT NULL DEFAULT pending, CONSTRAINT chk_orders_status CHECK (status IN (pending, paid, refunded, closed)), CONSTRAINT chk_orders_amount CHECK (amount 0)加了这两个约束之后任何一条把 status 写成 PAID 或者把 amount 写成负数的 INSERT 和 UPDATE都会被数据库直接拒绝报 3819 错误Check constraint violated。这对防止应用层漏校验和运营手工改库时手滑很有价值。一个细节CHECK 约束只拦截约束添加之后的新写入和更新不会去回溯清理表里已经存在的脏数据。换句话说在 8.0 上给一张满是脏数据的表加 CHECK加约束本身通常不会报错但后续对脏数据行的任何 UPDATE 都可能突然报 3819。上线前先把历史数据洗干净是动手前的必修课。4.2 FOREIGN KEY 的级联策略CASCADE、SET NULL、RESTRICT 怎么选外键约束只在 InnoDB 引擎下生效它保证子表引用父表的每一行都真实存在于父表。删除父表行时外键的级联策略决定了子表怎么反应常见的有三种RESTRICT / NO ACTION默认行为父表有子表引用时禁止删除或更新直接报 1451 错误CASCADE删除或更新父表行时子表引用行跟着删除或更新SET NULL父表行删除时子表外键列被置为 NULL前提是该列允许为空。级联策略没有标准答案取决于业务语义。还是拿订单举例订单是核心财务数据用户不能在有订单的情况下被删除所以 user_id 外键应该用 ON DELETE RESTRICT。而评论、站内信这类附属数据主内容被删了留着没意义用 CASCADE 反而省事或者出于审计需求保留记录那就把删除改成逻辑删除字段而不是物理删行。SET NULL 要特别注意这个策略要求外键列本身允许 NULL。我在代码评审里见过不少把外键列写成 NOT NULL 却配置 ON DELETE SET NULL 的表最后删除父行时直接报错。设计阶段就要想清楚这个关系是不是可断开的断了之后子表记录还有没有存在意义。外键也不是免费的。每次在子表写入、更新或删除InnoDB 都要去父表检查引用是否存在会引入额外的锁和 IO。高并发写入的核心链路外键的检查开销和锁竞争会被放大这也是很多团队在核心表上刻意不用外键的原因。4.3 分库分表之后外键的限度要重新评估外键是数据库层面的完整性机制前提是子表和父表在同一个数据库实例里。一旦做了分库分表订单表在订单库、用户表在用户库外键就彻底失效跨库跨节点的引用完整性只能由应用层通过事务、消息和补偿机制来保证。所以外键用不用不是一个好不好的道德问题而是部署架构决定的工程问题。单体应用、数据量可控的中小系统外键是性价比极高的数据质量保障分布式架构、核心链路高并发外键反而可能成为性能和扩展的瓶颈。我见过一些团队为了避免日后分库分表从第一天起就在所有表上禁用外键这有点矫枉过正——等到真的需要分库分表时再清除外键成本完全可控但在此之前外键替应用层挡住了大量脏数据。5. 从零设计一张带约束的订单表完整实操5.1 把业务需求翻译成约束清单设计规范的表结构本质上是一道翻译题把业务规则翻译成数据库能执行的约束。就以一个模拟订单场景为例需求是这样的每一笔订单必须有唯一的业务订单号订单必须属于某个真实存在的用户订单状态只能是 pending、paid、refunded、closed 四种订单金额不允许为负数有订单的用户不允许被直接删除。翻译成约束清单就是order_no 加 UNIQUE 约束user_id 加 NOT NULL 并建外键引用用户表级联策略选 RESTRICTstatus 加 CHECKamount 加 CHECK。这四条需求每一条都对应一个明确的约束设计时对照检查就不会出现字段裸奔的情况。5.2 完整建表 SQL 逐行解读最终建表语句如下CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, status VARCHAR(16) NOT NULL DEFAULT pending, amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT, CONSTRAINT chk_orders_status CHECK (status IN (pending, paid, refunded, closed)), CONSTRAINT chk_orders_amount CHECK (amount 0) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4;逐行看id 是自增主键纯粹的行标识不参与任何业务逻辑order_no 是业务订单号用 uk_order_no 这个唯一约束保证不重复user_id 上的外键 fk_orders_user 同时保证了用户必须存在和有订单的用户删不掉status 和 amount 两个 CHECK 约束把取值范围钉死created_at 和 updated_at 交给数据库自动维护。外键这里有个细节值得留意InnoDB 要求外键的引用列子表和被引用列父表都存在合适的索引。子表 user_id 上如果事先没有索引MySQL 会自动给它补一个父表 users.id 因为有主键天然满足条件。但如果你引用的是父表的非主键列一定要确认那个列有索引否则建外键会直接报 1215 错误。5.3 约束命名规范给自己留后路约束命名看起来是小事实际上非常影响后续维护。MySQL 里删除外键约束、检查约束都必须知道约束的名字如果建表时不显式命名系统会自动生成一堆类似 orders_ibfk_1 的名字数量一多根本分不清谁是谁。我习惯的命名规则是主键就叫 PRIMARY唯一约束用 uk_ 前缀加字段名比如 uk_order_no外键用 fk_ 前缀加子表_父表比如 fk_orders_userCHECK 用 chk_ 前缀加表名和含义比如 chk_orders_status普通索引用 idx_ 前缀。这套规则简单、自解释几个月后回头维护表结构看名字就能知道每个约束在管什么。6. 上线之后想改约束ALTER TABLE 操作与报错排查6.1 增删约束的标准语法表已经上线了数据也跑了一两个月这时候想补约束靠的就是 ALTER TABLE。语法其实不复杂-- 添加 CHECK ALTER TABLE orders ADD CONSTRAINT chk_orders_status CHECK (status IN (pending, paid, refunded, closed)); -- 删除 CHECK ALTER TABLE orders DROP CHECK chk_orders_status; -- 添加唯一约束 ALTER TABLE employees ADD UNIQUE KEY uk_emp_email (email); -- 删除唯一约束通过 DROP INDEX 删除因为 UNIQUE 约束底层就是唯一索引 ALTER TABLE employees DROP INDEX uk_emp_email; -- 添加外键 ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id); -- 删除外键 ALTER TABLE orders DROP FOREIGN KEY fk_orders_user; -- 给字段加非空 ALTER TABLE orders MODIFY COLUMN user_id BIGINT UNSIGNED NOT NULL;初学者最容易搞混的是删除语法删除普通索引用 DROP INDEX删除唯一约束也是 DROP INDEX删除外键是 DROP FOREIGN KEY删除 CHECK 是 DROP CHECK删除主键是 DROP PRIMARY KEY。为什么唯一约束走 DROP INDEX因为 MySQL 里 UNIQUE 约束和唯一索引本质上是同一个东西没有单独的 DROP UNIQUE 语法。6.2 加约束失败的典型报错与其解法给线上表加约束最大概率遇到的坑是表里已经有脏数据。约束是对未来的承诺但对过去的数据同样要负责MySQL 发现存量数据违反约束时加约束操作会失败。常见的报错和根因如下报错含义典型处理ERROR 1062 (23000): Duplicate entry已有重复值UNIQUE/主键加不上先查重复数据清洗后再加ERROR 1138 (22004): Invalid use of NULL value已有 NULLNOT NULL 加不上先 UPDATE 填充默认值再 MODIFYERROR 1452 (23000): Cannot add or update a child row已有孤儿数据外键加不上先处理不存在的引用再建外键ERROR 3819: Check constraint violated已有值不满足 CHECK 表达式先定位非法值并修正再加 CHECK以最常见的 1062 为例排查动作其实三行 SQL 就够SELECT order_no, COUNT(*) AS cnt FROM orders GROUP BY order_no HAVING cnt 1; -- 根据业务决定保留哪条多余的删除或改号 DELETE FROM orders WHERE id IN (...); ALTER TABLE orders ADD UNIQUE KEY uk_order_no (order_no);我的原则是任何约束操作之前先把对应字段的 SELECT 查一遍。加非空之前查 NULL加唯一之前查重复加外键之前查孤儿加 CHECK 之前查范围之外的值。这一步十分钟的检查能省下半夜处理报警的时间。6.3 外键报错的完整排查链路外键相关的报错是 ALTER 操作里最绕的我拆一个完整链路。假设线上执行删除用户操作时报错ERROR 1451: Cannot delete or update a parent row: a foreign key constraint fails (shop.orders, CONSTRAINTfk_orders_userFOREIGN KEY (user_id) REFERENCESusers(id))第一步看报错里给出的约束名和表名能立刻定位是 orders 表的 fk_orders_user 约束挡住了删除。第二步确认级联策略SHOW CREATE TABLE orders 查看外键定义发现是 ON DELETE RESTRICT说明设计上就是不允许删有订单的用户。第三步查询引用数据量SELECT COUNT(*) FROM orders WHERE user_id 目标ID。第四步决定方案要么给用户做逻辑删除而不是物理删除要么先把订单归档或迁移要么修改外键为 ON DELETE CASCADE——最后这个方案不推荐风险太大。再看加外键失败的场景ALTER TABLE 报 ERROR 1215: Cannot add foreign key constraint。这种情况的根因排查顺序非常重要通常按下面三步走先对比数据类型和字符集子表 user_id 如果是 INT父表 id 是 BIGINT或两边字符集、排序规则不一致都会被拒绝再检查父表被引用列是否有索引父表被引用列不是索引InnoDB 直接拒绝建外键最后检查子表已有数据子表里有父表不存在的 user_id加外键会被 1452 拒绝。这套链路走一遍95% 的外键问题都能在十分钟内定位完。剩下那 5%多半是字符集混用或者约束名冲突这类环境问题用 SHOW CREATE TABLE 把两个表结构摆在一起对比很快也能看到端倪。最后补一句个人经验。如果你接手了一张跑了两三年的老表第一反应千万别是把所有约束一次性都加上。先挑唯一性和非空这两类约束直接决定业务数据能不能信跑稳定了再加外键和 CHECK。每加一个约束之前先跑一条 SELECT 把违反规则的行捞出来和业务方确认清洗方案再执行 ALTER。数据库约束这东西是在事情还没发生时就建好的防御工事等数据烂了再补成本和勇气都不是平常能想象的。