前阵子帮朋友排查一个库存系统的数据异常打开后台订单表第一眼就看到三笔订单的customer_id对应不上客户表里的任何一行。再往后翻同一张表里还有两条重复的员工编号只是姓名不一样。排查到最后根子不在业务代码而是建表的时候根本没定义约束——数据入口完全敞开任何绕过前端校验的请求都能把脏数据写进去。MySQL基础课程里讲到约束这一块很多人觉得简单无非就是非空、唯一、主键、外键、检查、默认值这几个关键字。但实际拉过生产环境数据库的人都会明白约束用得好不好直接决定了数据会不会在三个月后变成一锅粥。这篇文章就围绕mysql基础中的约束展开把每个约束类型背后的行为语义、适用场景、常见坑都过一遍最后给你一套可以直接抄走的建表规划思路。适合刚在学数据库的初学者也适合那些建表比较随意、经常被脏数据折磨的开发同学。1. 约束到底解决什么问题从表结构失序说起1.1 没有约束时脏数据是怎么一步步混进来的很多项目刚上线时表结构干干净净业务逻辑也正常脏数据往往不是一天冒出来的而是从几个“不起眼”的裂缝里慢慢渗进来的。第一周运营在后台录入了一批历史订单为了赶工时直接写SQL导入少了两个必填字段导入程序也没拦两张表的数据对不齐。第二周开发A在服务端加了参数校验开发B觉得“前端已经校验过了”于是没加同一个页面里两个接口行为不一致某些字段开始出现NULL。一个月后报表组拉数发现同一客户有两条档案团队花了两天去重然后临时在代码里写了过滤条件但仓库里的原始脏数据还在。三个月后数据库里已经没人敢说清楚哪些数据是准的。这个过程的根源并不是某个人的失误而是错误没有在下游建立防线。应用层校验确实能挡住一部分问题但它挡不住所有入口定时任务、人工SQL、历史数据迁移、其他团队的服务只要有一个入口绕过了校验脏数据就能进来。约束的作用就是把规则下沉到数据库这一层让所有写入数据的路径都必须遵守同一套底线规则。1.2 三类完整性与约束的对应关系数据库理论里有个经典概念叫“数据完整性”约束就是它的落地方式。完整性主要分三类每类对应的约束也很清晰实体完整性保证表中每一行能被唯一区分典型就是主键约束和唯一约束。域完整性保证字段值在合理的范围内典型就是非空约束、检查约束和默认值。引用完整性保证表与表之间的关联关系有效典型就是外键约束。还有一个“用户定义完整性”意思是你自己定义的业务规则比如“员工的薪资不能高于部门主管”这类规则用CHECK约束可实现一部分。用一句话概括约束不是给数据库找麻烦的功能而是给数据上加“保险栓”。没有这些保险栓索引优化再厉害、缓存层再厚最后服务的是都是不靠谱的数据。2. 六大约束逐个拆解定义、行为与适用场景2.1 NOT NULL 非空约束最容易忽略的“地基”NOT NULL是最基础的约束但它经常被误用、忽视。很多人以为“默认不加NOT NULL字段就可以接受NULL”这个认知本身没错但问题是你确定业务允许这个字段没有值吗先搞清楚NULL和空字符串的区别。空字符串表示“这个字段有值只不过内容为空”而NULL表示“这个字段的值是未知的、缺失的”。两者在数据库里是完全不同的东西。email字段存代表用户注册时填了一个空邮箱存NULL代表系统根本不知道用户有没有邮箱。语义上差很远。NULL还有个很麻烦的特性参与任何计算都会传染。比如salary bonus只要salary是NULL结果就是NULL。写WHERE salary 5000时salary为NULL的行也不会被查出来因为NULL和任何值比较的结果都是“未知”条件不成立。很多报表数据莫名其妙少了几行往往就是NULL列在作怪。业务上怎么决定哪些字段加NOT NULL我的习惯是一唯一业务标识必须有值比如工号、订单号、流水号二只要参与排序、分组、筛选的重要字段能非空就非空三如果某字段语义是“用户没填所以留空”我更喜欢用NULL而不是空串因为NULL在数据库语义里更能表达“缺失”配合COALESCE函数展示默认文案也更清晰。CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL, nickname VARCHAR(32) NOT NULL, phone VARCHAR(20), PRIMARY KEY (id) );上面phone没加NOT NULL语义就是用户可以不填手机号。2.2 UNIQUE 唯一约束唯一不代表主键UNIQUE约束保证列或列组合的值不重复。它可以和PRIMARY KEY同时存在一张表也允许有多个唯一约束。很多人问唯一约束和唯一索引有什么区别从逻辑层面看约束是一种规则从物理层面看MySQL为了实现这个规则背后建的就是唯一索引。两者在InnoDB里几乎是同一个东西只是角色定位不同。一个典型的应用场景是主键用自增ID但业务上还需要保证手机号唯一。这时候手机号列就加UNIQUE约束。还可以做组合唯一比如同一订单下不允许出现重复的商品行可以用UNIQUE KEY uk_order_product (order_id, product_id)。这种约束在购物车、订单明细、配置表里特别常用。要注意的是UNIQUE和NULL的关系。MySQL里UNIQUE索引允许存在多行NULL因为NULL被视为“未知”多个未知值不认为彼此冲突。这个语义有时正好符合业务用户可以不填手机号而且没填手机号的用户可以有多个但填了手机号的用户手机号不能重复。如果你希望“所有人都必须填手机号且唯一”那就得phone VARCHAR(20) NOT NULL UNIQUE两个约束一起用。2.3 PRIMARY KEY 主键约束InnoDB物理存储的核心主键约束是唯一约束的“加强版”一张表只能有一个主键主键列不允许NULL并且主键列自动创建聚簇索引。为什么主键特殊因为InnoDB本身就是索引组织表数据行物理上就是按聚簇索引的顺序排列的。如果你定义了主键主键就是聚簇索引如果你没定义主键InnoDB会找第一个非空的唯一索引来当聚簇索引如果连这个都没有它会在内部生成一个隐藏的6字节rowid来组织数据。也就是说InnoDB无论如何都会有一个隐式主键区别只在于你能不能控制它。这个底层逻辑带来的结论很实际主键的长短直接影响整棵B树的大小也影响所有二级索引的存储空间因为二级索引叶子节点存的是主键值。主键越短索引体积越小写入越快。所以业务上一个常见的建议是优先使用自增BIGINT作为主键而不是用很长很随机的字符串。UUID不是不能用但随机UUID做主键会带来随机插入、页分裂、碎片化等问题性能上要付出代价这个话题后面单讲。2.4 FOREIGN KEY 外键约束关系数据库的“逻辑粘合剂”外键约束是引用完整性的核心。它保证子表里的外键值必须在父表主键里存在。比如员工表里的dept_id如果引用了部门表那么数据库会阻止你给员工分配一个不存在的部门编号。外键可以定义删除和更新时的行为常见的有RESTRICT / NO ACTION默认行为父表被引用时不允许删除或更新。CASCADE父表删除子表对应记录也被删除父表主键更新子表外键跟着更新。SET NULL父表删除或更新时子表外键设置为NULL外键列得允许NULL。很多人对外键有抵触情绪主要是怕它影响性能。这种担心有一定道理但要看场景。外键的代价在于每次往子表插入或更新数据时都要去父表检查一次引用是否存在每次删除或更新父表时要去子表检查有没有引用。并发高的时候外键会引入额外的锁开销。但如果你做的系统还在单体架构阶段数据量没有到千万级外键约束带来的“可靠性收益”远远大于那点性能开销。我见过太多没建外键的项目应用层代码_delete部门_忘记同步员工导致一堆孤儿记录最后加班写脚本修复。所以我现在的原则是同一个MySQL实例里的核心业务表能加外键就加分库分表后外键确实没法用再靠应用层弥补。这里有个MySQL的隐藏行为值得说创建外键时如果子表外键列没有索引InnoDB会自动创建一个索引这和其他数据库不太一样。这个特性同时也说明外键列必须能高效查找否则检查引用时的性能会很难看。2.5 CHECK 检查约束从MySQL 8.0开始才真正“硬起来”CHECK约束用来限定字段值的范围比如年龄必须大于0、余额不能为负数。这是域完整性里非常直观的规则。但这里必须划重点MySQL 8.0.16版本是分水岭。在这个版本之前MySQL虽然会解析CHECK子句但实际上是“接受并忽略”约束根本不生效。什么意思你写了CHECK (age 0)照样能插入-5不报错不拦截。很多人在旧版本上调试半天以为是自己的SQL写错了其实是版本没到位。MySQL 8.0.16之后CHECK约束才开始真正强制生效。比如CREATE TABLE account ( id BIGINT UNSIGNED PRIMARY KEY, balance DECIMAL(10,2), CONSTRAINT ck_balance_nonnegative CHECK (balance 0) );这时插入负余额就会直接报错。CHECK约束还有一个限制不能引用其他表的列不能包含子查询表达式结果必须是确定性的。也就是说你没法用CHECK去检查“这个员工的部门ID是否在部门表里”那是外键的职责别混用。2.6 DEFAULT 默认值常见却被误用严格来说DEFAULT不是约束关键字它是列属性但它对域完整性的作用非常大所以通常放在约束家族里一起说。最常见的默认值用法是状态字段status TINYINT NOT NULL DEFAULT 1。这样应用层即使忘了传status数据库也会给一个初始值避免“NULL状态”这种模糊数据出现。另一个高频场景是时间字段created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP插入时自动带当前时间。MySQL 8.0.13之前默认值只允许常量和少数特殊函数比如CURRENT_TIMESTAMP不允许任意表达式8.0.13之后支持了带括号的表达式默认值比如CREATE TABLE ticket ( id BIGINT UNSIGNED PRIMARY KEY, expire_at DATETIME NOT NULL DEFAULT (DATE_ADD(NOW(), INTERVAL 7 DAY)) );这里“DEFAULT (表达式)”的括号必须写否则报错。这类写法在优惠券、邀请链接、免费试用等场景很实用。3. 建表时的约束组合策略一个完整案例3.1 从业务语义反推约束清单光背约束定义没意义真正考验人的是如何在建模时把约束组合到位。我拿一个典型的“部门-员工”项目作为例子走一遍。业务需求是部门有部门编号编号全局唯一部门名称不能为空员工有工号工号全局唯一员工必须实名姓名不能为空邮箱自愿填写但填了就不能重复员工必须归属某个部门薪资必须大于0员工状态默认在职入职日期必填。逐条映射下来就是部门编号UNIQUE部门名称NOT NULL员工工号UNIQUE作为业务主键也可以员工姓名NOT NULL邮箱UNIQUE可空部门IDFOREIGN KEY 引用部门表薪资CHECKsalary 0状态NOT NULL DEFAULT 1入职日期NOT NULL3.2 建表DDL完整演示CREATE TABLE department ( dept_id BIGINT UNSIGNED AUTO_INCREMENT, dept_code CHAR(4) NOT NULL, dept_name VARCHAR(64) NOT NULL, PRIMARY KEY (dept_id), UNIQUE KEY uk_dept_code (dept_code), UNIQUE KEY uk_dept_name (dept_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE employee ( emp_id BIGINT UNSIGNED AUTO_INCREMENT, emp_no VARCHAR(20) NOT NULL, emp_name VARCHAR(64) NOT NULL, email VARCHAR(128), dept_id BIGINT UNSIGNED NOT NULL, salary DECIMAL(10,2), status TINYINT NOT NULL DEFAULT 1, hire_date DATE NOT NULL, PRIMARY KEY (emp_id), UNIQUE KEY uk_emp_no (emp_no), UNIQUE KEY uk_email (email), CONSTRAINT fk_employee_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id), CONSTRAINT ck_salary_positive CHECK (salary 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;注意几点雇员的dept_id我用了NOT NULL因为业务要求“员工必须归属某个部门”邮箱列没加NOT NULL但加了UNIQUE实现“可填但不能重复”薪资的CHECK单独起了名字ck_salary_positive方便后续删除和管理。如果建表时不给CHECK起名MySQL 8.0会自动生成一个约束名比如employee_chk_1后期看了头大。3.3 约束命名规范和可维护性思维约束名不是为了好看是为了能维护。主键、唯一约束、外键、检查约束我推荐按统一模式命名主键PK_表名唯一UK_表名_字段名外键FK_表名_关联字段名检查CK_表名_字段名约束名在一个库的范围内必须唯一。如果命名太随意后面对约束做修改、删除时你就得靠information_schema去猜哪个名字是干嘛的。查询方式SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA 要查询的库名;查外键的引用关系用SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA 要查询的库名;养成给约束起名的习惯以后排障会节省大量时间。4. 修改约束的实操细节ALTER TABLE 避坑指南4.1 后补约束的完整语法项目上线后没建约束后期想补这是常态。用ALTER TABLE逐个补-- 添加唯一约束 ALTER TABLE employee ADD CONSTRAINT uk_emp_idcard UNIQUE (id_card_no); -- 添加检查约束 ALTER TABLE employee ADD CONSTRAINT ck_salary_positive CHECK (salary 0); -- 添加外键约束 ALTER TABLE employee ADD CONSTRAINT fk_employee_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id); -- 删除外键约束 ALTER TABLE employee DROP FOREIGN KEY fk_employee_dept; -- 删除检查约束MySQL 8.0.19及以上支持DROP CONSTRAINT ALTER TABLE employee DROP CONSTRAINT ck_salary_positive;这里有个版本相关的坑MySQL 8.0.19之前不支持通用的DROP CONSTRAINT语句删除外键要用DROP FOREIGN KEY删除主键要用DROP PRIMARY KEY删除唯一约束要用DROP INDEX。8.0.19之后才统一支持DROP CONSTRAINT。你在写脚本时得先确认目标实例的版本否则会报语法错误。删除自增主键还有个更隐蔽的坑如果主键列带有AUTO_INCREMENT属性直接ALTER TABLE ... DROP PRIMARY KEY会报错。你需要先修改列定义去掉自增属性再删主键ALTER TABLE employee MODIFY emp_id BIGINT UNSIGNED NOT NULL; ALTER TABLE employee DROP PRIMARY KEY;这种操作哪怕在测试库也要先备份数据上面的步骤顺序一旦中间断了表状态可能变得很别扭。4.2 大表加约束的执行代价为什么不能手一抖就执行给一张几百万行、甚至上亿行的表加约束不是跑一条SQL那么简单。加唯一约束本质上是要“扫描全表 校验重复值 构建唯一索引”加CHECK约束要“扫描全表逐行校验”加NOT NULL约束如果列里已经存在NULL还要准备先做数据清洗。MySQL 8.0在DDL执行方式上做了很多优化有INSTANT、INPLACE、COPY几种算法。INSTANT最快只修改元数据但支持的操作有限主要是新增列等场景INPLACE是在原表上重建部分操作允许并发DMLCOPY是最慢的要拷贝整张表数据而且锁表严重。加约束这类操作很多走的是INPLACE或COPY无论如何都会有后台扫描和建索引的过程。我踩过的教训是大表加唯一约束之前要先自己查一遍重复值哪怕代码逻辑上觉得不可能重复SELECT id_card_no, COUNT(*) FROM employee GROUP BY id_card_no HAVING COUNT(*) 1;如果有重复ALTER TABLE直接报错还会留下一个半成品操作。更稳的做法是在低峰期操作提前评估表大小和磁盘空间必要时借助在线变更工具处理而不是大白天直接在生产库跑。另外加完约束不代表万事大吉还要在监控里观察主从延迟因为DDL在复制链路里也会产生负载。5. 真实业务中的约束踩坑记录5.1 版本不同导致CHECK约束“形同虚设”我接手过一个老项目建表语句里写了CHECK (age BETWEEN 1 AND 120)测试环境是MySQL 8.0一切正常。上生产后运维说生产库是MySQL 5.7应用第二天开始收到大量数据异常。查到最后才发现5.7的CHECK约束根本不强制插入的年龄甚至可以是“abc”这种字符串——因为age字段定义的是VARCHAR数据库连类型都没有硬性限制。这个案例说明两件事一是生产环境的MySQL版本必须提前确认不能拿本地8.0的语法和行为去套生产二是MySQL的约束行为在不同版本之间差异很大写SQL时要时刻记得版本边界。5.7里唯一靠谱的“检查约束”替代方案是在应用层校验或者用触发器强行拦截但触发器维护成本高不如直接升级到8.0来得省心。5.2 外键引发的并发删除死锁和批量处理陷阱外键用得好是保障用得不好是灾难。有一次我们做一个清理任务要按条件删除用户表里的一批用户同时删除关联的订单表记录。任务用多线程并发跑结果频繁死锁。原因是删除父表记录前InnoDB需要检查子表有没有引用这个检查会对子表相关行加锁多个线程同时删除父表不同记录、又同时往子表加锁锁等待形成回路就死锁了。解决思路是调整删除顺序和粒度先删子表再删父表或者减少并发线程数用单线程分批删除。另外一个常见坑是外键列虽然有自动创建的索引但如果你在批量插入子表时没有按照父表的顺序去操作外键检查的开销会被放大。生产中如果批量导入数据我建议先导入父表再导入子表并且把大批量拆成小批次给外键检查留出余地。5.3 唯一约束遇上NULL的微语义坑再聊一个容易被业务测试漏掉的场景。用户在注册时可以不填手机号数据库里phone列是可空的同时加了UNIQUE约束。测试发现用户A没填手机号用户B也没填手机号两条记录都能插入成功。产品看报告时嘀咕“不是说要手机号唯一吗为什么两个空手机号能共存”从数据库语义上讲这是正常的NULL不算重复值。但从产品角度这个“唯一”并没有覆盖“空手机号”的场景。如果业务就是要“所有注册用户手机号必须唯一”那唯一约束之前必须加NOT NULL约束让空手机号根本进不来。这个微语义在很多数据库里都一样建模时如果没想清楚等上线后才发现往往要动表结构代价很大。5.4 自增主键与UUID的持久战最后聊聊主键类型选择。自增BIGINT主键一直是关系库的默认选择因为它顺序插入对B树友好不会频繁导致页分裂。但它在分布式、多库合并场景下问题也明显多个节点各自生成自增ID合并时必然冲突另外自增ID可被猜测在某些业务里容易泄露数据量。UUID作为主键全局唯一适合分布式但32位十六进制字符串太长而且标准版UUID随机性太强插入时聚簇索引的页分裂很严重写性能明显下降。折中方案是使用雪花算法生成有序的BIGINT ID或者在MySQL 8.0里把UUID转成二进制后存储配合存储函数生成有序UUID如UUID_TO_BIN函数的swap_flag参数。但不管选哪种这个决策要在建表前定下来因为主键一旦使用频繁后面换主键是伤筋动骨的活。我在实际项目里的选择标准是单库单实例、数据规模可控直接自增BIGINT有分布式合并需求优先雪花ID团队对索引原理熟悉也可以用有序UUID方案。没有银弹只有取舍。最后说点个人体会约束这东西属于“建表时省五分钟以后熬夜补几天”的典型。我后来养成一个习惯每张表建完之后都习惯性地对照检查一遍主键定没定业务唯一字段有没有加唯一约束关联字段是不是该加外键数值字段有没有合理的CHECK兜底状态字段有没有默认值。不需要多复杂但这些检查真的能拦住绝大多数脏数据。给正在看这篇文章的朋友一个建议你可以打开手头项目的数据库用information_schema查看一下现有表的约束情况。如果发现一半以上的关联表都没有外键、核心业务表连唯一约束都没有那说明这张表已经在裸奔了。与其等数据乱到无法收拾不如挑个低峰期把该补的约束补上。数据库不会替你做业务判断但它能帮你守住最后一道底线。