你有没有想过一个注册表里同一手机号能出现三条记录商品价格能填到800万年龄栏里躺着–1000订单金额那一列还写着一串英文别觉得夸张这些都是我在真实项目里见过的数据。问题不在写SQL的人手抖而是当初建表的时候压根没给这张表立规矩。MySQL里的表约束干的就是这件事。它把“用户名必须唯一”“年龄不能是负数”“订单位不能为空的用户下单”这些规则直接写进表结构任何一条INSERT、UPDATE进了数据库都得先过这道关卡不合法就直接报错。这比在业务代码里一个个if判断靠谱得多——后端校验可以漏写、可以被绕过、可以因为换人维护而慢慢消失但表结构里的约束只要表还在规则就一直在。这篇文我会把MySQL的六大约束全部拆开讲主键、非空、唯一、默认值、检查、外键每个都有建表语句、底层原理、实际项目里的取舍和踩坑记录最后还会给出一份可以直接抄走的用户-订单建表模板把常见报错也整理成排查清单。适合刚学MySQL的新人系统补一遍基础也适合写了好几年业务SQL但从没认真梳理过约束逻辑的同学对照检查自己的建表习惯。1. 约束不是“限制”而是数据的“保命底线”1.1 没有约束的表会变成什么样我见过最夸张的一张订单表里面订单金额字段是VARCHAR类型理由据说是“当时不确定要不要存小数点”。结果就是报表统计的时候一堆“abc”“12.3.4”这种值混在里面业务代码里到处是try-catch去兼容脏数据SQL加个SUM都提心吊胆。这类问题的根源是建表的时候把规则全部押在应用层数据库只负责存不负责判断。但应用层是有漏洞的新来的同事不知道业务规则、接口漏写了校验、历史代码里藏着没覆盖的路径只要有一条脏数据进去后面所有依赖这张表的逻辑全得跟着遭殃。而且脏数据一旦进了生产库你想清理面对的可能是几百万行存量数据牵一发动全身。表约束解决的就是这个问题。它是数据进入数据库之前最后一道物理闸门不满足规则的数据直接拒收连入库的机会都没有。1.2 约束的本质声明式规则约束最核心的思想是“声明式”的——你在建表的时候把规则用建表语法“告诉”数据库数据库自己负责执行。你不需要在每条INSERT语句前面写一堆判断逻辑更不需要在多个后端服务里重复维护同一套校验规则。打个比方。车库入口设一道栏杆车进来之前先识别车牌不认识的直接拦住。约束就是这道栏杆而应用层校验相当于小区门口贴了一告示“请业主自行确认车牌”。告示靠人自觉栏杆靠机械强制。哪个更靠谱不用我说。而且约束的校验发生在数据库引擎内部流程短、稳定、没有中间层。只要你这一条SQL触发了规则数据库引擎会立即返回明确的错误码比如Duplicate entry、Column cannot be null、Cannot add foreign key constraint排错路径非常清晰。1.3 MySQL六种约束全景速览先给张表把六种约束整体列出来后面每一节再展开细说约束类型关键字解决的问题是否生成索引典型用途主键约束PRIMARY KEY每行数据的唯一身份标识是聚簇索引每张表必备定位唯一一行非空约束NOT NULL字段不能存NULL否业务上必须有的字段如手机号、金额唯一约束UNIQUE KEY字段值不能重复是业务上不能重复但非主键的字段如用户昵称默认值约束DEFAULT不传值时给一个兜底值否状态、时间等字段的自动填充检查约束CHECK字段值必须满足一个表达式否年龄范围、状态枚举、金额非负外键约束FOREIGN KEY子表引用父表合法记录是在子表自动建索引订单关联用户、明细关联订单两张容易混淆的底层事实先记住主键约束本质是“非空”加“唯一”的组合外键约束只在InnoDB引擎下真正生效MyISAM引擎建了也会被静默忽略。2. 六大约束逐个拆解语法、原理、易踩的坑2.1 主键约束每行数据的“身份证号”主键是每张表的核心它解决的问题是“在一堆数据里怎么唯一确定一行”。MySQL对主键的要求是三个字非空、唯一、且每张表只能有一个主键。先说字段类型选择。我强烈建议用无业务含义的代理主键也就是自增整数。原因很简单业务字段不适合当主键。比如用手机号当主键看起来很妙但手机号可能换号、可能被注销后二次放号、可能包含特殊格式一旦业务规则变化你要改的就是整张表的结构。而一个自增的id从出生到表删除都不变稳定可靠。建表写法如下CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户ID代理主键, phone VARCHAR(20) NOT NULL COMMENT 手机号登录账号, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;这里有个细节值得说INT加UNSIGNED是我个人的偏好。INT有符号范围是-21亿到21亿如果你确定id不会为负数UNSIGNED能把上限翻倍到42亿。订单表这种增长快的表更建议直接用BIGINT UNSIGNED别等id撞了天花板再说。自增主键还有一个坑AUTO_INCREMENT列在MySQL里必须是某个索引的列。如果你手滑写成一个没有索引的普通列建表会直接报错。这是不少新手第一次碰到的报错之一。复合主键也是存在的两个或多个字段共同组成主键。比如选课表student_id, course_id联合主键表示一个学生只能选一次同一门课。但复合主键用起来要谨慎建议只在业务语义确实如此时才使用否则还是加一个自增id更省心。2.2 非空约束与空值哲学NOT NULL看起来最简单但很多初学者对NULL的理解是错的。NULL不是空字符串也不是数字0。空字符串是“有值的空”0是“有值的0”NULL是“没有值”——它表示这个字段根本不存在数据。这个区别在SQL里直接导致一个现象NULL和任何值比较结果都是UNKNOWN不是TRUE也不是FALSE。举个例子。SELECT * FROM user WHERE age NULL是查不出数据的正确写法是WHERE age IS NULL。这一条不知道绊倒过多少人。所以在业务设计上我有个习惯能用NOT NULL DEFAULT表达的字段尽量不用NULL。状态字段写成status TINYINT NOT NULL DEFAULT 1比写成status TINYINT DEFAULT NULL好太多——前者你写WHERE条件只需要status 1后者得处理status IS NULL这种分支而且NULL在索引里的效率通常也没那么理想。对于本身可能没有值的字段比如邮箱也推荐用DEFAULT 而非NULL查询时少一个IS NULL判断客户端序列化JSON也不容易闹出字段缺失的问题。2.3 唯一约束防重复的最后一道闸门唯一约束和主键很像都是不允许重复但有两个区别唯一约束的列允许NULL一张表可以有多个唯一约束主键只能有一个。它们之间还有个很容易被忽略的坑唯一约束下的NULL可以被重复多次。也就是说你在email列上建了唯一约束表里可以同时存在多条email NULL的记录。因为NULL表示“没有值”数据库认为两个NULL并不相等。你要是把唯一约束当成“绝对不重复”来用必须注意这一条。联合唯一约束则是业务上防止重复记录的利器。比如用户收藏商品表你不想让同一个用户重复收藏同一个商品建表时写CREATE TABLE user_favorite ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, goods_id INT UNSIGNED NOT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_user_goods (user_id, goods_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户收藏表;这样不管应用层判断多漏数据库层面直接杜绝同一用户重复收藏同一商品。另外要记住唯一约束会自动创建一个索引。这意味着它既能防止重复也能加速基于该字段的查询。你建唯一约束的时候等于顺手把索引也建了一箭双雕。2.4 默认值约束不让字段裸奔DEFAULT的作用是INSERT时不传这个字段数据库自动填入一个值。最常见的使用场景就是时间字段create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间这样连后端都不用手动拼时间插入数据的时候MySQL自动带上当前时间。但DEFAULT有个版本坑必须提醒MySQL 8.0.13之前DEFAULT只支持常量不支持表达式。比如你想用默认值填当前时间早期只能用DEFAULT CURRENT_TIMESTAMP这种特定写法不能写DEFAULT NOW()更不能用自定义函数。8.0.13以后才放宽到支持表达式默认值。如果你的项目还在MySQL 5.7上别急着写复杂表达式先在目标环境验证一下。另一个细节DEFAULT和NULL搭配要小心。DATETIME DEFAULT NULL和DATETIME DEFAULT CURRENT_TIMESTAMP完全不同前者是“不填就存NULL”后者是“不填就存当前时间”。很多新手造表时顺手复制前者结果发现查时间字段全是NULL后悔都来不及。2.5 检查约束从源头拒绝非法值CHECK约束是我个人觉得最被低估的一种约束。它允许你写一个逻辑表达式数据库在写入之前检查这个表达式是否为真不为真就拒绝。最典型的例子就是年龄范围CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, age TINYINT UNSIGNED DEFAULT NULL, PRIMARY KEY (id), CONSTRAINT chk_age CHECK (age 0 AND age 120) ) ENGINEInnoDB;这里CONSTRAINT chk_age是给约束起的名字可加可不加但我强烈建议加名。这样后续报错的时候MySQL会直接告诉你约束名是chk_age一眼定位谁拦的不带名排查起来全靠猜。状态字段同样适合用CHECK约束。如果你只允许状态取0、1、2三个值CONSTRAINT chk_status CHECK (status IN (0, 1, 2))但最大的坑就在版本MySQL 8.0.16之前CHECK约束虽然在建表语法里可以写但引擎层并不强制执行等于形同虚设。5.7上很多人加了CHECK测试发现非法数据照样能插入就是这个原因。要在8.0.16以上版本才真正生效。如果你还在5.7别指望这个约束兜底老老实实用枚举字段加应用层校验。2.6 外键约束表和表之间的“血缘关系”外键是六个约束里唯一涉及多张表的。它表达的是父子关系子表里的一个字段必须是父表某字段里已存在的值。比如订单表的user_id必须是用户表里真实存在的id不能在用户不存在的情况下硬插一个订单。外键删除行为有四种选项建表时必须选明白行为含义适用场景RESTRICT父表有子记录引用时禁止删除或更新默认最安全防止误删CASCADE删除或更新父记录时子表记录同步删除或更新明细随主表删除如订单明细SET NULL父记录删除时子表外键字段置为NULL要求该字段可空如历史记录保留但解除关联NO ACTION行为类似RESTRICT延迟检查时二者区别才显现大多数情况与RESTRICT等价一个实际项目里最常见的组合是订单表的user_id外键更新用户id时级联更新订单里的user_id但删除用户时禁止RESTRICT防止删了用户留下孤儿订单CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES user (id) ON UPDATE CASCADE ON DELETE RESTRICT这里再补充一个外键的硬性前提四个条件必须同时满足否则建表或ALTER会失败两张表的存储引擎都必须是InnoDB外键列和引用列的类型必须完全一致VARCHAR(20)对VARCHAR(20)不能一个utf8一个utf8mb4引用列必须是索引列父表主键天然满足外键列本身不能是另一张表的被引用列形成循环依赖还要说一个行业争议大厂高并发场景通常主动放弃外键把关系校验放到应用层因为外键会让每次写入多一次父表检查锁粒度也可能放大。但对中小项目、内部系统、管理后台来说外键带来的数据完整性和排错便利性远大于那点性能损失。我的态度一直是能用外键就用外键别为了“未来可能高并发”提前阉割正确性。3. 一套可复制的建表模板与设计思路3.1 从零设计用户表、商品表与订单表理论说再多不如直接看一张完整表。下面这套是我在实际项目里经常用来教新人的模板覆盖了主键、唯一、非空、默认、检查、外键全套约束可以直接抄去改造。先建用户表CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户ID, phone VARCHAR(20) NOT NULL COMMENT 手机号登录账号, email VARCHAR(100) NOT NULL DEFAULT COMMENT 邮箱空串表达未填写, status TINYINT NOT NULL DEFAULT 1 COMMENT 账号状态1正常 0禁用, age TINYINT UNSIGNED DEFAULT NULL COMMENT 年龄允许未知, 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_phone (phone), CONSTRAINT chk_status CHECK (status IN (0, 1)), CONSTRAINT chk_age CHECK (age 0 AND age 120) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT用户表;再建商品表CREATE TABLE goods ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 商品ID, goods_name VARCHAR(100) NOT NULL COMMENT 商品名称, price DECIMAL(10,2) NOT NULL COMMENT 售价单位元, stock INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 库存, status TINYINT NOT NULL DEFAULT 1 COMMENT 上下架状态1上架 0下架, PRIMARY KEY (id), CONSTRAINT chk_goods_price CHECK (price 0), CONSTRAINT chk_goods_status CHECK (status IN (0, 1)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品表;最后建订单表通过外键把它和用户、商品关联起来CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 订单ID, order_no VARCHAR(32) NOT NULL COMMENT 订单编号, user_id INT UNSIGNED NOT NULL COMMENT 下单用户ID, goods_id INT UNSIGNED NOT NULL COMMENT 商品ID, quantity SMALLINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 购买数量, total_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_orders_user FOREIGN KEY (user_id) REFERENCES user (id) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_orders_goods FOREIGN KEY (goods_id) REFERENCES goods (id) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT chk_orders_quantity CHECK (quantity 0), CONSTRAINT chk_orders_amount CHECK (total_amount 0), CONSTRAINT chk_orders_status CHECK (status IN (0, 1, 2)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;看这套模板有几个设计决策值得展开说。金额字段用的是DECIMAL(10,2)不是FLOAT也不是DOUBLE。原因很直白浮点数在二进制里无法精确表示十进制小数0.1 0.2这种经典问题在数据库里同样存在。钱这种东西差一分钱都算事故必须用定点数。总金额字段在订单表里是冗余的按理说可以由商品单价乘数量算出。但订单一旦生成商品价格可能后续会调整如果一切靠实时计算历史订单金额就跟着变了。存一份冗余相当于给订单“拍了张快照”这是业务上常见的取舍而外键只保证关联存在不保证价格不被修改所以快照必须靠字段固化下来。id和order_no同时存在订单表里加了唯一约束uk_order_no。有的新手会问既然有自增id做主键为什么还要订单编号因为自增id在外部展示时容易暴露业务量和规律而且多库多表时自增id会冲突业务编号才是真正对外的标识。这里唯一约束就是用来确保这个对外编号不重复的。3.2 联合唯一约束的使用细节前文收藏表已经写过联合唯一这里再补一个细节。联合唯一约束中字段的顺序会影响索引的使用效果。比如UNIQUE KEY uk_user_goods (user_id, goods_id)生成的索引对WHERE user_id ?查询很友好但对WHERE goods_id ?就没那么高效。如果你的业务里两边查询都会高频出现可以考虑把联合索引方向和单列查询的频率对齐或者额外再加一个普通索引。这个问题本质是“左前缀原则”——联合索引只能从最左列开始连续匹配。所以建联合唯一时不要只看业务重复性还要顺便想想查询模式。另一个概念要分清联合唯一是“组合起来不重复”不是“每个字段单独不重复”。相同的user_id可以出现很多次相同的goods_id也可以出现很多次但(user_id, goods_id)这个组合在表里只能出现一次。这是业务语义的关键和单列唯一约束完全是两码事。3.3 约束与索引的关系加约束也是加索引主键、唯一、外键都会自动创建索引而默认值、非空、检查不会。这条规则直接决定你的表结构和索引规划。我见过一种典型错误建表时为了防重复给某个字段加了UNIQUE又手动给同一个字段加了一个普通索引白白浪费一份写放大。记住唯一约束已经自带索引了不需要再重复建。外键那边有个MySQL的自动行为如果外键列上还没有索引InnoDB会自动为它创建一个。但你最好不要完全依赖这个隐式行为而是自己在建表时明确安排好外键列的索引。一是自动创建的索引名字不够直观后续维护不方便二是如果你需要的是复合索引而非单列索引自动创建的单列索引可能并不满足查询需求。还有一点要提醒每个索引在提高查询速度的同时都在拖慢写入速度因为每次INSERT和UPDATE都要同步维护索引。表约束越多、索引越多写入就越慢。过度约束和零约束同样危险。我通常的反问是这条规则如果漏掉一条脏数据业务影响有多大如果影响很大就值得用约束兜底如果无关痛痒就别让一堆索引拖垮写入性能。4. 常见问题与排查技巧实录4.1 ERROR 1062Duplicate entry这个报错几乎是MySQL新手第一道坎。报错文本通常长这样ERROR 1062 (23000): Duplicate entry 13800138000 for key user.uk_phone翻译过来就是唯一约束uk_phone不允许手机号13800138000出现第二次。触发它的INSERT或者UPDATE被拒绝了。排查思路就三步。第一步把报错信息里的值和键名读出来明确是哪个约束第二步查一下这条记录是否真的已存在可以用SELECT id, phone FROM user WHERE phone 13800138000确认第三步如果确认是业务本身需要重复那就说明约束加错了应该改成联合唯一或者干脆去掉如果业务确实不允许重复那说明程序在插入前没做去重判断需要在应用层加查询或者捕捉这次主键/唯一冲突并给出友好提示。这里也有个常见误区Duplicate entry不一定是并发下两条一样的数据同时写入很多情况是历史脏数据已经存在你后来才加了唯一约束加的时候就会因为存量重复数据直接报错。解决办法是先把存量数据去重再加约束。4.2 ERROR 1215Cannot add foreign key constraint外键添加失败的报错原因通常是一个隐藏条件没满足。别慌按这个顺序排查两张表存储引擎是不是都是InnoDBMyISAM直接不支持字符集和排序规则是否一致一张utf8、一张utf8mb4或者COLLATE不一致都会报错字段类型是否完全匹配INT对INTBIGINT对BIGINTVARCHAR长度也要一致父表被引用列是否建立了索引主键天然满足其他字段要先建唯一约束或普通索引子表里是否已经存在父表中不存在的值存量脏数据会卡住外键创建最后一条是最容易忽略的。比如订单表里已经有一条user_id999的记录而用户表里根本没有999号用户这时候加外键必定失败。清洗掉这些孤儿数据之后再ALTER TABLE ADD CONSTRAINT就能过。ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES user (id);日常开发中不要在生产库直接跑这种ALTER。先在测试环境把条件和存量数据都验证一遍再动手。4.3 ERROR 1048Column cannot be nullERROR 1048 (23000): Column phone cannot be null这个报错的意思是你在NOT NULL字段上插入或更新成了NULL。常见来源有三个后端程序漏传了字段ORM把前端空字符串转换成了null某个接口的旧逻辑没适配新的非空字段。排查技巧是先看SQL语句和参数值而不是直接怀疑数据库。用SHOW CREATE TABLE user\G确认表的非空约束再用日志把插入参数打印出来。我见过最隐蔽的一种情况是代码里本来传的空字符串某个版本升级后ORM自动给它转成了null数据库瞬间报一堆1048。这类问题靠数据库约束反而成了好事——没有约束拦着空字符串和NULL混着入库后续查数据更头疼。4.4 CHECK约束没生效的版本坑如果你在MySQL 5.7或者8.0.16之前的环境建了CHECK约束测试时发现非法值照样能插入别怀疑自己写错了语法。这个版本范围的InnoDB会解析CHECK子句但不强制执行等于你把规则写在了墙上但没人当保安。解决方案分两种一种是升级到8.0.16以上版本这是根本解法另一种是如果暂时不能升级就把校验逻辑放回应用层同时用枚举字段加NOT NULL DEFAULT的组合来尽量缩小脏数据范围。顺带一提即使是在8.0.16以上CHECK约束也只对写入生效不会影响存量数据。你给一个已有脏数据的表加CHECK加的时候可能成功取决于版本和配置但下次修改这些脏数据时就会触发报错所以加之前先清理存量是正道。4.5 大表加约束与迁移数据的实用方法给已有数据的表增加约束远比在新建空表时加约束麻烦。因为数据库必须校验存量数据是否全部满足新规则几百万行数据直接跑ALTER可能会长时间锁表影响线上业务。我整理了几条实测下来的经验。第一条备份先行。任何结构变更前先把表备份或至少导出一份数据。别嫌麻烦生产库出事时没有后悔药。第二条先清洗后加约束。手工或者写脚本把违反规则的存量数据修复掉保证新约束能一次性加上。如果有外键要加先删除子表里的孤儿记录。第三条大表在线DDL要谨慎操作。MySQL 8.0的ALGORITHMINSTANT可以支持部分操作瞬间完成但加CHECK、加外键这类操作未必都能走INSTANT可能需要COPY表。在低峰期操作或者使用在线改表工具pt-online-schema-change这类降低锁表影响操作前先在测试环境用相同数据量压一遍评估耗时。第四条迁移数据时临时关闭外键检查可以用SET FOREIGN_KEY_CHECKS 0导入完成后再设回SET FOREIGN_KEY_CHECKS 1。这么做的前提是你已经确认数据满足所有外键关联否则问题只是被延后了。这个开关是我的常用手段但它只适合数据迁移场景日常业务运行期间不要动它。4.6 外键删数据报错与级联的威力父表数据被外键约束挡住删除报错形如ERROR 1451 (23000): Cannot delete or update a parent row意思是你想删的用户还有订单引用着不能直接删。这时候你得想清楚业务到底要怎么处理是级联删除所有订单还是禁止删除只允许软删加一个deleted标记还是把订单的user_id置为NULL表示“已注销用户”我的建议是用户、订单这种核心业务一律用软删除不要物理删。物理删除后关联数据要么跟着没、要么变成孤儿麻烦无穷。如果非要物理删那外键的ON DELETE行为就是一把双刃剑——CASCADE省事同时也危险你删一个用户顺手把他所有订单、订单明细全删了操作前想一想这个后果。5. 最后想说几句掏心窝的话折腾这么多年数据库我有一个很深的感触表结构设计阶段多花十分钟想约束比上线之后花两个小时洗数据划算太多。数据只有一份坏了很难补更别说有些脏数据一旦进入报表直接影响财务对账和业务决策那已经不是技术问题了。我建议你养成一个习惯每次建表的时候把这张表的每条字段逐个问一遍——“这个字段可以空着吗”“这个字段能重复吗”“这个值有范围限制吗”“它是不是另一张表的某个记录”。把这些问题在脑袋里过一遍约束自然就清晰了。写SQL之前先立规矩写出来的表才是可靠的表。最后再分享一个小技巧约束名一定要起好。CONSTRAINT fk_orders_user一眼就知道是订单表关联用户表CONSTRAINT chk_age一眼就知道是年龄范围检查。等哪天线上报错的时候光看报错信息里的约束名就能省下你半小时排查时间。希望这篇东西能帮你在MySQL的路上少踩几个坑。