平时在 MySQL 里建表写 SQL大家关注最多的往往是索引、查询优化、事务隔离约束CONSTRAINT反而成了最容易被忽略的那块。但数据质量一旦出问题重跑数据、修数、补全、排查重复记录哪个都比当初多写一行约束麻烦十倍。这篇文章不聊那些花哨的调优技巧专门把 MySQL 的约束体系从头到尾捋一遍六大约束类型分别怎么用、什么版本行为有差异、线上加约束要注意什么、漏掉约束会踩哪些坑一次讲透。如果你负责表结构设计、写 DDL或者正在接手一个数据越来越乱的老项目这篇内容值得先收藏再慢慢看。1. 约束不是“限制”是数据质量的最后一道闸门1.1 为什么数据库约束永远比应用层校验更可靠很多团队习惯把所有校验逻辑都写在应用层比如 Java、Go 里判非空、判唯一、查一遍外键是否存在。应用层的校验当然有用但它有两个致命前提:第一所有写入请求都走同一套代码第二代码逻辑一定正确。现实是数据分析师跑个临时脚本、DBA 直接从命令行 update 一条记录、定时任务半夜批量导入数据这些场景根本不经过应用层校验形同虚设。数据库约束是数据进入表结构之前的强制检查只要建在表上不管谁来写、用什么工具写、绕过多少层应用逻辑都必须先过约束这一关。拿唯一约束举例应用层判断用户名是否重复用的是 SELECT在高并发下存在典型的时间差问题两个请求同时查都发现用户名不存在然后同时执行 INSERT结果就插入了两条一模一样的用户名。而数据库层的唯一索引在写入时会做原子性检查真正做到了从源头拦截。从成本角度看约束的存在还能大幅减少应用层的校验代码。非空、默认值、取值范围这些通用规则数据库层面定义一次所有业务系统都能复用不需要每个微服务各自实现一套。1.2 约束体系的完整分类与设计视角MySQL 的约束体系本质上覆盖了三层完整性实体完整性靠主键约束保证每一行都能被唯一标识域完整性靠非空、默认值、CHECK 约束限制列的值范围参照完整性靠外键约束保证表与表之间的引用关系不悬空。外加 UNIQUE 唯一约束处理业务上的“不该重复”场景这六类约束组合起来基本能把脏数据挡在门外。从声明位置上看约束分为列级约束和表级约束。列级约束写在字段定义后边只对当前列生效比如id INT PRIMARY KEY、name VARCHAR(50) NOT NULL。表级约束独立写在所有字段定义之后适用于需要涉及多个列的约束场景比如复合主键、复合唯一键、外键和表级 CHECK。很多新手搞不清两者区别其实判断标准很简单如果这个约束只控制一个字段用列级写法更简洁如果它要跨字段联合判断或者需要显式指定约束名方便以后删除就必须用表级写法。另外约束和索引在物理层面关系密切。主键约束会创建主键索引唯一约束会创建唯一索引外键约束也会在引用列上自动创建索引。这意味着约束不是单纯的数据校验规则它同时还影响着查询计划。所以设计约束时不能只盯校验逻辑还得一并考虑索引成本和查询路径。2. 六大约束逐一拆解语法、细节、版本陷阱2.1 NOT NULL 非空约束空字符串和 NULL 是两回事NOT NULL 是最好理解也最容易被误用的约束。它规定字段值不允许为 NULL但很多人没意识到空字符串和 NULL 在 MySQL 里是完全不同的东西。NULL 表示“没有值”参与运算时结果基本是 NULLCOUNT、SUM之类的聚合函数会自动忽略。而空字符串是一个真实存在的值COUNT会计数WHERE name 也能匹配到。如果你希望某个字段既不能为空也不能是空串光写NOT NULL是不够的需要配合CHECK (col )或者在应用层再补一道校验。还有一类常见场景很多业务表设计soft_delete字段用 0 和 1 表示未删除和已删除。有些同学图省事允许字段为 NULL把 NULL 当成未删除来用。结果就是WHERE soft_delete IS NULL和WHERE soft_delete 0两条 SQL 查出的结果集不一样后续所有关联查询都要小心翼翼。我的建议是布尔类字段一律NOT NULL DEFAULT 0别让 NULL 混进来否则全团队都要为你的省事买单。2.2 UNIQUE 唯一约束允许 NULL 重复这件事必须知道UNIQUE 约束保证列值或列组合不重复但它有一个所有 MySQL 开发者都该背下来的特性唯一约束不限制 NULL而且多个 NULL 被认为是互不相同的。换句话说如果一个允许 NULL 的列上有唯一约束你可以插入无数条该列为 NULL 的记录。这在业务上会造成隐蔽的漏洞比如员工表中的工号字段逻辑上必须有值但设计时忘了加 NOT NULL只加了 UNIQUE结果插入多条emp_no NULL的记录时数据库全部放行重复的“未知工号”记录就产生了。正确的做法是UNIQUE 和 NOT NULL 经常是成对出现的业务上的不可重复字段如果逻辑上也不允许为空一定要两条约束一起上。对于联合唯一索引NULL 的判定规则更复杂——只要联合字段中任意一个为 NULL新插入的记录就不会和已有记录冲突它会被当作一个全新的组合放进去。这个特性有时可以巧妙利用比如软删除场景下用(business_key, deleted_at)做联合唯一多条删除记录对应不同的删除时间戳逻辑上等价于历史版本可回溯但如果把握不好又会变成重复数据的温床。2.3 PRIMARY KEY 主键约束一张表唯一的实体标识主键约束是 UNIQUE 和 NOT NULL 的组合升级版字段值不能重复、不能为 NULL而且一张表只能有一个主键。主键可以建立在单个字段上也可以建立在多个字段上这就是复合主键。复合主键在业务建模中争议很大。数据仓库的宽表、多对多关联的中间表里复合主键确实能准确定位一行数据比如订单明细表用(order_id, line_no)做主键逻辑上清晰自然。但在 OLTP 业务表里我见过太多因为强行上复合主键导致的问题外键关联时要带上所有主键字段查询条件稍不注意就绕过索引主键索引膨胀得厉害。实际项目里绝大多数 OLTP 表我都建议用自增主键或业务单号做主键复合主键留给关联表、明细表这类写多读少、字段稳定的场景就好。主键还有一个容易忽视的性能知识点InnoDB 是聚集索引组织表数据本身就是按主键顺序物理存储的。如果使用 UUID 这类随机值做主键每次插入都可能导致页分裂写入性能会明显劣化。现在的 MySQL 8.0 提供了 UUID_TO_BIN / BIN_TO_UUID 函数可以把字符串 UUID 转成 16 字节二进制存储既保留有序性又不破坏随机性但大多数没有强分布式 ID 需求的系统自增主键依然是最省心的选择。2.4 FOREIGN KEY 外键约束爱恨交织的参照完整性外键约束用于保证表与表之间的引用关系合法比如订单表里的user_id必须真实存在于用户表中。它支持几种级联动作CASCADE级联删除或更新、SET NULL将外键列置为 NULL、RESTRICT / NO ACTION拒绝操作。层级关系上被引用的列必须在父表上建有主键或唯一索引否则外键创建直接报错。另外需要特别注意的是外键只在 InnoDB 存储引擎下真正生效。MyISAM 引擎在语法层面能让你把外键语句写进去但它只是“假装建了外键”数据校验完全不执行。很多人排查半天才发现线上表是 MyISAM这是历史遗留问题但 MySQL 5.7 之后的版本默认都是 InnoDB新项目基本不用再担心。关于外键的使用业界有两种截然相反的声音。互联网大厂多半会禁用物理外键理由是高并发写入下外键的每次检查都会触发布引擎的锁等待批量导入和分库分表时外键约束会严重限制拆分灵活性。但中小型系统、金融类项目、对数据一致性要求极高的场景物理外键能防止大量“脏引用数据”价值远大于性能损耗。我的个人判断是如果你还在单库单表阶段业务并发也不高老老实实用物理外键它带来的维护性价比最高。等真的要分库分表了再考虑在应用层实现引用关系校验。别一开始就模仿大厂架构人家拆外键是踩过坑之后的取舍不是设计原则。2.5 CHECK 约束MySQL 8.0.16 是分水岭CHECK 约束用来限制字段的取值范围比如年龄在 0 到 120 之间、状态字段只能取指定枚举值。但 MySQL 的 CHECK 约束有段很坑的历史8.0.16 之前的版本CHECK 约束会被解析但不会真正执行。也就是说你把CHECK (age 0)写进表里它顺利建表、不报错但插入age -5的数据时数据库也跟着放行。很多开发被这个行为坑过还以为是自己的 SQL 写错了。MySQL 8.0.16 开始CHECK 约束才被真正强制校验。如果你还在用 5.7 版本想实现字段取值限制比较靠谱的方案有两个一是用触发器在 INSERT / UPDATE 前手动判断并抛异常二是在应用层做严格校验。考虑到触发器调试成本高我更推荐 8.0.16 以上版本 原生 CHECK配合应用层校验形成双保险。如果条件允许趁早把数据库升级到 8.0不仅 CHECK 能用了还有窗口函数、CTE、文档 JSON 这类实用特性。CHECK 约束还能实现一些组合限制比如CHECK (end_time start_time)检查时间区间有效性CHECK (gender IN (M, F) OR gender IS NULL)实现枚举 可空。这些单靠应用层判断非常容易漏尤其是很多人共用的底层表。2.6 DEFAULT 默认值约束不是所有列都能有默认值DEFAULT 约束指定字段未显式赋值时使用的默认值属于最容易理解、但也藏着版本差异的一个约束。MySQL 5.7 之前默认值只能是常量DEFAULT CURRENT_TIMESTAMP特殊一点但也只支持时间戳。MySQL 8.0.13 开始支持表达式默认值比如DEFAULT (UUID())、DEFAULT (RAND()*100)这给设计带来了很大灵活性。有几个字段类型上的限制要提前知道BLOB / TEXT / GEOMETRY 类型的字段在 5.7 及以前不能直接设置默认值想要默认值只能通过触发器或者给应用层传值来曲线实现但在 MySQL 8.0.13 之后这个限制被放开了一些表达式默认值对部分大字段类型也适用了。DEFAULT 和 NOT NULL 搭配使用是最常见的组合。比如status TINYINT NOT NULL DEFAULT 0插入时如果不显式传 status会自动落成 0但如果字段只是DEFAULT 0而没有NOT NULL插入时传 NULL 仍然会把 NULL 写进表里默认值不会生效。这一点经常在实际项目中造成上层应用以为传了默认值、数据库却存了 NULL 的尴尬结果。3. 约束的日常管理加、看、删、改的完整实操3.1 在已有表上添加约束的 ALTER 语法约束最好在建表时就定义清楚但现实里更多情况是接手老项目发现表结构根本没约束数据已经开始乱了。在已有数据上添加约束时MySQL 会先做一遍全表校验如果存量数据不满足约束条件ALTER 语句会直接报错拒绝执行不会有任何中间状态。以下加约束的语句都基于 MySQL 8.0 语法可以直接照抄-- 添加非空约束本质是修改列属性 ALTER TABLE user MODIFY COLUMN email VARCHAR(128) NOT NULL; -- 添加唯一约束指定约束名 ALTER TABLE user ADD CONSTRAINT uk_user_email UNIQUE (email); -- 添加 CHECK 约束 ALTER TABLE user ADD CONSTRAINT ck_user_age CHECK (age 0 AND age 120); -- 添加外键约束 ALTER TABLE order ADD CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE RESTRICT ON UPDATE CASCADE; -- 删除约束 ALTER TABLE user DROP INDEX uk_user_email; ALTER TABLE user DROP CHECK ck_user_age; ALTER TABLE order DROP FOREIGN KEY fk_order_user;这里有几个关键点要提醒删除唯一约束用的是DROP INDEX因为唯一约束底层就是唯一索引删除主键约束是ALTER TABLE ... DROP PRIMARY KEY删外键用DROP FOREIGN KEY但注意外键字段上系统自动创建的索引不会自动删除需要手动DROP INDEX清理。3.2 查看约束信息的两类方式想查一张表当前有哪些约束最直接的是SHOW CREATE TABLE table_name从 DDL 定义里能看到所有约束声明。但 DDL 输出信息比较杂我平时更习惯通过查系统表来精准提取-- 查表上的约束类型、约束名 SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA your_db AND TABLE_NAME your_table; -- 查唯一索引和主键涉及的列 SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE FROM information_schema.STATISTICS WHERE TABLE_SCHEMA your_db AND TABLE_NAME your_table; -- 查外键的引用关系 SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA your_db AND TABLE_NAME your_table;排查线上问题的时候这三条 SQL 基本能覆盖大部分需求。特别是外键相关的报错用KEY_COLUMN_USAGE能快速定位父表和子表的引用链。3.3 约束命名的规范与维护习惯约束命名的规范程度直接影响团队协作效率。我推荐一套常见的命名模式主键PK_表名唯一约束UK_表名_字段外键FK_当前表_关联表CHECK 约束CK_表名_字段。举个例子PK_user、UK_user_email、FK_order_user、CK_user_age。这样在后端排查、系统表中定位约束时一眼就能看出约束的类型和归属不需要再去翻 DDL。实际维护中还有两条经验第一无论建表还是 ALTER尽量给每个约束显式命名不要依赖 MySQL 自动命名。自动命名的规则比如外键会生成表名_ibfk_编号在删除、切换环境、做数据迁移时非常难对号入座。第二在大表上添加约束前先在测试环境用同样的数据量跑一遍 ALTER评估执行耗时。比如对一张千万行的表加唯一索引如果存量数据里潜伏着重复值ALTER 会直接失败你只能先查重、清理、再重新执行整个过程的停顿时间要做好预案。4. 复合约束和业务建模从单列限制到跨字段规则4.1 联合唯一约束业务防重的不二选择单列唯一约束只能管一个字段但真实业务里大量防重规则是跨字段的。比如购物车表里(user_id, sku_id) 的组合应当唯一表示同一个人不能把同一个商品加两次订单商品明细表里(order_id, sku_id) 避免重复录入签到表里 (user_id, sign_date) 表示每人每天只能签到一次。这些场景联合唯一索引就是最直接的兜底方案。联合唯一约束的另外一个细节是它创建的联合索引对查询也很关键。比如你建了UK (user_id, sku_id)这个索引对WHERE user_id ?的查询有效但对WHERE sku_id ?的查询基本没用因为联合索引遵循最左前缀原则。所以设计联合唯一约束时字段顺序不仅要考虑业务语义还要考虑实际查询模式。把区分度高、等值查询多的字段放左边索引利用率会明显更高。这一点经常被忽略最后为了一条防重规则项目里不得不额外建一个单列索引白白增加写入开销。4.2 触发器 约束组合实现更复杂的校验逻辑约束能解决格式、范围、唯一性这类规则但某些跨表的业务规则光靠标准约束表达不了。比如下订单前要求用户账户状态是正常且余额充足这个判断要同时读取多张表CHECK 约束做不到。标准的做法是应用层事务里先查再写但为了防止并发情况下的漏判也可以在数据库层加触发器做最后一道校验。触发器在 INSERT 之前判断、通过SIGNAL语句抛出异常可以有效拦截不符合业务规则的写入。但触发器是把双刃剑它隐式执行排查问题时很难发现而且在主从复制环境下一不小心就把逻辑复制到从库执行造成不可预期的偏差。我的建议是触发器只保底不主用。能用应用层事务解决的业务规则就别拖到数据库触发器里数据库层的约束体系做好它最擅长的事格式、范围、唯一性、引用完整性。4.3 业务表设计实战完整约束方案示例拿一个用户 订单模型来收束上面的内容这是一套比较完整的落地范式CREATE TABLE user ( id BIGINT UNSIGNED AUTO_INCREMENT, username VARCHAR(32) NOT NULL, email VARCHAR(128) NOT NULL, age TINYINT UNSIGNED NULL, status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_user_username (username), UNIQUE KEY uk_user_email (email), CONSTRAINT ck_user_age CHECK (age 0 AND age 120), CONSTRAINT ck_user_status CHECK (status IN (1, 2, 3)) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4; CREATE TABLE order ( id BIGINT UNSIGNED AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(10, 2) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_order_user (user_id), CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE RESTRICT ) ENGINE InnoDB DEFAULT CHARSET utf8mb4;这个模型里用户名、邮箱不可重复且不能为空账号状态限定枚举值年龄限定合法范围订单号全局唯一订单和用户的外键关系受物理外键保护。user_id上的普通索引idx_order_user即使不手动建外键约束也会自动创建写在这里是为了语义明确。ON DELETE RESTRICT的含义是用户存在未删除的订单时禁止直接删除用户记录这比 CASCADE 静默删掉用户的历史订单安全得多在金融、电商这类需要审计留痕的系统里尤其重要。5. 生产环境加约束的坑与性能影响5.1 大表加约束的耗时问题和在线变更方案前文提到过给已有数据的表加约束会触发全表扫描校验。对千万行级别的表来说直接执行ALTER TABLE可能锁表几分钟到几十分钟这在线上是不可接受的。标准解法是使用在线变更工具。用得最多的是 Percona Toolkit 里的pt-online-schema-change它通过创建临时表、同步增量数据、切换表名来完成结构变更业务基本无感知。命令大致长这样pt-online-schema-change Dyour_db,tyour_table \ --alter ADD UNIQUE INDEX uk_user_email (email) \ --host127.0.0.1 --userxxx --passwordxxx --execute工具会检查存量数据是否满足唯一约束如果不满足会直接终止它还依赖目标表上有主键或唯一索引否则没法做增量同步。另外变更期间如果业务写入非常频繁工具在最终切换时也可能遇到锁竞争建议在业务低峰期操作并且先在小表上演练一遍流程。基于开源工具的做法有时有限制如果你用的是阿里云 RDS也可以直接在控制台使用无锁变更功能体验更省心。5.2 外键带来的死锁和锁等待物理外键并非没有代价。子表插入数据时InnoDB 会对父表的被引用行加共享锁确保父表行在事务提交前不被删除或修改。如果高并发下多个事务同时插入子表、引用父表同一行加上父表本身有 UPDATE 操作就会出现锁等待甚至死锁。排查这类问题的方法很简单开启SHOW ENGINE INNODB STATUS查看最近的死锁信息结合performance_schema.data_lock_waits表确认锁等待链路定位到具体 SQL 后要么调整事务顺序、缩短事务持锁时间要么在有外键的表的写入链路上降并发。说到底外键约束本来就是为了 b 稳定一致性而牺牲部分并发性的适用场景要心里有数。5.3 约束与复制延迟、批量导入的冲突批量导入场景下约束同样会产生影响。用LOAD DATA或脚本逐条插入千万级数据时行数据每插入一条所有相关索引包括唯一索引约束产生的索引都要同步更新批量任务的总耗时会明显拉长。另外如果导入数据中间出现违反约束的批MySQL 默认半途回滚可能会让之前一大批数据全部丢失。所以批量导入大文件之前最好先预处理数据去除重复值、补齐必填字段、校验外键引用存在性让数据库在执行时只走“过约束”这一步而不是反复报错反复启动回滚。另一个值得提的是主从复制。如果主库表上有约束、从库表上因为历史原因缺了约束某些潜在违规数据在主库上被拦截从库上却被正常写入逻辑上倒不会出错但从库表结构和主库不一致本身就是隐患后面做主从切换时可能踩坑。规范操作是从库和主库的约束定义保持一致结构差异需要走统一的变更流程。6. 常见问题速查约束报错的定位与处理6.1 约束相关报错的典型场景和对策加约束、查数据时最常见的几类报错都列在这里直接用表格速查很方便。报错信息原因处理方法Duplicate entry xxx for key uk_xxx插入数据违反唯一约束或给有重复数据的老表添加唯一约束先查重清理存量数据再修正写入逻辑应用层捕获 1062 错误做降级处理Column xxx cannot be null插入值为 NULL违反 NOT NULL 约束修改传参或设置默认值检查程序里是否有未赋值字段Cannot add or update a child row: a foreign key constraint fails子表插入的外键值在父表不存在检查引用数据是否已存在事务内先插父表再插子表Cannot delete or update a parent row: a foreign key constraint fails删除或修改父表记录时被子表引用按业务选择先删子表或改用 ON DELETE SET NULL / CASCADECheck constraint xxx is violated插入数据不满足 CHECK 范围检查枚举值、区间限制确认版本是 8.0.16 以上且约束真正生效Cannot drop index xxx: needed in a foreign key constraint删除某个索引时发现它是外键列上的必要索引先删除对应外键约束再删除索引6.2 排查约束问题的一个完整思路当线上出现疑似约束引发的数据问题时我通常按下面四步走。第一步定位表结构。用SHOW CREATE TABLE快速确认表上有哪些约束重点核对业务字段是否被漏加了 NOT NULL 或 UNIQUE。第二步查看数据异常。用分组统计查重复、查 NULL、查越界值比如SELECT email, COUNT(*) FROM user GROUP BY email HAVING COUNT(*) 1一次性定位存量问题。第三步回溯写入链路。如果是应用层报错抓当时的 SQL 语句和参数确认是哪一环把非法值传了进去。第四步修复和预防。要么清理数据加约束要么调整代码逻辑并在测试环境验证加约束不影响正常业务。这套排查思路适用于大多数约束问题。有时候问题并不出在数据库端而是上游接口漏传了字段或者历史数据本身就没洗好排查时需要跳出数据库本身从整个数据链路里去找原因。6.3 避免约束设计中的几个常见误区我自己吃过亏的几个地方直接分享出来第一极端依赖应用层校验数据库裸奔。长期做下来各种脏数据渗入清洗成本远超当初省下的开发成本。第二动不动就上复合主键把 OLTP 表设计成宽表风格后期查询和关联都受限。第三枚举字段的取值不用 CHECK 规范而是靠口头约定。换了个同事维护往表里插入一个全新的状态值业务代码里根本没这个分支问题排查半天。第四给唯一约束字段留了 NULL 的口子结果 NULL 成了“数据黑洞”重复记录藏了一堆才发现。建表时多思考一分钟约束未来就能少熬一个通宵排查数据。这不是口号是每个经历过数据事故的工程师最真实的体会。7. 最后的实操建议给约束做一次 “体检”如果你已经在维护一个跑了一段时间的数据库我的建议是定期做一次约束体检选几条核心业务表用上面的系统表 SQL 把所有索引和约束列出来再结合SHOW CREATE TABLE逐张表核对字段是否该有主键、是否有重复漏网、是否有外键缺失。哪怕现在加约束要付出 ALTER 的短暂代价也比未来某天线上写入一批重复订单后再花几天清洗数据来得划算。版本选择上还在用 5.7 的团队至少把 CHECK 约束的行为差异记住你以为它生效了其实它没生效。有条件就升到 8.0约束层面的体验完全不一样。数据库的版本升级虽然听着工程量大但它换来的稳定性和功能的长期收益往往会被低估。MySQL 约束这一块整体不难难的是用的时候有没有系统性地想清楚每一条规则以及在业务模型里真正践行“数据完整性优先”这件事。