约束Constraint这个概念很多刚接触 MySQL 的开发者都不太当回事儿。建表的时候不写约束数据想怎么塞就怎么塞等到线上数据脏了、报表对不上了、订单金额变成负数了、用户重复注册了才开始回头补课。我自己最早也是这么过来的做某电商系统的订单表那会儿连外键都没建后来对账发现一堆孤儿订单一查全是当年偷懒埋的雷。所以这篇东西我想把 MySQL 约束这件事从头到尾讲透从六大约束类型到建表实操再到线上改表踩过的坑一次说清楚。不管你是刚入门的学生还是写了几年 SQL 想系统梳理一下的开发者这篇都适合你跟着过一遍。1. 约束的整体设计思路先想清楚数据的“底线”1.1 约束到底是什么约束说白了就是数据库层面的数据校验规则是 MySQL 强制要求写入的数据必须满足的条件。很多人在应用层做了校验就觉得万事大吉但应用层代码是人写的会改、会漏、会出 bug数据库约束才是最后一道谁也没法绕过的防线。我习惯把约束比作交通规则——车可以随便开但红灯必须停、实线不能变道违反规则的直接处罚这样路上才不会乱套。MySQL 的约束体系围绕四种完整性展开实体完整性保证每行数据能被唯一区分对应主键约束、唯一约束域完整性保证字段的取值在合法范围内对应非空约束、检查约束、默认值约束引用完整性保证表与表之间的关联关系不失效对应外键约束用户自定义完整性业务上额外的规则通常用检查约束或触发器实现。理解这四类完整性比死记硬背约束语法有用得多。因为你在设计表结构时本质上就是在回答四个问题这行数据怎么区分这个字段能填什么这张表和别的表什么关系业务上还有什么特殊要求1.2 为什么建表时必须先想清楚约束约束设计最理想的时间点永远是建表的那一刻。原因很简单约束一旦要回头补代价是成倍增长的。在一张几千万行的大表上执行 ALTER TABLE 加约束MySQL 可能要重建整张表期间产生的锁和 IO 压力能把线上业务拖垮。我实测过一张 8000 万行的订单表往上面加一个唯一约束跑了将近 20 分钟期间该表的写操作全部被阻塞大促期间这种操作想都不敢想。另一个容易被忽略的点是约束本身就是在给后续的开发者写文档。一个字段是 NOT NULL 还是允许 NULL直接反映了业务上这个属性是不是必填的外键关系直接告诉后来的人这两张表的关联逻辑。有经验的 DBA 拿到一份建表脚本不看业务文档光看约束就能猜出七八分业务逻辑。所以约束设计得清楚后面接手的同事能少踩一半坑。1.3 约束的分类列级约束与表级约束约束按定义位置可以分成列级约束和表级约束。列级约束直接写在字段定义后面只作用于这一个字段表级约束独立写在所有字段定义完之后可以作用于多个字段的组合。一个很典型的例子是联合唯一约束。比如订单明细表里同一个订单号下不允许出现重复的商品 ID这个约束必须同时作用在 order_id 和 product_id 两个字段上列级约束就做不到只能用表级约束写成 UNIQUE KEYuk_order_product(order_id,product_id)。从 MySQL 实际存储的角度看约束最终都会落到索引、外键元数据或者字段属性上。主键和唯一约束会创建对应的索引外键会自动在从表上建索引理解这一点对后面排查性能问题很有帮助。2. 六大约束类型逐个拆解2.1 NOT NULL最容易被忽略的底线NOT NULL 约束要求字段不允许存储 NULL 值。这个约束看起来简单实际用起来坑不少。最大的坑是很多人分不清 NULL 和空字符串的区别。在 MySQL 里NULL 是“没有值”空字符串是“一个长度为 0 的值”两者完全不同。比如手机号字段如果允许 NULL那么一条记录手机号是 NULL另一条是空字符串业务上可能都表示“没填手机号”但查询时WHERE phone 查不到 NULL 的记录WHERE phone IS NULL又查不到空字符串的记录一对账就是一堆脏数据。我的建议是业务上明确必填的字段一律 NOT NULL如果不确定要不要填宁可给 DEFAULT 值也不要轻易放开 NULL。很多公司规范里干脆约定“所有字段一律 NOT NULL用默认值兜底”这虽然激进但确实能省掉无数跟 NULL 纠缠的麻烦。NULL 还会影响索引效率因为有 NULL 的列在索引中需要额外标记查询优化器处理起来也更费劲。2.2 UNIQUE唯一性背后的索引代价UNIQUE 约束保证字段或字段组合的值不重复它在实现上会自动创建唯一索引。这里要特别注意唯一约束对 NULL 是网开一面的一个字段上可以有多个 NULL 值因为 MySQL 认为“NULL 不等于任何值包括它自己”。所以如果你用 UNIQUE 约束一个允许 NULL 的字段并不能真正阻止“重复的未填值”出现。标准做法是把这个字段改成 NOT NULL或者用一个生成列来兜底这个后面在问题排查章节我会详细展开。联合唯一约束是最常用的形式比如用户标签表里 (user_id, tag_id) 联合唯一从根本上杜绝了重复打标。这里有个设计上的取舍联合唯一约束建好之后查询时如果只按联合索引的第一字段查是可以走索引的但如果跳过了第一个字段直接查第二个字段索引大概率用不上。所以联合唯一约束的字段顺序要尽量跟业务查询的频率对齐高频过滤字段放前面。2.3 PRIMARY KEY主键选择的艺术主键约束是唯一约束的加强版一个表只能有一个主键它同时具备 NOT NULL 和 UNIQUE 的特点。主键在 InnoDB 里还有特殊地位它是聚簇索引的入口表数据最终是按照主键顺序物理存储的。主键选型上我吃过不少亏分享几个结论。自增整数主键是最省心的选择写入顺序跟主键顺序一致页分裂最少性能最稳定。业务主键比如用订单号字符串做主键虽然业务上直观但字符串随机性大插入时会频繁触发页分裂而且聚簇索引占用的空间也大。UUID 主键更坑随机插入对 InnoDB 很不友好如果非要用 UUID建议改成有序的版本或者用雪花算法生成的整数 ID。主键还有一个容易犯的错误用可变的业务字段做主键。比如用身份证号做主键一旦业务规则调整比如允许修改身份证号主键修改会牵连所有引用它的表和索引线上操作基本是灾难。所以主键尽量选跟业务无关、不会变化的代理键业务字段的唯一性用 UNIQUE 约束去保证两者职责分开。2.4 FOREIGN KEY保证引用完整性的双刃剑外键约束用来保证两张表之间的引用完整性。比如订单表的 user_id 引用用户表的 id有外键在就不可能出现“订单指向一个不存在的用户”这种孤儿数据。外键定义时可以指定引用动作这是最容易让人迷惑的地方。常见的动作有CASCADE父表删除或更新时子表跟着删除或更新SET NULL父表删除或更新时子表对应字段设为 NULL子表字段必须允许 NULLRESTRICT / NO ACTION父表有子表引用时禁止删除或更新。我见过很多团队一刀切禁用外键理由是性能差、影响并发。但实际上外键带来的额外开销主要是每次插入、更新时要检查父表这个代价在绝大多数业务场景下是可以接受的。真正的问题在于如果你在应用层做级联删除代码要处理所有分支漏掉一个就是脏数据外键至少给你兜住了底线。我的建议是核心业务、数据一致性要求高的场景该用外键就用分库分表之后外键没法用再退而求其次靠应用层保证。另外记住外键约束要求两张表都是 InnoDB 引擎字段类型必须一致被引用的字段必须是有索引的通常就是主键。2.5 CHECK8.0 才真正“复活”的检查约束CHECK 约束用来限制字段的取值范围比如年龄必须大于 0、性别只能取 M 或 F。MySQL 5.7 及之前版本对 CHECK 约束是“假装支持”的语法能过但实际不生效任何违反 CHECK 的值都能写入这个坑害了不少从文档学 MySQL 的新手。MySQL 8.0.16 开始CHECK 约束真正被强制执行了。写法上支持两种形式列级 CHECK 直接写在字段后面表级 CHECK 写在字段列表之后还支持给约束命名方便管理。比如CREATE TABLE employee ( id INT PRIMARY KEY, age INT, salary DECIMAL(10,2), CHECK (age 18 AND age 60), CONSTRAINT chk_salary_positive CHECK (salary 0) );CHECK 约束的设计要克制别把复杂业务规则全塞进约束里。比如“非 VIP 用户的折扣不能大于 0.9”这种跨字段、跨状态的规则写进 CHECK 里一方面可读性差另一方面以后改规则要重建表结构很折腾。这种场景用触发器或者应用层逻辑更合适。CHECK 最适合的是那些稳定不变的边界规则比如金额不能为负、状态只能取枚举集合里的几个值。2.6 DEFAULT给字段一个“保底方案”DEFAULT 约束在字段没有显式赋值时自动写入一个默认值。它跟 NOT NULL 搭配使用非常舒服既保证了字段不为空又让业务代码不用每次插入都操心这个字段。MySQL 8.0 的 DEFAULT 支持表达式这是个非常实用的特性。比如create_time DATETIME DEFAULT CURRENT_TIMESTAMP插入时自动填当前时间update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP每次记录更新时自动刷新时间。有了这个应用层完全不用维护时间字段了。用 DEFAULT 有几个要注意的细节BLOB、TEXT、GEOMETRY 这些类型不能直接给默认值JSON 类型的默认值在 8.0.13 之前也不支持。另外如果给一个字段设置了 DEFAULT插入时显式写入 NULL那默认值不会生效写入的仍然是 NULL。只有在插入语句里“完全没写这个字段”或者“显式写成 DEFAULT”时默认值才会起作用。3. 实操过程与核心环节实现3.1 一个完整的建表案例理论讲再多不如直接上一个能跑的建表脚本。我拿一个最经典的“用户-订单”场景来演示这是我在某模拟电商项目里实际用过的简化版结构。CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户ID, username VARCHAR(50) NOT NULL COMMENT 用户名, email VARCHAR(100) NOT NULL COMMENT 邮箱, phone VARCHAR(20) DEFAULT NULL COMMENT 手机号允许为空, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1-正常 0-禁用, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_username (username), UNIQUE KEY uk_email (email), CONSTRAINT chk_status CHECK (status IN (0, 1)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;CREATE TABLE order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 订单ID, user_id INT UNSIGNED NOT NULL COMMENT 下单用户ID, order_no VARCHAR(32) NOT NULL COMMENT 订单编号, amount DECIMAL(10,2) NOT NULL COMMENT 订单金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态0-待支付 1-已支付 2-已发货 3-已完成 4-已取消, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT chk_order_amount CHECK (amount 0), CONSTRAINT chk_order_status CHECK (status IN (0, 1, 2, 3, 4)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;这个案例里有几个设计决策可以拿出来说说。用户表强制 username 和 email 唯一因为业务上这两者都是登录凭证不能重复手机号没有设成 NOT NULL因为老用户可能没绑定手机用 NULL 表示“未绑定”这里我没用空字符串就是为了语义清晰。订单表的外键用了 ON DELETE RESTRICT意思是用户有订单时不允许删用户这比 CASCADE 安全得多防止手滑删除用户时把整个订单历史全灭掉。amount 加了 CHECK 保证金额不为负status 用 CHECK 限定在合法枚举里。3.2 表建完之后怎么改约束线上表结构变更时学会用 ALTER TABLE 修改约束是一项基本技能。常用操作我都整理在下面。添加约束-- 添加唯一约束 ALTER TABLE user ADD UNIQUE KEY uk_idcard (idcard); -- 添加外键约束 ALTER TABLE order ADD CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user (id); -- 添加 CHECK 约束MySQL 8.0.16 ALTER TABLE user ADD CONSTRAINT chk_age CHECK (age 0); -- 修改字段为 NOT NULL ALTER TABLE user MODIFY COLUMN phone VARCHAR(20) NOT NULL DEFAULT ;删除约束-- 删除唯一约束通过索引名 ALTER TABLE user DROP INDEX uk_idcard; -- 删除主键约束 ALTER TABLE user DROP PRIMARY KEY; -- 删除外键约束 ALTER TABLE order DROP FOREIGN KEY fk_order_user; -- 删除 CHECK 约束 ALTER TABLE user DROP CHECK chk_age;这里要特别提醒删除或添加约束之前一定先在生产环境的从库或者测试库跑一遍确认执行时间可以接受。而且大表上加唯一约束、加主键这类操作会重建表建议用在线变更工具分批处理避免长时间锁表。我见过有人凌晨两点在生产库直接 ALTER 一张上亿的表加唯一索引结果整张表锁了半小时业务告警直接被打爆。3.3 查看约束定义信息排查约束问题时第一步永远是确认表上现在到底有哪些约束。MySQL 提供了几种查看方式各有适用场景。最直接的是 SHOW CREATE TABLE能看到一张表的完整生成语句约束一目了然SHOW CREATE TABLE order\G但 SHOW CREATE TABLE 在约束非常多的时候输出很长看起来费劲。这时候可以查系统表 information_schema-- 查看某张表上的所有约束类型 SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA mydb AND TABLE_NAME order; -- 查看外键的具体引用关系 SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA mydb AND TABLE_NAME order AND REFERENCED_TABLE_NAME IS NOT NULL;我平时排查问题习惯先 SHOW CREATE TABLE 看全貌再用 information_schema 精准查某个约束两个配合基本够用。注意别在生产环境的系统表上随手 SELECT *数据量大的实例上这样查会拖慢整个库。4. 常见问题与排查技巧实录4.1 外键怎么都建不上外键约束创建失败是新手问得最多的问题报错信息五花八门但根因就那么几个。我把这些年遇到的坑总结成一张速查表。报错关键词根本原因解决办法Error 1215 Cannot add foreign key最通用的报错逐个核对下面几项引擎不一致父表或子表不是 InnoDB统一改成 ENGINEInnoDB字段类型不匹配如父表 INT UNSIGNED子表 INT改为完全相同类型包括 UNSIGNED字符集不一致两表默认字符集不同统一为 utf8mb4被引用字段无索引关联的父表字段不是索引父表字段必须是主键或有索引外键字段自身无索引子表外键列没有索引子表外键列建议手动加索引其中“字段类型不一致”这个坑最隐蔽。比如父表主键是 INT UNSIGNED子表外键写成 INTMySQL 认为类型不匹配直接报 1215。还有字符集问题两张表的表级字符集一样但某个列的字符集被单独改过也会建不上。遇到 1215 别慌按表格逐项排查五分钟之内基本能定位。另外一个经常被忽略的点是外键约束要求父表和子表的字段都加上索引。引用时如果父表字段是主键这天然满足子表的外键字段MySQL 在创建外键时会自动帮你建索引但如果是手工建表建议显式写出来命名也更规范。4.2 唯一约束与 NULL 的“相爱相杀”前面提过UNIQUE 约束允许字段存在多个 NULL 值。这个特性在业务上会造成很诡异的数据比如用户表的身份证号字段如果允许 NULL 又加了 UNIQUE 约束那么可以插入 10 条身份证号都是 NULL 的记录业务上这些“未实名用户”实际上是重复的。如果业务要求“未填写的值只能有一条”标准解法是把字段改成 NOT NULL DEFAULT 。但空字符串作为默认值也有问题比如查询WHERE idcard 和WHERE idcard NULL语义不同很容易混。MySQL 8.0 里更优雅的解法是用生成列ALTER TABLE user ADD COLUMN idcard_unique VARCHAR(18) GENERATED ALWAYS AS (NULLIF(idcard, )) STORED, ADD UNIQUE KEY uk_idcard_unique (idcard_unique);把空字符串统一转成 NULL再对生成列加唯一索引NULLIF 会把空字符串变成 NULL这样空值仍然可以有多条但实际业务上“多个空字符串”就被统一成一个 NULL 了配合 NOT NULL 约束就能达成严格唯一。这个技巧比较进阶但确实是处理“唯一但可空”需求的正规军。4.3 约束与性能的平衡怎么拿捏约束不是越多越好。每个唯一约束都对应一个索引写操作要维护所有索引索引太多会拖慢插入和更新速度外键在每次子表写操作时都要对父表加锁检查高并发下可能成为瓶颈。我在实际项目里的取舍标准是三个维度数据一致性要求、写入并发量、表的数据量级。核心交易链路比如订单、支付流水外键和 CHECK 该上就上宁可损失一点写入性能也要保证数据不出错日志、埋点、中间表这类海量写入且对一致性不敏感的表尽量减少约束最好只保留主键和必要的索引。CHECK 约束对性能的影响很小因为它只在写入时做一次条件判断成本可以忽略。真正影响性能的是主键和唯一约束对应的索引尤其是联合索引字段顺序选错不仅约束本身失效一部分查询也走不上索引。我见过一张表建了 (a, b, c) 联合唯一约束业务上却大量按 b 字段查结果每次查询都是全表扫描这纯粹是设计失误。4.4 线上改约束前的三个必做动作线上改约束尤其是给大表加唯一约束、加主键这类操作我总结了一套固定流程踩过几次坑之后就成了条件反射。第一步先在测试环境全量复现记录执行时间。第二步检查从库延迟主从延迟大的时候先等等否则操作过程中主库长时间持有 DDL 锁从库追不上来会拖垮读流量。第三步准备好回滚方案约束变更不像数据更新能简单地反向操作一旦执行完发现问题恢复成本极高所以操作前把原表结构完整备份甚至直接用可扩展的在线改表工具失败了能立刻回切。还有一个心得同一次变更里尽量少混合不同类型的约束修改。比如既要加唯一约束又要改外键还要调字段类型分开几次执行。混在一起出问题后很难定位到底是哪一步导致的。5. 最后再分享几个约束设计的小技巧在建表工具这一环上我强烈建议团队把约束设计沉淀成模板。比如约定所有表必须有自增主键、创建时间和更新时间必须有 DEFAULT、金额字段必须有 CHECK 非负、状态字段必须有 CHECK 枚举把这些规则固化到建表规范里新表直接套模板能少掉一大半低级错误。另外约束和索引的命名也要形成习惯。我个人的命名规则是主键就叫 PRIMARY唯一索引统一uk_前缀普通索引idx_前缀外键fk_前缀加引用表名CHECK 约束chk_前缀加字段名。命名规范的好处是将来查 information_schema 看约束列表的时候一眼就能看出每个约束是干嘛的不用逐个去对建表语句。最后说一个很多人不知道的小细节MySQL 8.0 里 ALTER TABLE 支持原子性了一条 ALTER 语句要么全部成功要么全部回滚不会出现执行到一半留下残缺结构的情况。这是 8.0 相比 5.7 的显著改进如果你还在 5.7 上挣扎又特别在意表结构变更的安全性这会是一个升级的充分理由。约束这个东西看起来只是建表时的几行关键字实际却是整个数据质量体系的基石。我在实际项目中见过太多因为一张表少了一个约束导致后续无数补数据脚本、对账任务和线上事故的情况。花十分钟把约束设计好后面能省下几百小时填坑的时间这笔账怎么算都划算。