做开发这几年我见过最多的数据事故不是SQL写错而是该有的约束没加。前几年帮一个合作团队排查订单问题商品状态的字段既能存“已支付”又能存“已付款”两个词意思一样业务上却当成两条数据后端对账对了一个月没查明白。问题出在哪建表时少写一行约束。所以MySQL基础篇里“约束”这一章绝对绕不过去它看起来简单用起来却遍地是坑。这篇博文我会把MySQL里六大约束的原理讲清楚把建表、改表、删约束、排查报错的完整路径走一遍新手照着敲就能复现老手也能查漏补缺。内容围绕“约束”这个核心展开默认你已经装好了MySQL能在命令行或Navicat里执行SQL准备好了直接往下看。1. 约束到底管什么先搞懂设计初衷1.1 约束的本质是什么约束的本质是数据库在写入数据时的一道“闸门”。你把规则定义好MySQL替你把关不符合规则的数据进不了表。这个思路和编程里前端表单校验很像用户名不能为空、邮箱不能重复、年龄不能为负数这些规则从前端到后端写一遍最后还得在数据库层再兜一遍底。为什么要兜底因为前面的校验都有可能被绕过。接口漏调、并发请求、历史数据迁移、手工SQL改数任何一环出了漏洞脏数据就悄然落地。但数据库层的约束是物理级别的规则只要约束在insert和update都绕不过它。MySQL的约束按功能可分成六类先看总览表格。约束类型关键字作用典型报错示例非空约束NOT NULL字段不允许为NULLColumn name cannot be null默认值约束DEFAULT不填值时自动填充指定值无自动填充唯一约束UNIQUE字段值不允许重复Duplicate entry xxx for key主键约束PRIMARY KEY唯一标识一条记录隐含非空唯一Duplicate entry / Column cannot be null自增约束AUTO_INCREMENT数值自动递增生成无自动生成外键约束FOREIGN KEY字表数据必须参考主表的合法值Cannot add or update a child row这六类约束我习惯再按“作用范围”拆一层列级约束和表级约束。直接写在字段定义后面的叫列级约束比如name VARCHAR(50) NOT NULL单独用CONSTRAINT关键字声明、写在字段定义下面的叫表级约束比如唯一约束和复合主键就经常要写成表级约束。后面实操部分我会具体演示这两种写法。1.2 为什么建表时多写一行比后期补更值我见过太多项目建表时图省事字段全部 nullable也不加唯一约束靠后端代码来保证数据正确。前期开发确实快但上线三个月后就开始还债。最常见的场景是业务中已经产生重复数据这时候再想去加唯一约束MySQL直接报错因为历史数据不满足约束规则你只能先清理数据再改表这个过程非常痛苦。遇到过最典型的一次用户表里同一个手机号出现了两条记录原因就是应用层先查后插的竞态问题两条线程同时查到“不存在”然后同时插入成功。如果一开始就在手机号字段上建唯一约束这个问题在数据库层根本不可能发生。所以我在做表结构设计时有一条硬性规矩能由数据库保证的规则绝不依赖应用层自觉。建表的时候多写一行约束后面能省下大量查数据和删脏数据的工时。这一节先把理念对齐下面每一类约束逐个过。2. 六大约束逐个拆解建表时的完整实操2.1 非空约束 NOT NULL最基础也最容易被忽略非空约束的含义很直白这个字段不允许存NULL。注意我这里说的是NULL不是空字符串。NULL表示“未知”“没有值”空字符串是“知道是空的内容”两者有本质区别。前端表单里用户没填手机号和填了一个空手机号落库后分别是NULL和语义完全不同。建表写法顺手就来CREATE TABLE user_info ( id INT PRIMARY KEY COMMENT 用户编号, phone VARCHAR(20) NOT NULL COMMENT 手机号, nickname VARCHAR(50) DEFAULT COMMENT 昵称 );nickname允许为空我用DEFAULT 兜底这是另一个约束下一节细说。phone不允许为空如果执行下面这种insert就会报错INSERT INTO user_info (id, phone) VALUES (1, NULL); -- Column phone cannot be null实际项目中我的经验是“尽量NOT NULL”。为什么有两个现实原因。第一NULL在索引、聚合、比较运算中都会产生特殊行为比如COUNT(phone)不会统计NULL值WHERE phone ! 123也不会匹配到NULL写查询的人一不留神就会漏数据。第二很多ORM框架里NULL字段映射到Java、Python对象时容易出现空指针异常用空字符串反而更安全。所以凡是业务上必然有值的字段一律加NOT NULL。2.2 默认值约束 DEFAULT别让空值满天飞默认值约束解决的是“用户没填时怎么办”的问题。我们看到热搜词里经常有人搜“mysql设置默认值为0”就是这类需求的典型。比如库存表里剩余数量没填时应该默认0状态字段没填时默认“待审核”。在建表时直接定义默认值CREATE TABLE inventory ( id INT PRIMARY KEY AUTO_INCREMENT, sku_name VARCHAR(100) NOT NULL, remaining INT NOT NULL DEFAULT 0 COMMENT 剩余库存默认0, status TINYINT NOT NULL DEFAULT 0 COMMENT 状态0待审核 1上架 2下架 );这一步执行后下面这条insert不需要填remaining和statusMySQL会自动填充0INSERT INTO inventory (sku_name) VALUES (经典款T恤); -- remaining 0status 0默认值约束还可以跟时间字段配合比如订单表创建时间默认当前时间create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间这里补充一个新手容易搞混的点DEFAULT约束只在“insert没有提供该字段值”时生效如果你显式写入NULL除非字段允许NULL否则依然会报非空错误。所以要让默认值真正生效字段一定要加NOT NULL两者组合使用才是完整的兜底方案。2.3 唯一约束 UNIQUE业务数据去重在数据库层搞定唯一约束保证一个字段或一组字段的值不重复最典型的场景是手机号、身份证号、学号、订单号。刚才提到的竞态问题就是靠唯一约束从根上解决的。建表写法有两种效果等价-- 方式一列级约束 CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, stu_no VARCHAR(20) UNIQUE COMMENT 学号 ); -- 方式二表级约束推荐便于命名 CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, stu_no VARCHAR(20) COMMENT 学号, CONSTRAINT uk_stu_no UNIQUE (stu_no) );方式二里的uk_stu_no是约束名自己起的建议命名规范统一一点比如uk_字段名。后期删除约束时这个名字有用后面第3部分会讲到。唯一约束有一个非常容易踩坑的特性它允许多个NULL存在。也就是说同一列上有两条记录的值为NULL时MySQL不会报重复错误。原因在于MySQL认为“未知值”是彼此不等的。这个问题业务上经常导致防重复失效所以如果你的业务要求“手机号要么填且唯一要么不填”那没问题但如果要求所有记录手机号都不允许为空且唯一记得同时加NOT NULL。唯一约束同样支持复合唯一比如一个订单里同一件商品不能重复出现用联合唯一CREATE TABLE order_item ( id INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL DEFAULT 1, CONSTRAINT uk_order_product UNIQUE (order_id, product_id) );复合唯一约束的规则是“组合值不允许重复”像order_id1、product_id2和order_id1、product_id3可以共存但两条order_id1、product_id2就会报错。这也侧面解释了为什么热搜词里总有“mysql创建索引”“mysql update语法”的搜索需求因为update一条记录时如果把它改成和其他记录重复的值MySQL同样会以约束违规为由拒绝执行UPDATE student SET stu_no 2024001 WHERE id 2; -- Duplicate entry 2024001 for key student.uk_stu_no2.4 主键约束 PRIMARY KEY每张表的定海神针主键约束可以理解为非空约束和唯一约束的叠加它要求字段值非空且唯一并且一张表只能有一个主键。主键存在的意义不只是约束更是InnoDB存储引擎组织数据的方式。InnoDB本身就是聚簇索引结构表里的数据按照主键顺序物理排列所以主键的选择直接决定写入性能和查询性能。建表时主键是这么写的CREATE TABLE class ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 班级编号, class_name VARCHAR(50) NOT NULL COMMENT 班级名称 );如果业务上确实没有单独的字段适合当主键也可以把多个字段组合成复合主键CREATE TABLE score ( student_id INT NOT NULL, course_id INT NOT NULL, score DECIMAL(5,2) NOT NULL, PRIMARY KEY (student_id, course_id) );这里student_id和course_id各自都可以重复但两个值的组合必须唯一。关于主键设计我踩过一个坑得拿出来说。很多初学者喜欢用业务字段当主键比如身份证号、手机号当时看起来没问题但业务规则一变就很麻烦。身份证号涉及隐私加密后长度变化、手机号可能换绑一旦主键值需要修改关联它的所有子表都要跟着改代价非常大。所以我个人强烈建议主键用一个无业务含义的自增id业务唯一性交给唯一约束去管。这个思路在MySQL社区有个经典说法叫“代理主键”搞明白它能少走很多弯路。主键字段因为自带唯一性还天然作为ORDER BY查询的快速通道很多模糊搜出来的“mysql排序”慢问题一查执行计划往往就是没走主键索引。2.5 自增约束 AUTO_INCREMENT高效生成编号自增约束一般不单独使用它总是配合主键出现作用是让MySQL自动生成递增的整数。这样insert时不用管主键值MySQL帮你按1、2、3的顺序排下去CREATE TABLE class ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 班级编号, class_name VARCHAR(50) NOT NULL ); INSERT INTO class (class_name) VALUES (Java班), (Python班), (Go班);执行完查询就是SELECT * FROM class; -- id: 1,2,3自增约束有几个细节容易忽略。第一一张表只能有一个自增列而且它必须被定义为索引最常见的就是主键。第二自增值不是一定连续的事务回滚、删除记录、唯一键冲突都会造成跳跃。比如某次insert因为唯一约束冲突失败这次“消耗掉”的自增值并不会回滚下一条记录从更大的值开始。这不是bug是设计理解这一点业务上就别纠结自增主键有空洞的问题。第三MySQL 8.0之后自增值持久化到了redo log里重启后不会重置早期版本遇到过自增值被重置导致主键冲突的惨案目前新版基本不用操心。如果你在开发测试中手动插入一个很大的自增值比如INSERT INTO class (id, class_name) VALUES (100, 测试班)那么接下来MySQL自动生成的下一个自增值会从101开始不会自动跳回原来最大值的下一个知道这个机制测试时就不会被“跳号”现象吓到。2.6 外键约束 FOREIGN KEY关联数据的强一致保障外键约束是六大约束里最复杂的一类它解决的是多表之间的数据一致性。比如学生表里的class_id应该必须指向班级表里真实存在的班级不能随便填一个不存在的编号。建表时外键定义如下CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, stu_no VARCHAR(20) NOT NULL UNIQUE, class_id INT NOT NULL, CONSTRAINT fk_student_class FOREIGN KEY (class_id) REFERENCES class(id) );这里class是父表被引用表student是子表。从表字段class_id的值必须在父表主键id中存在否则插入直接报错。外键约束还管更新和删除这就引出了级联策略的选择。删除/更新策略行为说明适用场景RESTRICT如果子表有引用父表禁止删除/更新默认行为防止误删NO ACTION同RESTRICT立刻检查并拒绝标准SQL默认更严格CASCADE父表删除/更新子表同步删除/更新父子数据生命周期一致SET NULL父表删除/更新后子表外键列置为NULL子表记录需要保留联动的定义和写在后面举例。RESTRICT是MySQL默认行为比如班级表里1班还有学生想直接删除1班就会报错。CASCADE适合“删班级同时删学生”的场景定义时加ON DELETE CASCADE。SET NULL则要求外键列允许NULL删除班级后学生记录保留但class_id变成NULL。外键约束的注意事项有必要单独列出来父表和子表的存储引擎必须都是InnoDBMyISAM不支持外键。外键列的数据类型必须和父表被引用列一致比如都是INT不能一个INT一个BIGINT。被引用的列必须是主键或者有唯一约束。子表外键列上最好有索引否则外键检查会扫描全表性能很差。实际上MySQL在创建外键时如果发现子表列上没有索引会自动帮你建一个。我在设计会员订单模型时很常用外键。比如父表member子表member_order的外键指向member_id这样订单永远不可能挂在不存在的人身上。这种强一致保证用应用层代码去实现反而容易漏。3. 表结构变更时的约束管理用得最多的场景3.1 给已有表添加约束的三种方式建表时忘了加约束后期想补这是工作中最常做的事。给已有表添加约束有三种常用方式分别适用不同场景。第一种用MODIFY COLUMN加非空和默认值约束。比如给已存在的name字段加NOT NULL再给status字段设置默认0ALTER TABLE student MODIFY COLUMN name VARCHAR(50) NOT NULL; ALTER TABLE inventory MODIFY COLUMN remaining INT NOT NULL DEFAULT 0;这种方式本质是重新定义整个列所以要把列的完整类型写出来。修改前先确认列里没有NULL数据否则会报错。第二种用ADD CONSTRAINT加唯一约束、主键约束、外键约束-- 添加唯一约束 ALTER TABLE user_info ADD CONSTRAINT uk_phone UNIQUE (phone); -- 添加复合唯一约束 ALTER TABLE order_item ADD CONSTRAINT uk_order_product UNIQUE (order_id, product_id); -- 添加外键约束 ALTER TABLE student ADD CONSTRAINT fk_student_class FOREIGN KEY (class_id) REFERENCES class(id); -- 添加主键约束 ALTER TABLE temp_table ADD PRIMARY KEY (id);这几种ADD操作执行前MySQL会先检查现有数据如果数据不满足约束操作直接被拒绝。要处理历史脏数据只能先清理或变更数据再执行添加。第三种用ALTER COLUMN直接设置或删除默认值。这个语法和MODIFY COLUMN容易混淆但方向不同-- 设置默认值 ALTER TABLE inventory ALTER COLUMN remaining SET DEFAULT 0; -- 删除默认值 ALTER TABLE inventory ALTER COLUMN remaining DROP DEFAULT;热搜词里总有人搜“mysql设置默认值为0”最常用的就是这一句。它与MODIFY COLUMN加DEFAULT的关键区别在于ALTER COLUMN只修改默认值属性不影响其他列属性MODIFY COLUMN则会完整重置列定义操作不当容易丢掉原有的注释、类型细节。3.2 删除约束的语法与常见禁忌删除约束对应的SQL写在下面注意不同约束删除方式差别很大-- 删除非空约束重新定义列为允许NULL ALTER TABLE student MODIFY COLUMN name VARCHAR(50) NULL; -- 删除默认值约束 ALTER TABLE inventory ALTER COLUMN remaining DROP DEFAULT; -- 删除唯一约束用约束名实际就是删除对应索引 ALTER TABLE user_info DROP INDEX uk_phone; -- 删除主键约束 ALTER TABLE student DROP PRIMARY KEY; -- 删除外键约束用约束名 ALTER TABLE student DROP FOREIGN KEY fk_student_class;这里有几个坑必须记住。第一删除非空约束不是用DROP NOT NULLMySQL没有这种语法必须靠MODIFY重新定义列。第二唯一约束和普通索引一样存储层面就是一个唯一索引所以删除唯一约束用的是DROP INDEX名字就是建约束时起的约束名。如果你建约束时没起名字MySQL默认会以被约束字段名作为索引名删除前先查一下。第三如果一张表的主键列是自增的删主键之前需要先把自增属性去掉。直接执行ALTER TABLE student DROP PRIMARY KEY;会报错。正确做法是先MODIFY列定义去掉AUTO_INCREMENT再删除主键。第四删除外键约束只去掉关联规则不会删除子表外键列上MySQL自动创建的索引。如果你希望连索引一起清理需要再单独执行DROP INDEX但这个索引可能被查询在用删之前要谨慎评估。给已有表加约束还有一个大表环境下的现实问题ALTER TABLE在大表上执行会锁表阻塞线上读写。我的经验是不管加什么约束先看数据量几百行随便改几百万行的表操作前要选业务低峰期必要时用在线DDL工具。MySQL 5.6以后支持ALGORITHMINPLACE, LOCKNONE的语法比如ALTER TABLE student ADD CONSTRAINT uk_stu_no UNIQUE (stu_no), ALGORITHMINPLACE, LOCKNONE;这样可以让DDL不阻塞业务读写但具体支不支持跟MySQL版本、操作类型有关执行前可以用EXPLAIN ALTER TABLE查看操作风险小很多。3.3 约束和索引的关系别把两件事搞混不少新手会把约束和索引当成一个东西其实它们有关系但不能画等号。索引是提升查询速度的数据结构它本身不保证数据唯一性约束是数据完整性规则它定义“能不能写入”。但MySQL的实现里两者经常绑定唯一约束会自动创建唯一索引主键约束自动创建聚簇索引外键约束要求被引用列有唯一索引并且子表列有索引。反过来说给一个列建普通索引不产生任何约束重复数据照样能进。所以热搜里总有“mysql创建索引”的需求如果你想让某一列不能重复要建的是唯一约束不是普通索引。说说我的理解唯一约束本质上是“利用索引去查重”MySQL执行insert或update时会借道唯一索引快速判断新值是否和旧值冲突。这也是为什么约束检查能保持高性能巨大数据量下也不会拖垮写入。这个关系还衍生出一个实践问题如果一个字段既要加唯一约束又要被频繁查询排序该怎么办答案很简单唯一约束本身建的索引就能用于查询排序不需要重复建普通索引。多个索引并存不仅占空间还会拖慢写入速度。我见过有人为了“保险”在一个字段上又建唯一约束又建普通索引完全多余白白浪费性能。4. 常见问题与排查技巧实录4.1 报错速查表从错误码直接定位问题MySQL的约束报错会给出错误码和消息我整理了一份高频速查表遇到报错对着查基本能秒定位。错误码典型消息触发场景解决方法1048Column xxx cannot be null违反非空约束补值或改列允许NULL1062Duplicate entry xxx for key yyy违反唯一约束/主键约束清理重复数据或修改新值1215Cannot add foreign key constraint外键添加失败检查引擎、字段类型、索引、引用列约束1822Failed to add the foreign key constraint子表外键列缺索引先给外键列建索引再添加1364Field xxx doesnt have a default value非严格模式下insert缺值提供字段值或给字段设置DEFAULT3730Cannot drop table referenced by a foreign key constraint试图删除被外键引用的父表先删外键约束再删表这里面1215值得展开说。我见过无数次有人加外键失败反复看语法都没问题最后发现是父表和子表字段类型不一致父表id是BIGINT子表class_id是INT类型不同MySQL直接拒绝。或者父表被引用列不是主键也没有唯一约束同样报1215。排查顺序应该是先确认存储引擎都是InnoDB再比对字段类型完全一致再看父表引用列是否有主键或唯一约束最后看子表外键列有没有索引。4.2 外键导致的“删不掉、改不动”问题外键约束最常见的线上事故就是“父表数据删不掉、改不了”。典型报错是DELETE FROM class WHERE id 1; -- Cannot delete or update a parent row: -- a foreign key constraint fails这个报错背后有两种情况。第一种是子表确实有数据引用它比如班级1班下还有学生默认RESTRICT策略不允许删。第二种是子表数据里虽然有引用但你不知道具体在哪张表只能逐个排查。排查SQL可以写成SELECT * FROM student WHERE class_id 1;业务确认历史班级和学生数据都不需要时按“先删子表、再删父表”的顺序清理DELETE FROM student WHERE class_id 1; DELETE FROM class WHERE id 1;如果数据量大或有多次循环依赖可以临时关闭外键检查完成数据清理后再打开。这个开关在MySQL里是个会话级变量SET FOREIGN_KEY_CHECKS 0; -- 执行删表、清数据操作 SET FOREIGN_KEY_CHECKS 1;注意这个开关只对当前连接生效而且用完立刻改回来。我曾经接过一个现场问题开发在执行数据导入前关闭了外键检查导完忘开结果后续有外键约束全部失效垃圾数据进表了才发现。教训就是临时开关必须配一句注释和责任人最好写进自动化脚本防止漏恢复。相比之下如果当初建表时用的是ON DELETE SET NULL级联策略删除班级后学生记录会保留、class_id置空就不存在“删不掉”的问题。所以外键策略不是建完就完了要结合业务判断“父记录没了子记录要不要活着”这个决策应该发生在表结构设计阶段而不是等到删数据时才想起。4.3 外键的实战取舍不是所有场景都要上物理外键这个话题在业内争议一直很大。在MySQL里物理外键、主从复制、分库分表之间有一些现实冲突这也是为什么很多互联网团队会把“外键约束”挪到应用层去控制。比如高并发写入场景下外键检查涉及父表和子表之间的锁交互可能放大锁竞争分库分表之后跨库的外键根本没法由MySQL自己保证只能靠应用层或分布式事务去兜底。所以在实际工作中我们要区分场景单体应用、管理后台、ERP系统、订单强一致场景物理外键是很省心的选择数据库层直接保证不会出现孤儿数据而超高并发、复杂微服务架构下物理外键的代价确实需要权衡这时一般靠应用层校验、定时对账、事件补偿等方式保证最终一致。我的立场很清楚学习阶段必须把物理外键玩熟因为它能帮你建立“数据关系完整性”的直觉生产环境则要结合团队架构做决策。踩过几次坑之后我现在的习惯是“先画清楚父子表关系再决定谁来做约束”而不是无脑上外键。这个过程就像设计红绿灯红灯停绿灯行很机械但只要每个人都遵守路口秩序就能保证如果嫌红绿灯碍事你得确保有个比它更可靠的红绿灯——哪怕这个红绿灯是业务代码、是定时任务、是人工审核但你得真的有一个。最后再分享一个我做表结构评审时反复强调的细节约束的命名一定要可读。主键用pk_前缀、唯一约束用uk_、外键用fk_后面跟表名和字段名。表结构上线可不像写代码能随时重构约束名起得混乱后面排查问题光解码名称就能耗掉半天。这是低成本高回报的习惯学约束的这一天顺手就把它培养起来。