我们写业务代码的人,天天跟数据打交道,但数据这玩意儿特别容易长歪。你说你往表里插了一行数据,name字段是空的,age字段写了个负数,user_id 指向了一个根本不存在的用户,这事儿谁能忍?所以数据库给了我们一套“红绿灯”机制——约束(Constraint),帮你我在数据进库之前就把不合规的车拦下来。
这篇文章就把MySQL里面最核心的三种约束掰开揉碎讲清楚: 非空 、 主键 、 外键 ,到底各自管什么,什么时候用,什么时候别硬上,以及我在实际开发里踩过的那些坑。适合刚学数据库的初学者,也适合写过几年SQL但对约束设计还不太上心的老伙计。把这套东西理顺了,你的表结构设计能少走很多弯路。
1. 先讲清楚:为什么说约束是数据库的“红绿灯”
你要是去路上开车,没有红绿灯的十字路口会乱成什么样,数据库里的数据也一样。没有约束的表,就像一条不限速、不查驾照、也没有车道线的公路,什么烂数据都能往里塞。有的同学觉得,我在Java代码里判断一下不就行了?我在前端表单里做个校验不就行了?话是没错,但你挡不住所有入口。
数据入口从来不止一个。业务后台、定时任务、数据修复脚本、同事手滑在Navicat里执行的一条update,甚至还有直接从Excel导入的批量数据。你能在每个入口都写一套校验逻辑吗?就算你写了,能保证每一套都校验得一模一样吗?难点就在这。
数据库约束的核心理念就一句话: 把校验规则下沉到数据层,让数据库成为数据的最后一道关卡 。不管你是从哪个门进来的车,只要想进这条马路,就得先过红绿灯。
MySQL的约束体系,按照我自己的理解,可以分成这么几层:
- 行内约束 :管的是这一行数据本身合不合法,比如非空、唯一、默认值、检查约束。
- 表间约束 :管的是这行数据和别的表数据之间的关系,比如外键。
- 结构约束 :管的是表结构本身怎么组织,比如主键、索引、自增。
这几种约束各有各的脾气。有的宽松,有的严格,有的会在关键时候拉你一把,也有的会在你删数据的时候突然炸毛。咱们一个一个来看。
可能你会问,约束这么多,到底有什么用?
- 保证数据完整:不允许残缺数据、错误数据出现在表里。
- 保证数据一致:多条记录之间、多张表之间,逻辑对齐。
- 保证数据可追溯:能通过主键精确找到任意一条记录。
- 减少脏数据带来的额外排查成本。
我记得刚入行那年,接手过一个老项目,订单表里面居然有user_id为0的记录,查了半天查不到是哪个用户下的单。最后发现是早期代码里有个bug,插入订单的时候没取到登录态,默认填了0。要是那时候表上有外键约束,这单子根本插不进去,bug当天就会被揪出来,后续几天的对账排查全都省了。约束这东西,看着像束缚,实际上是保护。
2. 非空约束:数据的最基础底线
先说最简单的:非空约束(NOT NULL)。道理不用讲,就是这一列不允许存NULL值。
2.1 什么场景下一定要非空
所有业务上“必须有值”的字段,都应该加非空约束。举几个非常典型的例子:
- 用户的用户名:注册的时候必须填,否则用户登录都找不到账号。
- 订单的订单号:每笔订单必须有流水号,没有订单号这单子就是无效数据。
- 商品的库存数量:库存必须有数值,哪怕为0也是有意义的,绝对不允许未知。
如果这些字段允许为空,后面的数据统计、查询、关联,全都可能出问题。你想想,统计用户数的时候 WHERE username IS NULL 到底算不算一个用户?到时候各业务方各执一词,扯皮扯得你头大。
2.2 空字符串和NULL的区分
这是新手最容易踩的坑。非空约束只能保证这一列不是NULL,但它管不住空字符串''。这两个看起来都是“没值”,在MySQL里却是两回事:
| 对比项 | NULL | 空字符串 '' |
|---|---|---|
| 含义 | 未知、未填写 | 明确填写了“空” |
| 占用空间 | 有额外标记 | 按字符串存储 |
| 参与计算 | 结果基本为NULL | 可以正常拼接 |
| 索引匹配 | 不走常规索引 | 可以走索引 |
| 比较方式 | IS NULL / IS NOT NULL | = '' 或 != '' |
我自己早期写表结构的时候,经常把这个搞混。比如用户昵称字段,我加了 NOT NULL,但业务逻辑里用户可以不填昵称,结果代码传了个空字符串进来,照样插入成功了。最后统计“有多少用户没设置昵称”的时候,用了 IS NULL,死活统计不出来,因为数据存的是 ''。
所以,使用非空约束之前,要想清楚一个业务问题:这个字段的“空值”是永远不会出现,还是会有明确的空字符串占位?想清楚了再去加约束。
2.3 非空约束的实战操作
建表的时候可以直接加:
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT COMMENT '用户ID',
username VARCHAR(50) NOT NULL COMMENT '用户名',
nickname VARCHAR(50) DEFAULT '' NOT NULL COMMENT '昵称,未设置为空串',
phone_number VARCHAR(20) COMMENT '手机号,允许不填',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
对已经存在的表,想把某个字段改成非空,用 MODIFY 或者 ALTER 都行:
ALTER TABLE users MODIFY COLUMN username VARCHAR(50) NOT NULL;
这里有一个特别坑的细节:如果表里已经存在NULL数据,执行这条语句会直接报错。比如 username 字段当前有3条记录是NULL,上面的 ALTER 就会失败,提示 Incorrect column value: 'NULL' for column 'username'。所以改历史表之前,一定要先把脏数据清理干净:
-- 先看一下哪几条是脏数据
SELECT * FROM users WHERE username IS NULL;
-- 清理脏数据,再执行约束修改
UPDATE users SET username = 'unknown' WHERE username IS NULL;
ALTER TABLE users MODIFY COLUMN username VARCHAR(50) NOT NULL;
顺序不能反,不然那条 ALTER 语句就是原地翻车。
3. 主键约束:每一行数据的身份证
主键(PRIMARY KEY)是MySQL里最重要的约束,没有之一。它负责给每一行数据发“身份证号”,保证每一行都有一个唯一标识,而且整个表里不会有两行一模一样的ID。
3.1 主键的两个基本特性
唯一性 :一个表的主键列,每一行的值都不能重复。这就确保了你可以通过主键精确找到某一行数据。
非空性 :主键列不允许为NULL。MySQL里的主键约束其实已经包含了非空约束的逻辑,你不需要单独给主键列再写一个 NOT NULL,它本身就禁止NULL。
这里要稍微注意一下 MySQL 对主键这种非空要求的实现。MySQL的InnoDB引擎里,主键列的定义默认就是NOT NULL。哪怕你建表时没写NOT NULL,MySQL也不会报错,但会自动替你加上非空属性。你查一下表结构就会发现,主键字段的Null属性总是显示NO。
3.2 主键的选择:自增还是UUID
这个话题在技术社区里吵了好多年,我说说自己的实践感受。
| 方案 | 优点 | 缺点 |
|---|---|---|
| 自增整型主键 | 写入快、占用空间小、索引效率高 | 有规律容易被遍历,分布式环境下有冲突 |
| UUID主键 | 全局唯一、生成简单、适合分布式 | 占用空间大、随机写入导致页分裂、性能偏低 |
| 雪花算法ID | 全局唯一、趋势递增 | 需要引入发号器组件 |
我个人的习惯是:单体应用、中小型系统,默认自增主键,省心又高效。分布式系统、分库分表的场景,优先考虑雪花算法这类全局有序ID。
MySQL的InnoDB是聚簇索引表,数据本身的物理存储顺序就是按照主键来的。自增主键可以保证插入的数据基本是顺序追加的,页分裂少,写入性能高。换成UUID的话,完全随机的值插入到索引树里,就会频繁触发节点的分裂、重组,写性能会明显下降。
3.3 联合主键什么时候用
联合主键就是用两列以上共同组成一个主键,比如:
CREATE TABLE course_selection (
student_id INT NOT NULL COMMENT '学生ID',
course_id INT NOT NULL COMMENT '课程ID',
semester VARCHAR(20) NOT NULL COMMENT '学期',
score DECIMAL(5,2) COMMENT '成绩',
PRIMARY KEY (student_id, course_id, semester)
) ENGINE=InnoDB COMMENT='选课记录表';
这个设计的意思是:同一个学生在同一个学期选修同一门课,只能有一条记录。联合主键适合描述“多对多关系”的中间表,以及需要从多个维度保证记录唯一的业务场景。
但联合主键也有坑。最典型的问题就是:如果将来业务需求变化,需要允许“同一个学生同一学期可以重复选修同一门课”的时候,联合主键就成了一条锁链,你还得去改主键结构,麻烦得要命。而且联合主键的字段越多,索引文件就越大,查询效率也会受影响。
所以我的经验是:能用单一主键表达的场景,别急着上联合主键;哪怕业务上需要唯一性,很多时候用一个逻辑上不存在的自增ID做物理主键,另外再加一个唯一索引保证业务唯一性,反而更灵活。
3.4 主键索引到底是怎么起作用的
主键会自动生成主键索引。你在MySQL里查询 WHERE id = 5,InnoDB会顺着主键索引这个B+树,几乎一瞬间就定位到记录。
MySQL主键索引和非主键索引的一个关键区别:普通索引的叶子节点存的是主键值,每次查询都要先走一遍索引,再根据主键回表去查完整记录;而主键索引的叶子节点存的就是完整的数据记录,一次定位就搞定。
这个知识对写SQL很有用:能用主键查询的,尽量不要用其他普通索引去兜底。多一次回表,可能多几十倍的磁盘IO。
4. 外键约束:表与表之间的红绿灯
非空约束管表内数据,主键约束管单表行的唯一性,外键约束管的则是 表与表之间的关系 。如果把数据库比作一座城市,外键就是路口红绿灯——车辆要右转进入另一条路,必须先确认那条路存在且合法。
4.1 外键到底在保护什么
直接看场景。有两张表,用户表users和订单表orders:
CREATE TABLE orders (
id INT NOT NULL AUTO_INCREMENT,
order_no VARCHAR(50) NOT NULL,
user_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB COMMENT='订单表';
加了这行外键约束之后,MySQL会强制保证以下逻辑:
- 插入订单时,user_id 必须在 users 表里真实存在。
- 修改订单的 user_id 时,同样要保证修改后的 user_id 存在于 users 表。
- 删除 users 表里的某一用户时,如果这个用户还有订单,删除行为会被阻止,或者按照你定义的动作联动处理。
这就把“订单必须属于真实用户”这个业务规则下沉到了数据库层。你再也不用在插入订单之前,手忙脚乱地去查一遍用户是否存在了。数据库替你盯着这件事。
4.2 外键的四种触发动作
当父表(被引用的表)发生删除或更新时,子表(有外键的表)该怎么应对?MySQL外键支持四种规则:
| 动作 | 含义 | 适用场景 |
|---|---|---|
| RESTRICT | 有子表记录引用时,禁止删除/更新父表记录 | 默认行为,安全第一 |
| CASCADE | 父表删除/更新时,子表记录同步删除/更新 | 父子数据“同生共死” |
| SET NULL | 父表删除时,子表外键列置为NULL | 保留子表记录但不保留关联 |
| NO ACTION | 同RESTRICT,实际上就是RESTRICT | MySQL中与RESTRICT等价 |
比如订单明细和订单的关系,订单删了明细肯定没意义,用CASCADE就很合适。用户和订单的关系,用户注销以后订单可能还要留作审计,就不应该用CASCADE,应该用RESTRICT阻止删除,或者软删除解决方案。
4.3 外键的争议:为什么很多公司明确禁止使用
聊到这就必须说一个现实问题:外键在两个阵营之间,存在非常明显的分歧。大厂面试的时候,有相当一部分面试官会问“为什么阿里规范不建议使用外键”,很多人只记住了答案却不知道背后的逻辑。
外键的主要问题有三个:
问题一:写入性能下降 。每次插入、更新子表,都要去父表检查对应记录是否存在。这一步看着简单,可一旦表数据量上了百万级,或者并发高起来,这个额外校验的开销就被放大了。
问题二:锁竞争和死锁风险 。外键检查通常要持有父表行级的共享锁,高并发下很容易引发锁等待以及死锁。
问题三:分库分表改造成本高 。一旦业务量起来要拆分数据库,外键这种跨库关联就废掉了。数据库层面没法做物理外键了,只能靠应用层保证逻辑关系,到时候要么删外键,要么做很复杂的兼容处理,都是苦活累活。
但这不代表外键就是洪水猛兽。中小型项目、内部管理系统、并发量不高的场景,外键能帮你省掉一大坨业务代码,而且数据一致性有保障。我的原则是: 数据一致性要求高、写并发低、表关系稳定的场景,放心用外键;超高并发或未来铁定要拆库的场景,才去考虑禁用外键。
4.4 外键设计时容易忽略的细节
- 外键列和引用列的数据类型必须完全一致,不然建不出来。
- 外键引用的列必须是有索引的列,通常都是主键列,这点天然满足。
- 字符集和排序规则要一致,否则外键约束建不成功,这是一个很隐蔽的坑。两张表都是utf8mb4,但一张表用的排序规则是utf8mb4_general_ci,另一张是utf8mb4_unicode_ci,外键就是建不上。
- 被引用的父表记录,除非必要,尽量别做物理删除。用软删除(加一个deleted标志位)会更稳。
5. 约束的管理:改约束、删约束、临时关闭约束
很多人建表之后就没有然后了,表结构一跑就是好几年。其实约束本身也是可以被管理的,该改就改,该删就删。
5.1 主键约束的修改与删除
删除主键:
ALTER TABLE users DROP PRIMARY KEY;
给没有主键的表添加主键:
ALTER TABLE users ADD PRIMARY KEY (id);
删除自增后再改主键,需要两步走:
-- 第一步:去掉自增属性
ALTER TABLE users MODIFY COLUMN id INT NOT NULL;
-- 第二步:删除主键
ALTER TABLE users DROP PRIMARY KEY;
这里有个容易踩的坑:如果原来主键列是自增的,必须先去掉AUTO_INCREMENT属性再删主键,否则会报错。同样的逻辑,加自增主键时也要先把原有主键删掉,再加新的自增列,顺序反了MySQL会给你脸色看。
5.2 外键约束的修改与删除
删除指定外键:
ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;
添加外键:
ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id);
外键约束有个比较麻烦的地方:删除外键并不会自动删除外键列上的普通索引。MySQL会为外键列自动创建一个索引,删掉外键后这个索引还会留下。如果你确定不需要这个索引了,要手动再删一次:
ALTER TABLE orders DROP INDEX fk_orders_user;
5.3 特殊场景下临时关闭外键检查
有时候你需要导入一批历史数据,数据量很大,而且导入的先后顺序没法保证父表记录一定在子表记录之前,这时候外键检查就成了绊脚石。可以用:
-- 关闭外键检查
SET FOREIGN_KEY_CHECKS = 0;
-- 执行大批量导入操作
LOAD DATA INFILE '/tmp/orders.csv' INTO TABLE orders;
-- 记得重新开启,不然会话内所有外键保护都会失效
SET FOREIGN_KEY_CHECKS = 1;
这句 SET FOREIGN_KEY_CHECKS = 0 在MySQL里只会影响当前会话(连接),不会全局生效,相对安全。但无论如何,关闭之后一定要记得打开,尤其是JDBC连接池的复用场景,如果连接被归还前没有把开关恢复,下一个业务请求就可能带着外键关闭的状态运行,风险极高。我自己一般会在finally块里强制恢复,避免连接池污染。
6. 日常开发中那些“报错”到底在说什么
约束用得不熟的时候,看MySQL的报错真是一头雾水。我在这节列几个最常见的报错场景和排查思路,以后看到直接就能对上号。
6.1 Column 'xx' cannot be null
这个报错代表非空约束生效了。你想往一个NOT NULL字段里插入NULL,直接被拒了。
排查思路:先看业务代码里这个字段到底有没有赋值,再看是赋值成了null还是空字符串,最后确认表结构里这一列是不是不该加NOT NULL约束。如果业务允许为空,那就说明表结构设计错了,应该去掉非空约束——而不是为了一条报错去改业务代码。
6.2 Duplicate entry '1' for key 'PRIMARY'
主键冲突,最常见的错误之一。说明你插入的数据主键值已经存在了。
排查思路:确认是不是真的插入了重复数据,还是主键值没有按预期递增。很多时候自增主键跳号是正常的,比如删除过记录或者插入失败过,都会导致自增ID不连续,不要过分惊讶。
6.3 Cannot add or update a child row: a foreign key constraint fails
外键约束拦截。说明你插入或更新的子表记录,引用了父表里不存在的ID。
排查思路:先查父表里有没有这个ID,没有就说明业务数据有问题;有的话,检查一下是不是字符集不一致、字段类型不一致导致匹配不上。
6.4 Cannot delete or update a parent row: a foreign key constraint fails
删父表记录时被外键挡下来了,说明子表里还有引用这条父记录的记录存在。
排查思路:确认业务上是不是需要级联删除,如果是,把外键ON DELETE改成CASCADE;如果不是,说明删除操作本身可能是误操作,应该保留父表这条记录。
6.5 常见问题速查表
| 报错关键字 | 含义 | 解决办法 |
|---|---|---|
| cannot be null | 非空约束拦截 | 业务补值或去掉非空约束 |
| Duplicate entry | 唯一性/主键冲突 | 检查是否插入了重复值 |
| foreign key constraint fails | 外键引用不存在 | 检查父表关联ID |
| is not allowed in a generated column | 生成列与约束冲突 | 修改表结构定义 |
| Incorrect column value | 历史脏数据导致改约束失败 | 先清理脏数据再改 |
7. 关于约束设计,我最后想说的几个实操原则
约束这东西,不是用得越多越好,也不是用得越少越好。设计约束就是在“数据安全和系统灵活性”之间找平衡点。基于我这些年做过的项目,最后分享几个实际原则。
7.1 能加在数据库层的,尽量不要只放业务层
很多人觉得业务代码里做判断就够了,数据库层再加一层约束是浪费时间。但我的经验是,数据库约束是成本最低的数据保护手段。业务代码会重构,接口会变化,人为疏漏会存在,但数据库约束只要不删,它就一直在守卫你的数据。从安全性来说,它比任何一次业务代码review都可靠。
7.2 约束不要设计完就放着不管,要定期review
表结构不是一次定死的东西。业务变了,约束也要跟着变。比如某个字段以前必须填,后来业务改了可以不填,那你就要及时去掉非空约束;反过来,以前允许为空的字段,现在业务上必须有值,那就得补上约束。我见过太多项目,表结构建了五年,约束从未调整过,数据质量早就失控了。
7.3 表的命名和注释也要一起规范
约束字段的名字要有意义,外键约束的名字更是如此。比如 fk_orders_user 一看就知道是orders表关联users表。最怕看到什么 fk_1、fk_2 这种偷懒命名,三个月后自己看都得猜半天。字段注释同样重要,尤其是业务上有特殊标记的字段,比如0代表未删除、1代表已删除,这类信息一定要写在字段注释里,不然下一个接手的人会一脸懵。
7.4 测试环境随便改,生产环境改约束必须走流程
生产环境执行 ALTER TABLE 修改约束,务必在低峰期操作,先备份数据,再在测试环境验证SQL,最后在生产小流量灰度。改约束不是小事,尤其是有外键互相关联的几张核心表,操作前最好把涉及的关联关系全部梳理一遍,写成checklist,避免漏改。
讲到最后,还是那句话:约束的本质不是限制你,而是保护你。它帮你把数据质量的底线守住了,让你写业务代码的时候不用天天惦记数据会不会烂掉。设计表的时候多想五分钟,后面能给你省下几十个小时查脏数据的时间,这笔账怎么算都不亏。
318



被折叠的 条评论
为什么被折叠?



