两年前我接手了一套经营一年多的订单系统做代码 review 的时候顺手打开生产订单表查出三条 order_no 完全一样的记录还有一批 order_status 是 NULL 的单子user_id 也能对上不存在的用户。当时我就在想这套系统能跑到现在还没炸只能说是运气好。后来逐张表核对 DDL才发现问题不是业务代码写得烂而是建表时压根没把 MySQL 表的约束当回事——该唯一的不唯一该非空的字段全允许 NULL外键一个都没建。从那以后我对表约束这件事变得特别敏感。这篇文章不是给你背一遍《MySQL 官方文档》里的约束定义而是想把约束从建表、使用、踩坑到面试这条线完整串起来。不管你是刚学 MySQL 的学生还是写了两三年业务代码的开发又或者正在做数据库设计评审的 DBA都能从中看到一点自己踩过或即将踩的坑。约束不是 DDL 里的装饰品它决定了一张表的数据底线也决定了你后续写业务 SQL 时要花多少精力去兜底脏数据。1. 约束不是摆设它决定了一张表的数据底线1.1 我之前接手的那张“什么都能存”的表先说那张让我印象深刻的订单表。它的结构大概是这样的CREATE TABLE orders ( id BIGINT AUTO_INCREMENT, order_no VARCHAR(64), user_id BIGINT, amount DECIMAL(10,2), status VARCHAR(20), created_at DATETIME ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;猛一看好像也没什么字段名都能看懂。但问题全在细节里order_no 没有唯一约束所以业务上同一个订单号可以被插入多次status 没有 NOT NULL 和 CHECK所以业务代码一旦写错状态值NULL 或者任意字符串都能进表user_id 没有外键也不一定有对应真实用户amount 没有默认值也没有检查负数和 NULL 都能进来。你以为业务代码里做了校验确实做了但总有漏的情况。更麻烦的是这套系统在很长一段时间里根本没有统一的写入入口有的是后台任务在写有的是运营脚本在刷数据还有的定时任务在补单。任何一个入口没写好脏数据就进去了。等数据乱了以后你再回头想靠 SQL 去清洗既费劲又容易误伤。这就是约束的价值它是在数据库层面强制规定这张表能接受什么样的数据不依赖任何代码入口。业务校验是大部分时候有效约束是只要走 SQL 就一定拦得住。1.2 约束、索引与存储引擎的三角关系学约束之前很多人会忽略约束和索引、存储引擎之间的关系。实际上 MySQL 里不少约束并不是独立存在的它们底层依赖索引。主键约束PRIMARY KEY会自动创建一个唯一索引而且是 InnoDB 的聚簇索引唯一约束UNIQUE会自动创建一个唯一索引外键约束FOREIGN KEY要求参考列上必须有索引如果这个索引不存在InnoDB 会自动为外键列创建一个索引。所以你会发现你在建表时写了一个 UNIQUE 约束SHOW INDEX FROM 表名 里就会多出一个索引。约束是逻辑规则索引是实现这个规则的物理手段。两者相辅相成也常常共享同一个名字。再说存储引擎外键约束只有 InnoDB 真正支持。如果你用 MyISAM就算建表语句里写了 FOREIGN KEYMySQL 也会高抬贵手地忽略掉不会报错但外键完全不会生效。这在日常开发中是个容易踩的坑——很多人以为表建出来了约束就有了其实不一定。1.3 每一次 INSERT 的约束检查要付出多少成本约束不是免费的。每插入一条记录InnoDB 都要做一堆检查NOT NULL逐字段判断是否有 NULL 值主键 / 唯一约束通过索引检查当前插入的键值是否已经存在外键约束检查父表中的参考键是否存在并且根据隔离级别决定加什么锁CHECK 约束逐行计算检查条件。所以约束确实会带来性能开销。尤其在批量写入的场景下唯一约束和外键约束的检查会显著拉低写入吞吐。这也是很多高并发团队选择牺牲约束换性能的原因之一。但我想说一个观点对于绝大多数业务系统约束带来的性能损耗完全可以接受。少一次线上数据修复比多写几千条 SQL 的吞吐要有价值得多。你不能既想要强一致性又不想付任何代价。约束的检查成本本质上是为数据质量买的保险。2. 六类约束逐个拆解用法、语义和最容易踩的坑2.1 主键约束自增、UUID 还是业务字段判断标准不是唯一性主键约束是唯一性最强的约束不能为 NULL不能重复一张表最多只能有一个主键。InnoDB 里主键对应的索引就是聚簇索引所以主键的选择会直接影响表的数据组织方式。工作中最常见的三种主键方案是自增 BIGINT、UUID或类 UUID 字符串、业务自然主键。自增主键写入顺序和主键大小单调递增新数据插入时基本是在索引末尾追加页分裂少性能最好。但在分库分表后自增容易冲突需要用号段或者雪花算法。UUID 主键最大的好处是全局唯一分布式环境下不用考虑冲突。但 UUID 字符串长度长随机性强插入时 B 树要频繁做中间插入容易造成页分裂写性能明显差于自增。业务自然主键比如用身份证号、手机号之类的字段做主键省掉一个额外的主键字段。但这样的方案风险很大业务字段一旦变化主键也要跟着改代价非常大。我的经验是能加代理主键就加代理主键别让业务字段承担主键职责。主键应该是一个永恒不变的标识而不是一个可能被业务规则影响的属性。2.2 唯一约束NULL 带来的双重含义唯一约束UNIQUE表示列中的所有非 NULL 值必须唯一。这里的关键点在于MySQL 的 InnoDB 允许唯一约束的列存在多个 NULL 值。为什么因为 SQL 标准里NULL 表示未知两个 NULL 并不相等。所以唯一约束不拦截 NULL除非你用 NULLS NOT DISTINCTMySQL 8.0.16 起支持要求 NULL 也互相不重复。这在业务上会带来一个很典型的陷阱比如你要在 user 表上给 nickname 加唯一约束想实现昵称不能重复。但如果你允许 nickname 为 NULL那么用户不填昵称时所有未填昵称的用户都会被存成 NULL而且都能插入成功——因为多个 NULL 不算重复。结果你想要的唯一性只在用户填了昵称时才部分生效。所以如果你要的是这一列绝对不能有相同值那要先考虑是否应该把这一列设为 NOT NULL。否则唯一约束只约束了非 NULL 部分。另外也要小心空字符串和 NULL 的区别。空字符串 是一个确定的值唯一约束会拦截第二条 但不会拦截 NULL。这个差异经常导致线上莫名其妙的重复数据问题。2.3 非空与默认约束数据质量的隐形守门员NOT NULL 和 DEFAULT 是两种最容易写也最容易被忽略的约束。很多人建表时懒得写 NOT NULL结果后续所有涉及该字段的查询、聚合、程序判断都要考虑万一是 NULL。比如SELECT SUM(amount) FROM orders 时NULL 会被直接忽略可能让你以为没有数据WHERE status paid 永远不会包含 status 为 NULL 的记录JOIN 关联字段为 NULL 时关联条件不会匹配上。NUL 的问题不在于我没有把数据放进去而在于 NULL 会在计算、比较、索引各方面产生让人意外的行为。DEFAULT 约束则是给字段一个合理的默认值。建议这样配合status VARCHAR(20) NOT NULL DEFAULT pending, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPMySQL 8.0.13 往后DEFAULT 还支持表达式比如 DEFAULT (UUID()) 这种。但日常使用中最常见的还是时间默认值和状态默认值。这里有一个建议不要一边允许 NULL一边又在业务代码里写一堆如果字段为空就赋值默认值的逻辑。该用数据库默认约束的地方就交给数据库去处理而不是靠应用层补。2.4 外键约束能用但别滥用性能影响要说透外键约束用于维护两个表之间的引用完整性。比如 order 表的 user_id 引用 user 表的 id那么在插入一条 user_id 不存在的订单时外键会直接报错。外键约束本身不是万恶之源但它有几个比较麻烦的特点每次 INSERT/UPDATE/DELETE 都要检查父表或子表可能产生额外的锁对父表删除或更新时会根据 ON DELETE、ON UPDATE 规则去操作子表记录CASCADE、SET NULL 等这些操作往往是隐式完成的业务代码里看不到在分库分表、数据归档、高并发写入的场景下外键很容易成为瓶颈。我见过一个真实的案例某团队用了外键 ON DELETE CASCADE删一个父订单结果 MySQL 静默级联删除了几万条子记录。操作的人以为只删了一条等发现时数据已经没了。这种隐式操作在外键场景下非常危险尤其是大表。因此对于核心交易链路、需要严格引用完整性的单一数据库系统外键可以用。但如果你已经上了分库分表或者表数据量级很大建议把外键去掉在应用层做引用完整性校验再用定时任务/事件补偿去处理孤儿数据。2.5 CHECK 约束MySQL 8.0 才能用的“新”能力CHECK 约束用来限制字段取值范围。在 MySQL 8.0.16 之前CHECLK 子句虽然能写进建表语句里但 MySQL 会直接忽略它不报错也不生效。这是一个非常坑的历史遗留行为——很多老教程里写的 CHECK 都没有真正起过作用。从 8.0.16 开始MySQL 正式强制启用 CHECK 约束。用法很简单CREATE TABLE orders ( status VARCHAR(20) NOT NULL, CONSTRAINT chk_orders_status CHECK (status IN (pending, paid, cancelled)) );CHECK 约束还可以写成表级约束用来做跨字段校验CONSTRAINT chk_order_amount CHECK (amount 0)有了这个你就可以不用 ENUM 来限制取值了。ENUM 的最大问题是后续想加一个枚举值时ALTER TABLE 修改成本很高而且在 MySQL 8.0 前 ENUM 的值变更也会引发表重建。CHECK 约束直接修改条件就行灵活得多。不过要注意MySQL 的 CHECK 约束目前只支持行内当前字段的检查不能做跨行、跨表的子查询校验。跨表校验还是得靠触发器或者外键。3. 电商订单模块的约束设计实操从需求到建表语句3.1 订单表的字段梳理与约束需求与其空谈约束类型不如直接走一遍完整的建表过程。假设我们要设计一个简单的电商订单模块包含两张表users用户表和 orders订单表order_items订单明细表。我先把业务需求罗列出来orders.order_no 业务订单号必须全局唯一orders.user_id 下单用户必须存在且不能为空orders.status 订单状态只允许 pending / paid / cancelled / shipped 四种orders.total_amount 订单总金额不能为负数默认 0orders.created_at 创建时间默认当前时间order_items.order_id 必须引用 orders.idorder_items.quantity 购买数量必须大于 0order_items.price 单价不能为负数order_items 的同一个订单内不允许出现重复的 product_id逻辑上每个商品只买一条数量累加。基于这些需求下面手写建表语句。3.2 建表 SQL 完整实现假设 users 表已经存在这里只给核心订单两张表CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, order_no VARCHAR(64) NOT NULL COMMENT 业务订单号, user_id BIGINT UNSIGNED NOT NULL COMMENT 用户ID, status VARCHAR(20) NOT NULL DEFAULT pending COMMENT 订单状态, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单总金额, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_orders_order_no (order_no), KEY idx_orders_user_id (user_id), CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id) REFERENCES users (id), CONSTRAINT chk_orders_status CHECK (status IN (pending, paid, cancelled, shipped)), CONSTRAINT chk_orders_amount CHECK (total_amount 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表; CREATE TABLE order_items ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, order_id BIGINT UNSIGNED NOT NULL COMMENT 订单ID, product_id BIGINT UNSIGNED NOT NULL COMMENT 商品ID, quantity INT UNSIGNED NOT NULL COMMENT 购买数量, price DECIMAL(10,2) NOT NULL COMMENT 成交单价, PRIMARY KEY (id), UNIQUE KEY uk_order_items_product (order_id, product_id), CONSTRAINT fk_order_items_order_id FOREIGN KEY (order_id) REFERENCES orders (id), CONSTRAINT chk_order_items_quantity CHECK (quantity 0), CONSTRAINT chk_order_items_price CHECK (price 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单明细表;注意几个细节所有外键列都手动创建了索引。虽然 InnoDB 在外键不存在索引时也会自动建但手动命名和创建后续维护更可控唯一键 uk_order_items_product 使用了 (order_id, product_id) 联合唯一保证了同一个订单内商品不重复CHECK 约束都给了明确的名字方便后续 ALTER TABLE DROP CHECK金额字段没有用 FLOAT而是 DECIMAL避免浮点精度问题。3.3 如何调整已有约束ADD、DROP 和 MODIFY 的差异表已经建好了但如果以后业务变化需要调整约束该怎么办MySQL 的 ALTER TABLE 语句里有几种不同操作很多人容易搞混。新增约束-- 新增唯一约束 ALTER TABLE orders ADD CONSTRAINT uk_orders_order_no UNIQUE (order_no); -- 新增外键约束 ALTER TABLE order_items ADD CONSTRAINT fk_order_items_order_id FOREIGN KEY (order_id) REFERENCES orders(id); -- 新增检查约束 ALTER TABLE orders ADD CONSTRAINT chk_orders_status CHECK (status IN (pending, paid, cancelled, shipped));删除约束-- 删除主键 ALTER TABLE orders DROP PRIMARY KEY; -- 删除唯一约束底层是索引 ALTER TABLE orders DROP INDEX uk_orders_order_no; -- 删除外键约束 ALTER TABLE order_items DROP FOREIGN KEY fk_order_items_order_id; -- 删除检查约束 ALTER TABLE orders DROP CHECK chk_orders_status;注意一个容易踩的坑MySQL 里没有通用的 DROP CONSTRAINT 语法部分版本和部分约束类型除外。比如唯一约束你只能用 DROP INDEX 来删除因为唯一约束背后就是一个索引。外键约束则必须用 DROP FOREIGN KEY。如果你写 ALTER TABLE orders DROP CONSTRAINT uk_orders_order_no某些版本会直接报语法错误。修改字段的 NULL 属性则用 MODIFY COLUMNALTER TABLE orders MODIFY COLUMN order_no VARCHAR(64) NOT NULL;这个语句会把原来的列定义整个替换掉所以写 MODIFY 时要把字段的全部属性重新写全否则默认值、注释之类的配置容易丢。3.4 用系统表反向验证约束是否生效建完表和调整完约束后怎么确认约束真的生效了光看表结构会有点累有两个很实用的方法。第一种直接看建表语句SHOW CREATE TABLE orders\G输出里会完整展示当前表所有的约束、索引、存储引擎。如果约束被 MySQL 忽略了这里就能明显看出来少了什么。第二种查 data dictionary 里的约束信息SELECT table_name, constraint_name, constraint_type FROM information_schema.table_constraints WHERE table_schema your_db AND table_name orders;如果想看外键的关联关系可以查SELECT constraint_name, table_name, referenced_table_name FROM information_schema.referential_constraints WHERE constraint_schema your_db;这两个查询在排查为什么约束没生效外键建到哪去了时很好用。我一般建议在每次结构变更后用 SHOW CREATE TABLE 做一次人工检查用系统表做自动化巡检双保险。4. 约束引发的线上故障与排查思路4.1 外键导致的锁等待一条 DELETE 卡了半小时有一次线上系统突然出现大量 DELETE 慢查询卡了半小时最后把整个订单表锁住了。当时的表结构里 orders 和 order_items 有外键关系业务上做逻辑删除但也会物理删除一些中间态的脏订单。问题出在删除父表记录时InnoDB 需要在子表 order_items 的外键列上检查是否存在引用记录。如果外键列索引没有被利用起来或者子表数据量很大这个检查会变成全表扫描同时还会给子表加锁。并发上来以后锁冲突就越来越严重最终形成长时间的锁等待。排查时先用 SHOW PROCESSLIST 看哪些 SQL 是 Waiting for table metadata lock 或 Lock wait timeout再用 EXPLAIN 查看删除语句的执行计划。如果发现 type 是 ALL大概率是外键列的索引没用好。解决办法短期用事务先删除子表记录再删除父表或者改用逻辑删除长期建议在子表外键列上补索引并且在高并发场景下重新评估是否真的要保留物理外键。这个问题的本质不是外键规则错了而是外键在没有合适索引支持时的成本被严重低估。4.2 唯一约束冲突让批量导入中断另一件让我印象深刻的事是运营同学用 LOAD DATA 批量导入历史订单数据导到一半程序报错退出前十几万条全部回滚。原因就是表里有唯一约束 uk_orders_order_no导入文件里混进了几条重复的 order_no。很多人不知道 MySQL 的多行 INSERT 是一个语句InnoDB 下要么全部成功要么全部回滚。所以如果一个文件里有几条脏数据整个批处理都会失败。这不是约束的锅但你可以选择更合适的处理方式-- 忽略重复键保留第一条 INSERT IGNORE INTO orders (...) VALUES ...; -- 重复键时更新其他字段 INSERT INTO orders (...) VALUES ... ON DUPLICATE KEY UPDATE status VALUES(status);LOAD DATA 里也可以加 IGNORE 关键字LOAD DATA INFILE /tmp/orders.csv IGNORE INTO TABLE orders ...但要注意INSERT IGNORE 不仅会忽略唯一键冲突还会忽略其他很多数据错误比如字段超长、非空约束违规等。滥用 IGNORE 可能让真正的问题数据被默默丢掉。所以如果是比较关键的数据导入我建议先用脚本把数据做一遍预校验再决定要不要用 IGNORE。4.3 数据迁移时临时关闭外键检查却引发了脏数据做数据迁移时很多人会习惯性地先 SET FOREIGN_KEY_CHECKS 0 再导入数据导入完再恢复。这个方法本身没问题问题在于很多人关了外键检查以后就开始乱导入完全不管导入顺序和关联关系。有一回我参与一个库的数据拆分同事把 orders 和 order_items 的数据分别导出然后先导 orders 后导 order_items照理说没问题。但他在导数据时临时把外键检查关了有一批 order_items 对应的 order_id 在 orders 表里并不存在——可能是线下对账脚本生成的脏数据。外键检查开着的时候这批数据会被拦住但检查关了以后直接溜进了生产库。从这以后我的习惯是即使 SET FOREIGN_KEY_CHECKS 0也在导入完成后专门写校验 SQL 去查一遍孤儿数据SELECT COUNT(*) FROM order_items oi LEFT JOIN orders o ON oi.order_id o.id WHERE o.id IS NULL;这个查询一定要做。外键检查关闭只是救急手段不是让你跳过数据质量验证的免死金牌。4.4 约束名重复导致 DROP INDEX 报错还有一类常见问题是约束名/索引名重复导致的 DDL 失败。MySQL 里约束名和索引名在同一个命名空间里尤其唯一约束和唯一索引本质上是同一个东西。如果你先建了一个唯一索引后来再加唯一约束时又起了同样的名字或者名字之间因为大小写、下划线问题产生了冲突ALTER TABLE 就会报错。曾经有同事在多个环境同步脚本时由于不同环境下标的名字不一致导致 DROP INDEX 时找不到对应的索引名。看了半天才发现他以为唯一约束名是 uk_orders_order_no但实际上当时自动生成的约束名是 order_no_2因为某个历史版本中已经存在过一个同名的索引。所以在约束命名上我建议建立统一的规范主键直接用 PRIMARY唯一约束uk_表名_字段名外键约束fk_表名_父表名_字段名检查约束chk_表名_字段名。这样不仅代码可读性好也避免约束名冲突。在删除约束前先用 SHOW CREATE TABLE 或 information_schema 查一下真实的约束名再执行 DROP不要靠猜。5. 关于约束的选择开发规范、面试题和我的个人体会5.1 什么时候该放弃外键分库分表、高并发写入场景外键在理论上很优雅但在工程里经常被放弃。我参与过的项目中凡是上了分库分表或者读写分离比较彻底的系统基本都不会在核心链路表上建物理外键。原因很直接分库后两张表可能不在同一个数据库实例上外键根本无法跨实例支持即使是单库外键的级联操作和锁机制在高并发写入下也会成为瓶颈数据归档时经常要拆表、整表迁移外键会变成沉重的负担。但这不代表外键应该被一棒子打死。对于内部管理系统、低频写入的管理后台、统计报表库外键可以明显减少脏数据维护成本几乎可以忽略。我的判断标准很简单如果业务写并发不高且数据强一致性要求高外键可以用如果写并发高、数据量增长快、架构迟早要分片那就提前考虑去掉。5.2 约束、应用校验与触发器的分工很多刚入行的同学会问数据库约束、应用代码校验、触发器三者的功能似乎有重叠到底什么时候用哪个我的经验是数据库约束永远是数据正确性的最后一道防线应用校验是用户友好型的第一道防线触发器则是最后一张底牌。应用校验能给出及时的、友好的错误提示但应用代码会漏、会改错、会被绕过不能依赖它来保证数据质量数据库约束不管你从哪个入口写数据它都能强制拦截所以该建的唯一、非空、检查、外键约束一定要建触发器适合做复杂的审计日志、跨表汇总等逻辑但触发器隐含在 DML 操作后面排查问题困难性能影响也比较大。不要用触发器去替代简单约束。5.3 面试时我会怎么考察候选人的约束功底我面试后端开发时如果聊到 MySQL一定会问几个跟约束沾边的问题唯一索引里的 NULL 可以重复吗如果你没踩过 NULL 的坑很难答出允许重复这个点。MySQL 5.7 建表时写了 CHECK 约束有用吗这个问题能筛掉很多只背建表语法的人。外键为什么不建议随便用能讲出锁、性能、级联风险的人才算真正写过线上系统。给你一个订单表需求你会在哪些字段上设计什么约束这个开放题能看出一个人对业务和表的理解深度。这些问题的价值不在于答案本身而在于候选人有没有真的在真实表结构里吃过亏。约束这个东西靠背诵记不住只有踩过坑才知道在每个场景下该怎么选。5.4 几点建议最后分享几条我在实际项目里总结出来的小习惯一是我现在写每一张业务表都会先问自己几个问题这表的主键是什么哪些字段绝对不允许为空哪些业务标识符要全局唯一金额和数量的取值范围是什么有没有跨表依赖关系想清楚这些问题之后再写 DDL约束基本都是顺手带出来的。二是不要把约束名省掉。MySQL 默认生成的约束名往往没有业务含义后续想删除、调整、排查的时候非常痛苦。显式命名花不了十秒钟但能省掉后面至少半小时的查表时间。三是每次做结构变更都要同步确认约束是否还符合当前业务规则。业务变化比代码变化更快表结构里的约束也需要跟着演进。旧约束没删新约束没加往往是数据质量问题的高发期。四是对待约束的粒度要合适。老话说过犹不及建一堆没意义的 CHECK 和唯一约束只会让写入变慢、维护变烦。只约束真正需要保证的数据规则把设计约束当成和数据模型同等重要的事来做。
MySQL约束详解：从建表设计到故障排查的完整实践指南
两年前我接手了一套经营一年多的订单系统做代码 review 的时候顺手打开生产订单表查出三条 order_no 完全一样的记录还有一批 order_status 是 NULL 的单子user_id 也能对上不存在的用户。当时我就在想这套系统能跑到现在还没炸只能说是运气好。后来逐张表核对 DDL才发现问题不是业务代码写得烂而是建表时压根没把 MySQL 表的约束当回事——该唯一的不唯一该非空的字段全允许 NULL外键一个都没建。从那以后我对表约束这件事变得特别敏感。这篇文章不是给你背一遍《MySQL 官方文档》里的约束定义而是想把约束从建表、使用、踩坑到面试这条线完整串起来。不管你是刚学 MySQL 的学生还是写了两三年业务代码的开发又或者正在做数据库设计评审的 DBA都能从中看到一点自己踩过或即将踩的坑。约束不是 DDL 里的装饰品它决定了一张表的数据底线也决定了你后续写业务 SQL 时要花多少精力去兜底脏数据。1. 约束不是摆设它决定了一张表的数据底线1.1 我之前接手的那张“什么都能存”的表先说那张让我印象深刻的订单表。它的结构大概是这样的CREATE TABLE orders ( id BIGINT AUTO_INCREMENT, order_no VARCHAR(64), user_id BIGINT, amount DECIMAL(10,2), status VARCHAR(20), created_at DATETIME ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;猛一看好像也没什么字段名都能看懂。但问题全在细节里order_no 没有唯一约束所以业务上同一个订单号可以被插入多次status 没有 NOT NULL 和 CHECK所以业务代码一旦写错状态值NULL 或者任意字符串都能进表user_id 没有外键也不一定有对应真实用户amount 没有默认值也没有检查负数和 NULL 都能进来。你以为业务代码里做了校验确实做了但总有漏的情况。更麻烦的是这套系统在很长一段时间里根本没有统一的写入入口有的是后台任务在写有的是运营脚本在刷数据还有的定时任务在补单。任何一个入口没写好脏数据就进去了。等数据乱了以后你再回头想靠 SQL 去清洗既费劲又容易误伤。这就是约束的价值它是在数据库层面强制规定这张表能接受什么样的数据不依赖任何代码入口。业务校验是大部分时候有效约束是只要走 SQL 就一定拦得住。1.2 约束、索引与存储引擎的三角关系学约束之前很多人会忽略约束和索引、存储引擎之间的关系。实际上 MySQL 里不少约束并不是独立存在的它们底层依赖索引。主键约束PRIMARY KEY会自动创建一个唯一索引而且是 InnoDB 的聚簇索引唯一约束UNIQUE会自动创建一个唯一索引外键约束FOREIGN KEY要求参考列上必须有索引如果这个索引不存在InnoDB 会自动为外键列创建一个索引。所以你会发现你在建表时写了一个 UNIQUE 约束SHOW INDEX FROM 表名 里就会多出一个索引。约束是逻辑规则索引是实现这个规则的物理手段。两者相辅相成也常常共享同一个名字。再说存储引擎外键约束只有 InnoDB 真正支持。如果你用 MyISAM就算建表语句里写了 FOREIGN KEYMySQL 也会高抬贵手地忽略掉不会报错但外键完全不会生效。这在日常开发中是个容易踩的坑——很多人以为表建出来了约束就有了其实不一定。1.3 每一次 INSERT 的约束检查要付出多少成本约束不是免费的。每插入一条记录InnoDB 都要做一堆检查NOT NULL逐字段判断是否有 NULL 值主键 / 唯一约束通过索引检查当前插入的键值是否已经存在外键约束检查父表中的参考键是否存在并且根据隔离级别决定加什么锁CHECK 约束逐行计算检查条件。所以约束确实会带来性能开销。尤其在批量写入的场景下唯一约束和外键约束的检查会显著拉低写入吞吐。这也是很多高并发团队选择牺牲约束换性能的原因之一。但我想说一个观点对于绝大多数业务系统约束带来的性能损耗完全可以接受。少一次线上数据修复比多写几千条 SQL 的吞吐要有价值得多。你不能既想要强一致性又不想付任何代价。约束的检查成本本质上是为数据质量买的保险。2. 六类约束逐个拆解用法、语义和最容易踩的坑2.1 主键约束自增、UUID 还是业务字段判断标准不是唯一性主键约束是唯一性最强的约束不能为 NULL不能重复一张表最多只能有一个主键。InnoDB 里主键对应的索引就是聚簇索引所以主键的选择会直接影响表的数据组织方式。工作中最常见的三种主键方案是自增 BIGINT、UUID或类 UUID 字符串、业务自然主键。自增主键写入顺序和主键大小单调递增新数据插入时基本是在索引末尾追加页分裂少性能最好。但在分库分表后自增容易冲突需要用号段或者雪花算法。UUID 主键最大的好处是全局唯一分布式环境下不用考虑冲突。但 UUID 字符串长度长随机性强插入时 B 树要频繁做中间插入容易造成页分裂写性能明显差于自增。业务自然主键比如用身份证号、手机号之类的字段做主键省掉一个额外的主键字段。但这样的方案风险很大业务字段一旦变化主键也要跟着改代价非常大。我的经验是能加代理主键就加代理主键别让业务字段承担主键职责。主键应该是一个永恒不变的标识而不是一个可能被业务规则影响的属性。2.2 唯一约束NULL 带来的双重含义唯一约束UNIQUE表示列中的所有非 NULL 值必须唯一。这里的关键点在于MySQL 的 InnoDB 允许唯一约束的列存在多个 NULL 值。为什么因为 SQL 标准里NULL 表示未知两个 NULL 并不相等。所以唯一约束不拦截 NULL除非你用 NULLS NOT DISTINCTMySQL 8.0.16 起支持要求 NULL 也互相不重复。这在业务上会带来一个很典型的陷阱比如你要在 user 表上给 nickname 加唯一约束想实现昵称不能重复。但如果你允许 nickname 为 NULL那么用户不填昵称时所有未填昵称的用户都会被存成 NULL而且都能插入成功——因为多个 NULL 不算重复。结果你想要的唯一性只在用户填了昵称时才部分生效。所以如果你要的是这一列绝对不能有相同值那要先考虑是否应该把这一列设为 NOT NULL。否则唯一约束只约束了非 NULL 部分。另外也要小心空字符串和 NULL 的区别。空字符串 是一个确定的值唯一约束会拦截第二条 但不会拦截 NULL。这个差异经常导致线上莫名其妙的重复数据问题。2.3 非空与默认约束数据质量的隐形守门员NOT NULL 和 DEFAULT 是两种最容易写也最容易被忽略的约束。很多人建表时懒得写 NOT NULL结果后续所有涉及该字段的查询、聚合、程序判断都要考虑万一是 NULL。比如SELECT SUM(amount) FROM orders 时NULL 会被直接忽略可能让你以为没有数据WHERE status paid 永远不会包含 status 为 NULL 的记录JOIN 关联字段为 NULL 时关联条件不会匹配上。NUL 的问题不在于我没有把数据放进去而在于 NULL 会在计算、比较、索引各方面产生让人意外的行为。DEFAULT 约束则是给字段一个合理的默认值。建议这样配合status VARCHAR(20) NOT NULL DEFAULT pending, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPMySQL 8.0.13 往后DEFAULT 还支持表达式比如 DEFAULT (UUID()) 这种。但日常使用中最常见的还是时间默认值和状态默认值。这里有一个建议不要一边允许 NULL一边又在业务代码里写一堆如果字段为空就赋值默认值的逻辑。该用数据库默认约束的地方就交给数据库去处理而不是靠应用层补。2.4 外键约束能用但别滥用性能影响要说透外键约束用于维护两个表之间的引用完整性。比如 order 表的 user_id 引用 user 表的 id那么在插入一条 user_id 不存在的订单时外键会直接报错。外键约束本身不是万恶之源但它有几个比较麻烦的特点每次 INSERT/UPDATE/DELETE 都要检查父表或子表可能产生额外的锁对父表删除或更新时会根据 ON DELETE、ON UPDATE 规则去操作子表记录CASCADE、SET NULL 等这些操作往往是隐式完成的业务代码里看不到在分库分表、数据归档、高并发写入的场景下外键很容易成为瓶颈。我见过一个真实的案例某团队用了外键 ON DELETE CASCADE删一个父订单结果 MySQL 静默级联删除了几万条子记录。操作的人以为只删了一条等发现时数据已经没了。这种隐式操作在外键场景下非常危险尤其是大表。因此对于核心交易链路、需要严格引用完整性的单一数据库系统外键可以用。但如果你已经上了分库分表或者表数据量级很大建议把外键去掉在应用层做引用完整性校验再用定时任务/事件补偿去处理孤儿数据。2.5 CHECK 约束MySQL 8.0 才能用的“新”能力CHECK 约束用来限制字段取值范围。在 MySQL 8.0.16 之前CHECLK 子句虽然能写进建表语句里但 MySQL 会直接忽略它不报错也不生效。这是一个非常坑的历史遗留行为——很多老教程里写的 CHECK 都没有真正起过作用。从 8.0.16 开始MySQL 正式强制启用 CHECK 约束。用法很简单CREATE TABLE orders ( status VARCHAR(20) NOT NULL, CONSTRAINT chk_orders_status CHECK (status IN (pending, paid, cancelled)) );CHECK 约束还可以写成表级约束用来做跨字段校验CONSTRAINT chk_order_amount CHECK (amount 0)有了这个你就可以不用 ENUM 来限制取值了。ENUM 的最大问题是后续想加一个枚举值时ALTER TABLE 修改成本很高而且在 MySQL 8.0 前 ENUM 的值变更也会引发表重建。CHECK 约束直接修改条件就行灵活得多。不过要注意MySQL 的 CHECK 约束目前只支持行内当前字段的检查不能做跨行、跨表的子查询校验。跨表校验还是得靠触发器或者外键。3. 电商订单模块的约束设计实操从需求到建表语句3.1 订单表的字段梳理与约束需求与其空谈约束类型不如直接走一遍完整的建表过程。假设我们要设计一个简单的电商订单模块包含两张表users用户表和 orders订单表order_items订单明细表。我先把业务需求罗列出来orders.order_no 业务订单号必须全局唯一orders.user_id 下单用户必须存在且不能为空orders.status 订单状态只允许 pending / paid / cancelled / shipped 四种orders.total_amount 订单总金额不能为负数默认 0orders.created_at 创建时间默认当前时间order_items.order_id 必须引用 orders.idorder_items.quantity 购买数量必须大于 0order_items.price 单价不能为负数order_items 的同一个订单内不允许出现重复的 product_id逻辑上每个商品只买一条数量累加。基于这些需求下面手写建表语句。3.2 建表 SQL 完整实现假设 users 表已经存在这里只给核心订单两张表CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, order_no VARCHAR(64) NOT NULL COMMENT 业务订单号, user_id BIGINT UNSIGNED NOT NULL COMMENT 用户ID, status VARCHAR(20) NOT NULL DEFAULT pending COMMENT 订单状态, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单总金额, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_orders_order_no (order_no), KEY idx_orders_user_id (user_id), CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id) REFERENCES users (id), CONSTRAINT chk_orders_status CHECK (status IN (pending, paid, cancelled, shipped)), CONSTRAINT chk_orders_amount CHECK (total_amount 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表; CREATE TABLE order_items ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, order_id BIGINT UNSIGNED NOT NULL COMMENT 订单ID, product_id BIGINT UNSIGNED NOT NULL COMMENT 商品ID, quantity INT UNSIGNED NOT NULL COMMENT 购买数量, price DECIMAL(10,2) NOT NULL COMMENT 成交单价, PRIMARY KEY (id), UNIQUE KEY uk_order_items_product (order_id, product_id), CONSTRAINT fk_order_items_order_id FOREIGN KEY (order_id) REFERENCES orders (id), CONSTRAINT chk_order_items_quantity CHECK (quantity 0), CONSTRAINT chk_order_items_price CHECK (price 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单明细表;注意几个细节所有外键列都手动创建了索引。虽然 InnoDB 在外键不存在索引时也会自动建但手动命名和创建后续维护更可控唯一键 uk_order_items_product 使用了 (order_id, product_id) 联合唯一保证了同一个订单内商品不重复CHECK 约束都给了明确的名字方便后续 ALTER TABLE DROP CHECK金额字段没有用 FLOAT而是 DECIMAL避免浮点精度问题。3.3 如何调整已有约束ADD、DROP 和 MODIFY 的差异表已经建好了但如果以后业务变化需要调整约束该怎么办MySQL 的 ALTER TABLE 语句里有几种不同操作很多人容易搞混。新增约束-- 新增唯一约束 ALTER TABLE orders ADD CONSTRAINT uk_orders_order_no UNIQUE (order_no); -- 新增外键约束 ALTER TABLE order_items ADD CONSTRAINT fk_order_items_order_id FOREIGN KEY (order_id) REFERENCES orders(id); -- 新增检查约束 ALTER TABLE orders ADD CONSTRAINT chk_orders_status CHECK (status IN (pending, paid, cancelled, shipped));删除约束-- 删除主键 ALTER TABLE orders DROP PRIMARY KEY; -- 删除唯一约束底层是索引 ALTER TABLE orders DROP INDEX uk_orders_order_no; -- 删除外键约束 ALTER TABLE order_items DROP FOREIGN KEY fk_order_items_order_id; -- 删除检查约束 ALTER TABLE orders DROP CHECK chk_orders_status;注意一个容易踩的坑MySQL 里没有通用的 DROP CONSTRAINT 语法部分版本和部分约束类型除外。比如唯一约束你只能用 DROP INDEX 来删除因为唯一约束背后就是一个索引。外键约束则必须用 DROP FOREIGN KEY。如果你写 ALTER TABLE orders DROP CONSTRAINT uk_orders_order_no某些版本会直接报语法错误。修改字段的 NULL 属性则用 MODIFY COLUMNALTER TABLE orders MODIFY COLUMN order_no VARCHAR(64) NOT NULL;这个语句会把原来的列定义整个替换掉所以写 MODIFY 时要把字段的全部属性重新写全否则默认值、注释之类的配置容易丢。3.4 用系统表反向验证约束是否生效建完表和调整完约束后怎么确认约束真的生效了光看表结构会有点累有两个很实用的方法。第一种直接看建表语句SHOW CREATE TABLE orders\G输出里会完整展示当前表所有的约束、索引、存储引擎。如果约束被 MySQL 忽略了这里就能明显看出来少了什么。第二种查 data dictionary 里的约束信息SELECT table_name, constraint_name, constraint_type FROM information_schema.table_constraints WHERE table_schema your_db AND table_name orders;如果想看外键的关联关系可以查SELECT constraint_name, table_name, referenced_table_name FROM information_schema.referential_constraints WHERE constraint_schema your_db;这两个查询在排查为什么约束没生效外键建到哪去了时很好用。我一般建议在每次结构变更后用 SHOW CREATE TABLE 做一次人工检查用系统表做自动化巡检双保险。4. 约束引发的线上故障与排查思路4.1 外键导致的锁等待一条 DELETE 卡了半小时有一次线上系统突然出现大量 DELETE 慢查询卡了半小时最后把整个订单表锁住了。当时的表结构里 orders 和 order_items 有外键关系业务上做逻辑删除但也会物理删除一些中间态的脏订单。问题出在删除父表记录时InnoDB 需要在子表 order_items 的外键列上检查是否存在引用记录。如果外键列索引没有被利用起来或者子表数据量很大这个检查会变成全表扫描同时还会给子表加锁。并发上来以后锁冲突就越来越严重最终形成长时间的锁等待。排查时先用 SHOW PROCESSLIST 看哪些 SQL 是 Waiting for table metadata lock 或 Lock wait timeout再用 EXPLAIN 查看删除语句的执行计划。如果发现 type 是 ALL大概率是外键列的索引没用好。解决办法短期用事务先删除子表记录再删除父表或者改用逻辑删除长期建议在子表外键列上补索引并且在高并发场景下重新评估是否真的要保留物理外键。这个问题的本质不是外键规则错了而是外键在没有合适索引支持时的成本被严重低估。4.2 唯一约束冲突让批量导入中断另一件让我印象深刻的事是运营同学用 LOAD DATA 批量导入历史订单数据导到一半程序报错退出前十几万条全部回滚。原因就是表里有唯一约束 uk_orders_order_no导入文件里混进了几条重复的 order_no。很多人不知道 MySQL 的多行 INSERT 是一个语句InnoDB 下要么全部成功要么全部回滚。所以如果一个文件里有几条脏数据整个批处理都会失败。这不是约束的锅但你可以选择更合适的处理方式-- 忽略重复键保留第一条 INSERT IGNORE INTO orders (...) VALUES ...; -- 重复键时更新其他字段 INSERT INTO orders (...) VALUES ... ON DUPLICATE KEY UPDATE status VALUES(status);LOAD DATA 里也可以加 IGNORE 关键字LOAD DATA INFILE /tmp/orders.csv IGNORE INTO TABLE orders ...但要注意INSERT IGNORE 不仅会忽略唯一键冲突还会忽略其他很多数据错误比如字段超长、非空约束违规等。滥用 IGNORE 可能让真正的问题数据被默默丢掉。所以如果是比较关键的数据导入我建议先用脚本把数据做一遍预校验再决定要不要用 IGNORE。4.3 数据迁移时临时关闭外键检查却引发了脏数据做数据迁移时很多人会习惯性地先 SET FOREIGN_KEY_CHECKS 0 再导入数据导入完再恢复。这个方法本身没问题问题在于很多人关了外键检查以后就开始乱导入完全不管导入顺序和关联关系。有一回我参与一个库的数据拆分同事把 orders 和 order_items 的数据分别导出然后先导 orders 后导 order_items照理说没问题。但他在导数据时临时把外键检查关了有一批 order_items 对应的 order_id 在 orders 表里并不存在——可能是线下对账脚本生成的脏数据。外键检查开着的时候这批数据会被拦住但检查关了以后直接溜进了生产库。从这以后我的习惯是即使 SET FOREIGN_KEY_CHECKS 0也在导入完成后专门写校验 SQL 去查一遍孤儿数据SELECT COUNT(*) FROM order_items oi LEFT JOIN orders o ON oi.order_id o.id WHERE o.id IS NULL;这个查询一定要做。外键检查关闭只是救急手段不是让你跳过数据质量验证的免死金牌。4.4 约束名重复导致 DROP INDEX 报错还有一类常见问题是约束名/索引名重复导致的 DDL 失败。MySQL 里约束名和索引名在同一个命名空间里尤其唯一约束和唯一索引本质上是同一个东西。如果你先建了一个唯一索引后来再加唯一约束时又起了同样的名字或者名字之间因为大小写、下划线问题产生了冲突ALTER TABLE 就会报错。曾经有同事在多个环境同步脚本时由于不同环境下标的名字不一致导致 DROP INDEX 时找不到对应的索引名。看了半天才发现他以为唯一约束名是 uk_orders_order_no但实际上当时自动生成的约束名是 order_no_2因为某个历史版本中已经存在过一个同名的索引。所以在约束命名上我建议建立统一的规范主键直接用 PRIMARY唯一约束uk_表名_字段名外键约束fk_表名_父表名_字段名检查约束chk_表名_字段名。这样不仅代码可读性好也避免约束名冲突。在删除约束前先用 SHOW CREATE TABLE 或 information_schema 查一下真实的约束名再执行 DROP不要靠猜。5. 关于约束的选择开发规范、面试题和我的个人体会5.1 什么时候该放弃外键分库分表、高并发写入场景外键在理论上很优雅但在工程里经常被放弃。我参与过的项目中凡是上了分库分表或者读写分离比较彻底的系统基本都不会在核心链路表上建物理外键。原因很直接分库后两张表可能不在同一个数据库实例上外键根本无法跨实例支持即使是单库外键的级联操作和锁机制在高并发写入下也会成为瓶颈数据归档时经常要拆表、整表迁移外键会变成沉重的负担。但这不代表外键应该被一棒子打死。对于内部管理系统、低频写入的管理后台、统计报表库外键可以明显减少脏数据维护成本几乎可以忽略。我的判断标准很简单如果业务写并发不高且数据强一致性要求高外键可以用如果写并发高、数据量增长快、架构迟早要分片那就提前考虑去掉。5.2 约束、应用校验与触发器的分工很多刚入行的同学会问数据库约束、应用代码校验、触发器三者的功能似乎有重叠到底什么时候用哪个我的经验是数据库约束永远是数据正确性的最后一道防线应用校验是用户友好型的第一道防线触发器则是最后一张底牌。应用校验能给出及时的、友好的错误提示但应用代码会漏、会改错、会被绕过不能依赖它来保证数据质量数据库约束不管你从哪个入口写数据它都能强制拦截所以该建的唯一、非空、检查、外键约束一定要建触发器适合做复杂的审计日志、跨表汇总等逻辑但触发器隐含在 DML 操作后面排查问题困难性能影响也比较大。不要用触发器去替代简单约束。5.3 面试时我会怎么考察候选人的约束功底我面试后端开发时如果聊到 MySQL一定会问几个跟约束沾边的问题唯一索引里的 NULL 可以重复吗如果你没踩过 NULL 的坑很难答出允许重复这个点。MySQL 5.7 建表时写了 CHECK 约束有用吗这个问题能筛掉很多只背建表语法的人。外键为什么不建议随便用能讲出锁、性能、级联风险的人才算真正写过线上系统。给你一个订单表需求你会在哪些字段上设计什么约束这个开放题能看出一个人对业务和表的理解深度。这些问题的价值不在于答案本身而在于候选人有没有真的在真实表结构里吃过亏。约束这个东西靠背诵记不住只有踩过坑才知道在每个场景下该怎么选。5.4 几点建议最后分享几条我在实际项目里总结出来的小习惯一是我现在写每一张业务表都会先问自己几个问题这表的主键是什么哪些字段绝对不允许为空哪些业务标识符要全局唯一金额和数量的取值范围是什么有没有跨表依赖关系想清楚这些问题之后再写 DDL约束基本都是顺手带出来的。二是不要把约束名省掉。MySQL 默认生成的约束名往往没有业务含义后续想删除、调整、排查的时候非常痛苦。显式命名花不了十秒钟但能省掉后面至少半小时的查表时间。三是每次做结构变更都要同步确认约束是否还符合当前业务规则。业务变化比代码变化更快表结构里的约束也需要跟着演进。旧约束没删新约束没加往往是数据质量问题的高发期。四是对待约束的粒度要合适。老话说过犹不及建一堆没意义的 CHECK 和唯一约束只会让写入变慢、维护变烦。只约束真正需要保证的数据规则把设计约束当成和数据模型同等重要的事来做。