学MySQL有一阵子的人基本都会卡在同一个地方——表的约束。这个词单独拿出来看每个字都认识但一到建表就不知道该怎么用主键和唯一约束到底什么区别外键要不要加CHECK是不是摆设NULL和空字符串到底算不算“空”今天这篇就把这块拆开讲透。约束的本质就是给表“立规矩”哪些数据能进哪些数据必须拦在门外。别小看这几条规则建表时多花十分钟把约束想清楚正式跑业务以后能省掉无数排查脏数据的痛苦。这篇文章适合刚学完SELECT、正往建表设计方向走的初学者也想把我实际项目里管理约束踩过的坑、总结出的经验一并分享出来。1. 整体设计与思路拆解1.1 约束的本质用数据库兜住脏数据先想一个问题数据完整性为什么需要数据库来管很多初学者第一反应是“应用层校验不就行了吗”。实际做过项目就明白应用层校验只能约束那一个入口一旦系统有多个端、多个服务、多个历史遗留接口总有人绕过校验往库里写东西。数据库约束的优势在于它是最后一道防线不管数据从哪条路进来只要不符合规则直接拒绝写入。举个例子一张用户表如果连“邮箱不能重复”这个规则都靠应用层保证一旦某个接口漏写了判断两个账号共用一个邮箱的脏数据就进来了。后续再做营销、做登录态绑定全都会出乱子。数据库里的约束就是用引擎层面的强制力帮业务兜底不给脏数据留门。这和生活中的“小区门禁”很像门禁不是为了让每个人重新登记一遍身份而是让所有进入小区的人都必须通过同一套校验规则拦住不该进来的人。约束就是数据库的小区门禁而且它24小时不睡觉、不偷懒。1.2 六类约束总览一张表理清适用场景MySQL 的约束一共六类非空约束、默认约束、唯一约束、主键约束、外键约束、检查约束。这六个名字单独看都简单但它们各管一摊组合起来才能覆盖完整的业务规则。约束类型关键字作用典型场景非空约束NOT NULL字段不允许为 NULL用户名、手机号、订单号等必填字段默认约束DEFAULT不填时自动取默认值状态字段默认“待审核”、创建时间默认当前时间唯一约束UNIQUE字段值不能重复邮箱、身份证号、业务单号主键约束PRIMARY KEY唯一标识一行隐含非空自增ID、业务主键外键约束FOREIGN KEY关联其他表保证引用完整性订单关联用户、成绩关联学生检查约束CHECK字段值必须满足条件年龄范围、分数范围、枚举状态这里要注意一个版本差异MySQL 8.0.16 之前CHECK 约束只是“写了不报错但不生效”从 8.0.16 开始才真正强制校验。如果项目还在用 5.7想限制字段范围就得靠 ENUM 或者触发器不能只依赖 CHECK这是很多人踩过的坑。1.3 约束与索引的关系为什么说是 MySQL 学习的分水岭约束和索引不是一回事但经常被放在一起讨论。核心原因是主键约束和唯一约束在 InnoDB 引擎下会自动创建索引外键约束也会在被引用列和引用列上隐式生成辅助索引。所以约束不光是“规则”还直接影响存储结构和查询效率。这也是我把它称为“MySQL 学习分水岭”的原因。前面学 SELECT、JOIN、GROUP BY本质上是在“用”表到约束这里你开始“设计”表。你会发现建表不只是一串 CREATE TABLE而是要思考数据从哪里来、怎么保证有效、如何被其他表引用。这一关过了后面再看索引优化、事务隔离、分库分表思路都会顺很多。2. 核心细节解析与实操要点2.1 非空与默认约束最简单的两个最容易被误解非空约束和默认约束是最容易上手的但也是被误解最多的。先说说 NOT NULL。它的含义是“字段不允许没有值”但很多新手会把“没有值”和“空字符串”混为一谈。NULL 表示“未知、未填”空字符串表示“填了一个长度为0的字符串”这俩在 MySQL 里是两种完全不同的东西。NOT NULL 只能拦住 NULL拦不住空字符串。比如手机号字段设了 NOT NULL但应用层传了一个进去照样入库业务上依然等于没有手机号。要拦住空字符串在 MySQL 8.0 里可以配合 CHECK 约束来做或者应用层再做一层校验。我个人的经验是对于真正“必须有内容”的字段设计阶段就把 NULL 和空字符串都当成非法值尽早统一规范。再说 DEFAULT。默认约束的价值在于让数据“有兜底”比如订单状态字段默认pending、创建时间默认当前时间CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, status VARCHAR(20) NOT NULL DEFAULT pending, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP );这样应用层就可以少传两个字段数据库自己搞定。MySQL 8.0.13 之后 DEFAULT 还支持表达式比如默认值可以写(UUID())不再局限于常量非常实用。2.2 唯一约束跟主键一字之差用起来天差地别唯一约束和主键约束都是“不能重复”但差别很大。主键一张表只能有一个而且隐含 NOT NULL唯一约束一张表可以有多个而且它允许有多个 NULL。这个“允许 NULL 重复”的行为很多人一开始不知道实际插入两条NULL进唯一约束字段MySQL 是放行的。因为 NULL 在 MySQL 眼里是“未定义”每个未知值都可以不同不参与重复判断。两者对比看更清楚对比项主键约束唯一约束数量一张表只能有一个一张表可以有多个NULL不允许允许且可以有多个索引自动创建聚簇索引自动创建二级索引外键引用可以被外键引用也可以被外键引用业务含义一行数据的身份标识某个业务字段不重复主键强调“身份”唯一约束强调“业务唯一性”。比如用户表里id是身份email是业务唯一这两个职责分开设计最清晰。唯一约束还经常用来做“复合唯一”比如选课表里防止同一个学生重复选同一门课CREATE TABLE student_course ( id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NOT NULL, course_id INT NOT NULL, UNIQUE KEY uk_student_course (student_id, course_id) );这种写法比在应用层查一遍再插入靠谱得多因为数据库能在最底层挡住并发场景下的重复插入。2.3 主键设计自增主键与业务主键怎么选主键设计是建表的核心决策没有之一。InnoDB 里主键对应聚簇索引数据行本身按主键顺序物理排列。主键选不好插入性能、页分裂、索引膨胀都会跟着出问题。大多数在线业务系统推荐用自增整数主键。原因很简单插入是顺序的对 B 树友好页分裂概率低整数类型占用空间小二级索引的存储成本也低。对应的就是一个id INT AUTO_INCREMENT PRIMARY KEY。业务主键能不能直接做主键能但要看业务编号的性质。比如“订单号”带产品线前缀、带日期信息看起来有意义但做主键有隐患一是规则一旦变化就得改表二是字符串主键在二级索引里会大量占用空间三是如果业务编号含有特殊字符或大小写规则比较和排序都更复杂。更稳妥的办法是保留业务编号字段并加唯一约束另起一个自增主键做内部身份标识。UUID 主键在某些分布式场景下有必要但对初学者来说先不用纠结LN 界的默认答案——自增整数主键——在绝大多数场景下都不会错。3. 实操过程与核心环节实现3.1 外键约束先搞懂级联策略再动手外键是约束里最复杂的一个因为它牵扯到两张表。它的作用是保证引用完整性子表里的引用值必须在父表里真实存在。没有外键你就可能删掉一个用户却留下一堆订单还指着这个不存在的用户。先看一个最小示例CREATE TABLE student ( id INT AUTO_INCREMENT PRIMARY KEY ); CREATE TABLE score ( id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NOT NULL, score DECIMAL(5, 2), CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE ON UPDATE CASCADE );注意几个细节。第一外键列和被引用列的数据类型必须完全一致包括 UNSIGNED 这种修饰符不一致会报错。第二被引用列必须是有索引的列通常就是主键或唯一键。第三外键约束只支持 InnoDB 引擎MyISAM 建了也不生效。外键最重要也是最容易忽略的是级联策略我用一句话解释四种行为策略删除父表时更新父表时RESTRICT有子表引用则删除失败有子表引用则更新失败NO ACTION同 RESTRICT同 RESTRICTCASCADE子表对应行一并删除子表引用值同步更新SET NULL子表外键列置为 NULL子表外键列置为 NULL用 CASCADE 做删除很方便比如删除一个学生他的选课记录自动清掉但也要小心级联删除可能会误删比预期更多的数据。SET NULL 则要求外键列允许为 NULL语义上是“父记录没了子记录保留但悬空”。我自己的习惯是关键业务关系用 RESTRICT宁可报错也不自动删日志型、从属型数据才用 CASCADE。3.2 CHECK 约束MySQL 8.0 终于补齐的“自定义把关”CHECK 是现代数据库里很常见的能力MySQL 一直到 8.0.16 才真正落地。它的意思是给字段加一个布尔表达式只有满足条件的数据才能写入。比如年龄必须在 0 到 150 之间分数必须在 0 到 100 之间CREATE TABLE student_profile ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, age TINYINT, score DECIMAL(5, 2), CONSTRAINT chk_age CHECK (age 0 AND age 150), CONSTRAINT chk_score CHECK (score 0 AND score 100) );CHECK 可以用在列级也可以像上面一样用在表级。列级适合单字段简单条件表级适合多字段组合条件比如“结束时间大于开始时间”CONSTRAINT chk_time CHECK (end_time start_time)这在 5.7 里是做不到的只能靠触发器模拟非常麻烦。所以如果你在用 8.0请大胆用 CHECK 把业务规则下沉到数据库。要注意的是修改 CHECK 不能直接 ALTER只能先 DROP 再重新 ADD。命名时统一加ck_前缀后面维护时一眼就能看出是什么约束。3.3 一次成型从零建出一套选课系统表结构理论说了这么多直接来一个完整的实战。我们要建三个表学生表、课程表、选课表。选课关系到学生和课程是典型的多对多关系。-- 学生表 CREATE TABLE student ( id INT AUTO_INCREMENT COMMENT 学号, name VARCHAR(50) NOT NULL COMMENT 姓名, gender CHAR(1) NOT NULL DEFAULT M COMMENT 性别, email VARCHAR(100) NOT NULL COMMENT 邮箱, age TINYINT UNSIGNED COMMENT 年龄, PRIMARY KEY (id), UNIQUE KEY uk_email (email), CONSTRAINT chk_gender CHECK (gender IN (M, F)), CONSTRAINT chk_age CHECK (age 0 AND age 100) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 课程表 CREATE TABLE course ( id INT AUTO_INCREMENT COMMENT 课程编号, course_name VARCHAR(100) NOT NULL COMMENT 课程名称, PRIMARY KEY (id), UNIQUE KEY uk_course_name (course_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 选课表 CREATE TABLE student_course ( id INT AUTO_INCREMENT, student_id INT NOT NULL, course_id INT NOT NULL, score DECIMAL(5, 2) COMMENT 成绩未考为NULL, PRIMARY KEY (id), UNIQUE KEY uk_student_course (student_id, course_id), CONSTRAINT fk_sc_student FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_sc_course FOREIGN KEY (course_id) REFERENCES course(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT chk_score CHECK (score 0 AND score 100) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这套设计里每一条约束都有明确业务含义。学生表姓名必填邮箱唯一性别只有两种值年龄不能为负数。课程表课程名不允许重复避免同一门课被建两次。选课表复合唯一约束防止重复选课外键保证选课记录引用的学生和课程真实存在成绩范围限定在 0 到 100 分。建完表马上验证约束是否真的在干活-- 正常插入 INSERT INTO student (name, gender, email, age) VALUES (张三, M, zhangsanexample.com, 20); -- 重复邮箱会报 Duplicate entry INSERT INTO student (name, gender, email, age) VALUES (李四, M, zhangsanexample.com, 21); -- 年龄超出范围CHECK 约束会拦截 INSERT INTO student (name, gender, email, age) VALUES (王五, M, wangwuexample.com, 150); -- 插入选课记录学生和课程必须先存在 INSERT INTO student_course (student_id, course_id, score) VALUES (1, 1, 88); -- 选了不存在的学生外键报错 INSERT INTO student_course (student_id, course_id, score) VALUES (99, 1, 88); -- 删除学生由于 ON DELETE CASCADE选课记录会自动清理 DELETE FROM student WHERE id 1;这一套流程走下来你会直观感受到数据库可以在多少环节拦住非法数据。建表时多花心思跑业务时就能安心很多。4. 常见问题与排查技巧实录4.1 约束冲突类问题速查表实际开发里新手遇到约束报错是最常见的事。我把高频报错按“现象-原因-解法”整理成表方便直接查。报错/现象大概率原因解决思路Column name cannot be null非空约束拦截检查插入逻辑是否漏传必填字段或补 DEFAULTDuplicate entry xxexample.com for key uk_email唯一约束冲突先查重确认是业务不要重复还是字段约束过严Cannot add or update a child row: a foreign key constraint fails外键列引用的父行不存在先保证父表数据存在或修正子表引用值Cannot delete or update a parent row: a foreign key constraint fails外键 RESTRICT 拦截删除先处理子表依赖数据或调整外键级联策略Check constraint chk_age is violatedCHECK 条件不满足检查写入值是否越界或重新评估约束范围遇到约束报错第一件事不是急着改约束而是先判断数据是不是本来就该被拦。如果数据确实是脏数据约束拦下来是好事如果约束规则本身定得太死才考虑调整表结构。4.2 修改与删除约束三条“保命”经验第一改表前先备份或者先在一张临时表上演练。我见过不止一次一个 ALTER TABLE 下去约束名字写错或者参数没对齐直接导致整张表被锁。线上表的结构变更尤其是大表慢的一天一个 ALTER 都执行不完所以务必谨慎。第二删除约束前要知道约束的名字。MySQL 里约束名不像表名那样一眼就能看到需要查元数据SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME student;然后才能精准地 DROPALTER TABLE student DROP INDEX uk_email; ALTER TABLE student DROP FOREIGN KEY fk_sc_course; ALTER TABLE student DROP CHECK chk_age;注意唯一约束和索引同名删除用 DROP INDEX外键和 CHECK 删除用 DROP FOREIGN KEY / DROP CHECK。写错了不会报“语法错”但你会以为删掉了结果数据还是被约束拦着更坑。第三大表加约束要挑业务低峰期执行。虽然 MySQL 8.0 对不少 ALTER 操作支持 Online DDL但加唯一约束或外键时仍然需要扫描表数据并构建索引在数据量大的时候对 CPU、磁盘 IO 和锁等待都有影响。我的习惯是列一个变更窗口先跑 EXPLAIN 预判成本再决定是否分批执行。4.3 几个容易被忽略的“为什么”为什么唯一约束的 NULL 可以重复因为 SQL 标准认为 NULL 是“未知”两个未知值互不相等所以不参与唯一性判断。如果一个业务字段就是“可选的但一旦填了就不能重复”那这个行为反而正好满足需求如果业务要求“绝对不能允许空值”就得另加 NOT NULL。为什么不建议用业务编号当主键业务编号通常带规则、会变化、可能是字符串这些对聚簇索引都不友好。主键越短、越稳定、插入越有序InnoDB 的整体性能越可控。业务编号保留唯一约束即可。为什么外键对查询性能影响不大因为外键列会隐式建索引JOIN 本身就能走索引外键主要影响的是 DML 性能因为每次写入或删除都要去父表做一次存在性检查。小规模项目完全能承受大并发场景才需要权衡但学习阶段必须先会用外键别急着“优化”。还有一个经常被忽略的点约束命名规范。我的习惯是主键pk_、唯一uk_、外键fk_、检查ck_后面跟表名和字段名。这个习惯在出问题时能帮你用一条 SQL 直接在元数据表里查到目标约束省去慢慢猜的时间。最后说一点个人体会。当年学约束的时候觉得建表就是把字段类型写对就行约束好像可有可无。后来线上表里混进一堆重复邮箱、负数价格、空用户名才知道约束的重要性。如果让我只挑一条建议送给正在学 MySQL 的朋友那就是建表时把约束一次做全宁可多花十分钟想清楚每一条规则也不要等脏数据进库后用一个又一个临时脚本去擦。这个思路后来帮我省掉了太多本可以避免的夜班。
