我接触MySQL的时间越久越觉得建表是最能看出一名后端或运维功底的环节。很多人写业务代码时逻辑清晰一到建表就放飞自我字段类型随便定约束能省则省结果上线半年重复数据、脏数据、删不动的关联数据全来了。“MySQL——表的约束”这个话题不夸张地说是数据完整性的命门。约束就是数据库给我们提供的“门禁系统”它能在数据写入之前就把非法值拦住而不是等到应用层出bug再去擦屁股。这篇文章适合刚学会CREATE TABLE的初学者也适合被线上数据质量折磨过的后端同学我会把主键、唯一、非空、默认、检查、外键、自增这些约束的底层逻辑和实操细节一次讲透。1. 约束的本质为什么你的表需要“门禁”1.1 数据完整性到底是什么先讲一个真实场景注册接口在应用层做了手机号非空校验但是运维同学手抖写了一条脚本绕过应用直接往用户表插数据手机号字段写了一个NULL。结果这条记录在登录、营销推送、客服查询时全线出错。这种问题不是偶发而是没在数据库层加约束导致的必然事故。数据完整性包含四个维度实体完整性、域完整性、引用完整性和用户定义的完整性。实体完整性对应主键约束保证每一行能被唯一识别域完整性对应非空、默认、检查约束保证字段值合法引用完整性对应外键约束保证表间关联不产生孤儿数据用户定义的完整性则是一堆自定义规则比如状态字段只能取1、2、3。生活化类比表是公寓楼约束就是门禁系统。主键是每户唯一的门牌号唯一约束是“一个手机号只能实名绑定一个账号”非空约束是“入住必须填真实姓名”默认约束是“没填备注就默认写无”检查约束是“未成年人不得入住主楼”外键约束是“租户必须签约对应房东”。没有门禁的公寓谁都能进什么数据都能塞进来最后只能靠人力挨个排查。1.2 约束比业务代码可靠在哪业务代码校验看似灵活但它有三个致命弱点。第一绕过方式太多后台脚本、数据修复SQL、同步任务、人工运维操作不可能每个入口都写一段相同的校验逻辑第二并发场景下应用层校验不可靠两个请求同时插入相同手机号都能通过校验但数据库里却会出现两条脏数据第三应用层校验本质是“事后判断”数据在到达数据库前还有很多环节任何一环有漏洞脏数据就进来了。数据库约束是最后一扇门而且是原子性执行。MySQL在执行INSERT、UPDATE时约束检查与数据写入在同一个事务引擎内部完成不存在“检查完又被别人写走”的竞态。声明式约束也省掉大量重复代码你只需要在DDL里写一次UNIQUE所有入口全部生效。对比一下如果你靠业务层去判断“手机号是否已存在”每个语言、每个服务都要重新写一遍还容易出偏差。提示约束的本质是把数据规则下沉到存储层而不是留给上层应用去“自觉遵守”。这是数据库设计的基本功也是线上数据质量的最后防线。2. 六大约束逐一拆解从建表SQL到业务场景2.1 主键约束每张表的身份证主键约束是表设计的核心没有主键的InnoDB表就像没有身份证的人出了问题根本找不到是哪一条。主键有两个硬性要求非空且唯一。一张表只能有一个主键但可以有一个联合主键也就是多个字段共同组成唯一标识。几乎所有业务表都会用一个自增整数或雪花ID做主键。自增整数简单直观适合中小型系统分布式场景下更推荐雪花ID或UUID但要注意UUID作为主键会让聚簇索引随机写入产生大量页分裂写入性能会明显下降。下面是一个标准主键写法CREATE TABLE t_user ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 自增主键, user_name VARCHAR(50) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;主键不仅仅是标识在InnoDB里它还决定数据在磁盘上的物理组织形式这一点直接关系到查询的IO路径。后面第3章会展开讲这里先记住一句话主键是InnoDB表中最重要的约束也是最容易被忽略的索引来源。2.2 唯一约束手机号与身份证的差别唯一约束保证一列或一组列的值在表中不重复但它和主键有几个关键差别一张表可以有多个唯一约束唯一约束允许NULL而且MySQL中的UNIQUE约束允许多个NULL值因为NULL在逻辑上表示“未知”未知不等于未知。业务里最常见的唯一约束是手机号、邮箱、身份证号。比如用户表可以允许手机号NULL但一旦有值就不能重复。建表时可以是列级约束也可以是表级约束CREATE TABLE t_user ( id BIGINT AUTO_INCREMENT, phone VARCHAR(20) NULL, email VARCHAR(100) NULL, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone), UNIQUE KEY uk_email (email) ) ENGINEInnoDB;这里有一个坑很多人以为加了UNIQUE就能防重复插入尤其是使用INSERT IGNORE或INSERT ... ON DUPLICATE KEY UPDATE时唯一约束正是触发“重复”判定的依据。如果唯一约束没建对应用层就算写了幂等插入逻辑还是会产生重复数据。所以凡是在业务上必须唯一的字段都必须在数据库层加唯一约束不要只靠代码。2.3 非空约束和默认约束把脏数据挡在外面非空约束就是字段必须要有值默认约束是当插入时不指定该字段自动填一个预设值。这两个约束经常配合使用尤其是“状态”“创建时间”“排序值”这类字段。为什么要强调这个“mysql设置默认值为0”是很多人会搜的热词场景多半是建表时没给status设置默认值插入数据时又没传status字段结果严格模式下直接报错Field status doesnt have a default value。解决办法有两条一是给字段补上DEFAULT 0二是修改数据库sql_mode但我强烈建议走第一条不要为了省约束去放宽数据库严格模式。CREATE TABLE t_order ( id BIGINT AUTO_INCREMENT, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2取消, remark VARCHAR(255) NOT NULL DEFAULT , PRIMARY KEY (id) ) ENGINEInnoDB;使用默认约束要注意三点BLOB、TEXT、JSON类型的字段不能直接指定默认值MySQL 8.0.13之后支持表达式默认值比如DEFAULT (NOW())但低版本不支持如果字段同时加了NOT NULL那么默认值就是它的保底方案否则插入时缺字段很容易报错。2.4 检查约束MySQL 8.0才真正生效的“年龄门禁”检查约束CHECK用来限制字段值的取值范围比如年龄必须在0到120之间性别只能是M或F。很多老开发会习惯性地跳过去因为MySQL 5.7及之前的版本里CHECK约束只是“语法上支持实际不生效”写了也不会拦数据。从MySQL 8.0.16开始CHECK约束才被真正强制执行。所以如果你还在用5.7不要指望它帮你拦数据老老实实靠应用层或者触发器。如果已经升级到8.0就可以愉快地用了CREATE TABLE t_user ( id BIGINT AUTO_INCREMENT, age TINYINT NOT NULL, gender ENUM(M,F) NOT NULL, PRIMARY KEY (id), CONSTRAINT chk_age CHECK (age 0 AND age 120), CONSTRAINT chk_gender CHECK (gender IN (M,F)) ) ENGINEInnoDB;CHECK约束有几个限制要记住表达式中不能使用子查询、存储函数、变量只能引用当前行的列。比如想写“密码不能等于用户名”这种跨列校验是可以的直接CHECK (password username)但想查另一张表是否符合条件就不行。所以它更适合做单行内的取值范围校验跨表校验仍得靠外键或应用层。2.5 外键约束一对多关系的“连坐”机制外键约束是引用完整性最直接的实现。它保证子表中的外键列的值要么是NULL要么必须存在于父表被引用的列中。比如订单表里的user_id如果指定了外键就不允许出现一个不存在的用户ID。外键的核心价值是防止“孤儿数据”。没有外键时你很容易在删除用户时忘记删订单或者订单表被人为插入了不存在的用户ID。基于外键MySQL还提供了几种删除策略CASCADE表示父表删除时子表一起删SET NULL表示父表删除时子表外键置空RESTRICT或NO ACTION表示有子记录时禁止删除父记录。CREATE TABLE t_user ( id BIGINT AUTO_INCREMENT PRIMARY KEY ) ENGINEInnoDB; CREATE TABLE t_order ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES t_user (id) ON DELETE CASCADE ) ENGINEInnoDB;但要提醒的是互联网高并发场景下很多团队刻意不用外键因为外键会让每次插入、更新、删除都额外检查父表并且在高并发锁竞争中容易成为瓶颈。我的建议是核心账务、人事系统等强一致性系统外键可以放心用但分库分表、复杂拆分的业务系统尽量不要用外键否则跨库检查根本做不了反而把数据库拖垮。2.6 自增约束别在高并发表上直接用它AUTO_INCREMENT并不是严格意义上的约束但它和主键绑定得如此紧密以至于提到主键就绕不开它。自增列在InnoDB里的行为是每次分配一个比当前最大值更大的值但它不保证连续。事务回滚、删除记录都会导致自增值“空跳”网上搜“mysql排序”时看到自增不连续其实是正常现象。自增列真正的坑在高并发插入。MySQL 5.7默认的innodb_autoinc_lock_mode1对批量插入还会用特殊的锁单行插入性能还好但在秒杀、下单这种高吞吐场景所有插入都要抢占自增锁容易互相等待。升级到MySQL 8.0后锁策略有优化但分布式环境下我更建议直接用雪花ID或号段方案避免依赖数据库单调自增也方便后续分库分表。CREATE TABLE t_order ( id BIGINT NOT NULL COMMENT 雪花ID, order_no VARCHAR(32) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB;3. 约束与索引的纠缠当“io约束”遇上辅助索引3.1 主键约束如何决定聚簇索引InnoDB表本质上是一棵聚簇索引B树数据行就存在主键索引的叶子节点上。主键约束不仅约束了数据的唯一性还决定了整张表的物理存储结构。所谓“io约束”本质上是主键选择对磁盘IO路径的影响如果主键是自增整数新数据基本是顺序写入页分裂少插入快如果主键是随机UUID每次插入都要在B树中间找位置触发大量页分裂和随机写IO负载明显上升。所以说主键约束不只是“唯一非空”那么简单它同时承担了“数据如何摆放”的重任。这也是为什么我一直强调InnoDB表必须显式主键不要依赖隐藏主键因为隐藏的rowid对查询没有任何帮助一旦发生全表扫描IO就是灾难。3.2 唯一约束对辅助索引的隐性影响所有唯一约束在MySQL里都会自动创建一个唯一索引而普通索引是非唯一索引。辅助索引的叶子节点保存的是主键值而不是数据行地址所以查询时先通过辅助索引找到主键再回到聚簇索引取数据这个过程叫回表。“辅助索引如何避免回表”是一个高频问题实际上与约束密切相关。比如你在status字段上建了一个唯一约束那么你的唯一索引就覆盖了status和主键两个字段。当查询只关心status和id时可以直接从辅助索引中拿到结果完全不用回表但如果你还想查order_no字段就少不了回表。结合唯一约束来规划索引是种技巧唯一约束本身已经创建了索引就不要再去重复建一个普通索引。很多人既加了UNIQUE KEY又单独ADD INDEX在同一个字段上纯属浪费空间和写入成本。辅助索引不是越多越好每个索引都意味着INSERT、UPDATE时的额外维护。设计表时要把“约束的索引红利”利用起来能用唯一约束满足查询覆盖的就不要再多建一个普通索引。3.3 约束数量与写入性能的真实关系约束和性能不是反义词但每个约束都附带了额外成本。主键要维护聚簇索引唯一约束要维护唯一索引并做冲突检查外键要检查父表记录非空和默认几乎无成本CHECK约束在写入时做表达式判断。你可以把它理解成安检一个人过安检很慢但能保证安全如果全大楼每个出入口都设置层层安检那通行效率必然下降。我曾经优化过一张线上表原本有7个唯一约束每次写入要检查7个索引高峰期插入延迟居高不下。后来逐一梳理发现其中两个字段其实是低基数列业务上也允许少量重复就改成普通索引写入延迟降了三分之二。约束要按真实业务需求来凡是保证数据唯一性的字段该加还得加但没必要把每一列都做成UNIQUE或CHECK牺牲性能换取不会发生的“理论正确”是不划算的。4. 完整实操从零构建用户表和订单表4.1 需求分析与约束清单现在我们做一个实际案例一个小型电商系统的用户表和订单表。需求如下用户必须有手机号非空且唯一邮箱可选但也不能重复年龄限制在0-120岁状态默认有效。订单必须有用户ID而且订单号必须唯一订单金额不能为负数下单时间默认当前时间。这个需求对应的约束清单很清晰用户表的主键是id手机号加唯一约束邮箱加唯一约束但允许NULL年龄加CHECK约束状态加非空默认。订单表的主键是id订单号加唯一约束user_id加外键策略选择CASCADE金额加CHECK约束下单时间加默认当前时间。4.2 建表SQL与每个约束的落地注释下面是完整的建表SQL我建议你在自己的本地MySQL 8.0环境里跑一遍注意外键部分必须在InnoDB下才能生效CREATE TABLE t_user ( id BIGINT AUTO_INCREMENT COMMENT 用户ID, phone VARCHAR(20) NOT NULL COMMENT 手机号, email VARCHAR(100) NULL COMMENT 邮箱, age TINYINT NOT NULL COMMENT 年龄, status TINYINT NOT NULL DEFAULT 1 COMMENT 1有效 0禁用, PRIMARY KEY (id), UNIQUE KEY uk_user_phone (phone), UNIQUE KEY uk_user_email (email), CONSTRAINT chk_user_age CHECK (age 0 AND age 120) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE t_order ( id BIGINT AUTO_INCREMENT COMMENT 订单ID, order_no VARCHAR(32) NOT NULL COMMENT 订单号, user_id BIGINT NOT NULL COMMENT 下单用户ID, amount DECIMAL(10,2) NOT NULL COMMENT 订单金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2取消, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 下单时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES t_user (id) ON DELETE CASCADE, CONSTRAINT chk_order_amount CHECK (amount 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;关于这条SQL有几个细节值得琢磨。phone字段非空且唯一这是最标准的手机号约束模式邮箱允许NULL唯一索引在MySQL中会放过多个NULL这符合业务预期。外键ON DELETE CASCADE用在这里表示删除用户时订单一起删但实际严肃业务里更推荐逻辑删除物理删除配合CASCADE要慎重。amount用DECIMAL而不是FLOAT避免浮点误差CHECK约束保证金额不为负数。4.3 用Navicat图形界面添加唯一约束很多同学不习惯写SQL喜欢用Navicat操作。以添加唯一约束为例步骤其实简单但我见过不少人点错地方。打开表设计界面后先选“索引”页签点击“添加索引”填写索引名在字段列表里勾选一个字段最后在“索引类型”下拉框里选择“Unique”保存即可。Navicat的“设计表”对话框里字段行上方有一个“唯一”勾选项有些版本可以直接勾选它的底层就是给该字段创建唯一索引。外键约束则在外键页签里添加选择字段名、被引用数据库、被引用表、被引用字段再设置删除时、更新时的策略。图形界面是把双刃剑操作前一定要看清楚索引类型是Normal还是Unique否则你以为是去重约束结果只是普通索引数据重复了都不知道。4.4 后续修改约束的正确姿势表已经建好了线上发现漏加约束怎么办用ALTER TABLE修改。添加唯一约束ALTER TABLE t_user ADD UNIQUE KEY uk_user_email (email);向外键添加约束ALTER TABLE t_order ADD CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES t_user (id);删除约束时要分清类型主键用DROP PRIMARY KEY唯一索引和普通索引用DROP INDEX外键用DROP FOREIGN KEY。这里最容易写错的是外键删除方式很多人对外键执行DROP INDEX结果报错因为外键索引需要先DROP FOREIGN KEY再DROP INDEX。大表上添加约束要特别小心加唯一索引会扫描全表并锁表。我的习惯是在低峰期执行或者用一个新表导入校验后再改避免直接把线上业务卡死。5. 建表异常与常用排查思路速查5.1 Duplicate entry重复值撞上唯一约束这是加唯一约束时最常见的报错错误信息通常长这样Duplicate entry 13800138000 for key t_user.uk_user_phone。这说明表里已经存在手机号13800138000而你想要把它设为唯一数据库不允许。解决办法不是直接删掉重复记录而是先排查重复数据的范围。用一条分组查询找出所有重复项SELECT phone, COUNT(*) FROM t_user GROUP BY phone HAVING COUNT(*) 1;确认重复后决定保留哪条、合并哪条再删除或更新多余数据最后重新加唯一约束。整个过程必须在事务里做提前备份。千万不要为了让约束加上而去修改sql_mode或禁用唯一检查那是掩耳盗铃。5.2 Field doesnt have a default value严格模式惹的祸看到这个报错很多人第一反应是“MySQL坏了”其实是被严格模式挡住了。默认的sql_mode包含STRICT_TRANS_TABLES如果插入时没给NOT NULL且没有默认值的字段提供值MySQL直接拒绝执行而不是像过去那样插入一个隐式的0或空字符串。“mysql设置默认值为0”这个需求本质就是给状态字段加DEFAULT 0。正确SQL是ALTER TABLE t_order ALTER COLUMN status SET DEFAULT 0;然后原语句就能插入了。记住临时修改sql_mode去掉严格模式可以绕过报错但会让数据质量失控线上千万别这么干。正确思路永远是审视表结构把该给的默认值补上把该非空的字段设为NOT NULL。5.3 外键创建失败的5个常见原因外键约束看起来简单实际创建时踩坑率极高。我把最常见的原因列成速查表原因排查要点字段类型不一致父表和子表的外键字段必须类型完全相同BIGINT不能配INT字符集或排序规则不同两张表的字符集必须一致比如都是utf8mb4被引用的列不是索引父表被引用列必须有主键或唯一索引约束名重复同一个数据库里外键名不能重复换个名字即可存储引擎不是InnoDBMyISAM不支持外键检查两张表的ENGINE遇到外键创建失败按表里列出的顺序逐项检查基本都能解决。特别提醒字符集问题很多人建表时默认字符集结果一张表是utf8mb4另一张是utf8mb4_general_ci也会报错需要统一。5.4 约束设计的最佳实践清单结合我自己的踩坑经验整理一份可以直接抄的约束设计清单每张InnoDB业务表都要有主键建议自增或雪花ID业务上唯一的字段必须加UNIQUE约束比如手机号、订单号所有字段先想好NULL还是NOT NULL是NULL就要考虑默认值CHECK约束只用于单行内的范围校验跨表校验交给外键或应用层外键在强一致性业务中用高并发分库分表场景则尽量不用约束不是越多越好低基数列要谨慎加唯一约束。这份清单是我在多个项目里反复调整后的结果。核心原则是约束要精准命中业务规则而不是把数据库变成一张处处受限的蜘蛛网。该有的约束必须到位否则脏数据迟早回来教育你不该有的约束放出去只会拖慢写入速度让运维同学半夜爬起来处理锁等待。我在实际项目里最有感触的一个案例是早期做订单系统时为了性能砍掉了订单表和用户表的外键结果上线第三周就出现了孤儿订单用户ID在用户表里根本不存在。后来花了一整天才写出清洗脚本把历史脏数据挽回然后又老老实实补了应用层校验。那次之后我就明白约束和性能的取舍不能拍脑袋你要先想清楚业务到底允不允许脏数据。如果业务强制要求完整性省掉的约束早晚会以更贵的方式还回来。