MySQL表的约束这几年我带过的项目里十个有八个数据库出问题的根源不是SQL写得差而是建表的时候约束没定明白。新手很容易把约束当成一种“限制”觉得碍手碍脚实际上它是数据库帮你兜底的数据完整性防线。这篇文章把你必须知道的MySQL表约束全部捋一遍从类型、原理到建表实操、踩坑复盘希望你看完能直接用到自己项目里。1. MySQL表的约束为什么建表必须先想清楚它1.1 没有约束时的数据乱象先看一个最常见的反例。业务上用户昵称必须是唯一的但开发在user表里只写了一个name字段没加任何约束。上线后第一天就有两个用户同时注册成“小明”到了登录、发消息、关联订单的环节数据全乱了。这种问题靠应用层去判断总是有缝可钻因为并发情况下你说不清谁先谁后更别说还会出现进程重启、网络抖动这些意外。数据库约束的存在就是把“应该永远成立的数据规则”交给数据库去强制保证。它能管住三类问题实体完整性表内每一行能被唯一区分、域完整性字段的值符合规则比如不为空、在某个范围内、参照完整性表之间关联的数据不能对不上。1.2 约束类型一览主键、唯一、非空、默认、外键、检查MySQL里面约束可以粗分为六种主键约束PRIMARY KEY唯一标识一行一个表只能有一个主键。主键列不能为NULL而且自带唯一性和索引。唯一约束UNIQUE保证一列或一组列的值不重复可以有多个唯一约束。非空约束NOT NULL不允许字段存NULL值。默认值约束DEFAULT插入时不显式给值就用默认值。外键约束FOREIGN KEY确保子表的某列值必须存在于父表的被引用列中用来维护表之间的父子关系。检查约束CHECK限制字段的值必须满足某个表达式比如年龄大于0。其中主键、唯一、非空、默认、外键在MySQL所有常用版本里都用得很成熟CHECK则是到了MySQL 8.0.16才开始真正执行。1.3 约束是性能负担还是数据保护伞不少人担心索引会影响写入性能所以干脆全表不建约束。这种想法我理解但得看场景。主键约束会自动生成一个主键索引唯一约束会自动生成唯一索引外键也会强制在子表列上建索引。这些索引确实会增加写入时的维护成本但量级远小于数据写坏后的修复成本。说个实际案例当初我们在做订单系统时为了追求极端写入性能把外键全去掉了靠应用层保证订单和用户关联。后来因为一个历史数据清洗任务误删了用户导致的连锁反应排查了整整一周。换句话说约束本质上是在用很小的固定开销换长期的确定性这笔账大多数业务都值得。2. 核心约束逐个拆解原理、SQL写法与隐藏陷阱2.1 主键约束每张表都该有的身份证主键约束是最严格也最基础的约束。它有三个硬性要求不能为NULL、必须唯一、一个表只能有一个。主键索引本质上是一种特殊的唯一索引而且在InnoDB引擎里就是聚集索引直接影响行数据的物理存储顺序。最常用的主键写法是CREATE TABLE user ( id BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, PRIMARY KEY (id) );这里有一个很实用的设计原则最好用与业务无关的代理主键比如自增ID或者雪花算法生成的分布式ID。不要拿身份证号、手机号这种天然存在但可能会变的业务字段当主键因为一旦业务规则变了改主键代价极大。另外推荐BIGINT UNSIGNED而不是INT。很多表到后期数据量上来INT的上限2的31次方就顶不住到时候扩字段类型会锁表那才是真正的灾难现场。2.2 唯一约束保障业务逻辑上的唯一性唯一约束用来保证不重复但可以存NULL。这句话很多人会理解错。CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(100) NULL, UNIQUE KEY uk_email (email) );上面的email列如果有多行都是NULLMySQL完全允许因为在MySQL的概念里NULL表示“未知”两个未知并不相等。这个特性在某些业务下是好事比如用户不填邮箱时就把该字段置NULL系统里可以同时存在多个空邮箱用户但如果你想表达“要么为空要么全局唯一”用NULL是OK的。如果你用了空字符串去表示未填那唯一约束就会强制只能有一个未填的用户因为空串也是值这是最容易踩的坑之一。再强调一次唯一约束和唯一索引在MySQL里基本可以划等号。你写UNIQUE KEY定义的唯一约束数据库底层就是一个唯一索引。它不光是约束还能加速查询。2.3 非空约束与默认值约束填坑还是埋坑非空和默认值经常一起出现但在设计上各有各的讲究。NOT NULL很容易用过头。比如一个“用户备注”字段允许用户不填那就应该允许NULL而不是强制成空字符串。NULL和空字符串在语义上完全不同NULL表示“没有值”空串表示“一个空的值”。统计时用COUNT(column)会自动忽略NULL但不忽略空串这在报表统计上会有很大差异。默认值约束则是用来降低应用层负担的。比如创建时间字段可以让数据库直接生成created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP但要注意DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP这个组合只有TIMESTAMP和DATETIME类型下有效且8.0的高版本对DATETIME支持得很完整。如果你在5.7上稍微老一点的配置DATETIME的默认值设置会受版本限制这个需要根据实际版本确认。2.4 外键约束强一致性是福是祸外键约束维护的是参照完整性。典型场景订单表里的user_id必须存在于用户表。如果不存在插入订单时数据库直接拒绝如果父表删了某用户行子表的处理策略由ON DELETE和ON UPDATE决定。常用四种策略CASCADE父表删除/更新子表跟着删除/更新。SET NULL父表变更后子表对应列置为NULL。RESTRICT如果子表有引用父表拒绝删除/更新。NO ACTION和RESTRICT同理在MySQL里等同于RESTRICT。CREATE TABLE order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, product_name VARCHAR(100) NOT NULL, CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE RESTRICT ON UPDATE CASCADE );关于外键业界争议很大。高并发互联网项目里很多人为了写入性能和水平拆分会放弃外键把一致性交给应用层。但我要说的是中小型项目、强一致性的金融交易、后台管理系统外键是帮你防呆的好东西大型高并发分布式系统另说。如果用了外键尽量统一删除策略。混用CASCADE和RESTRICT会在删除链路上产生意料之外的阻断或者级联误删。2.5 CHECK约束MySQL 8.0才开始真正生效一个冷知识MySQL 8.0.16之前CHECK约束的语法是能写上的但数据库直接忽略不报错也不检查纯属安慰剂。从8.0.16开始CHECK约束才正式生效。比如要限制年龄字段必须大于等于0且小于120CREATE TABLE student ( id BIGINT PRIMARY KEY AUTO_INCREMENT, age INT NOT NULL, CONSTRAINT chk_age CHECK (age 0 AND age 120) );如果你还在用5.7就别指望这个约束能够拦数据了。在旧版本上业务规则里的枚举值和范围判断必须靠实体关系建模时定义的枚举类型、应用层校验或者触发器来实现。3. 实操从零开始设计一个带约束的学生课程成绩库3.1 表结构设计与约束规划这里我拿“学生课程成绩信息实体表设计mysql”这个场景来完整演示一遍。需求很简单系统记录学生、课程以及学生对某门课的一个成绩。三个核心实体学生student、课程course、成绩score。对约束的规划是学生表用自增ID作主键学号必须唯一姓名不允许为空性别用CHECK约束强制允许值入学年份默认当前年份。课程表用自增ID作主键课程名称非空且唯一。成绩表用自增ID作主键同时给学生ID和课程ID加上外键并且不允许成绩超过0到100这个范围。3.2 建表SQL完整实现CREATE TABLE student ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 内部主键, stu_no VARCHAR(20) NOT NULL COMMENT 学号, name VARCHAR(50) NOT NULL COMMENT 姓名, gender TINYINT NOT NULL DEFAULT 1 COMMENT 性别1男 2女, birth_year YEAR NOT NULL DEFAULT 2024 COMMENT 入学年份, PRIMARY KEY (id), UNIQUE KEY uk_stu_no (stu_no), CONSTRAINT chk_gender CHECK (gender IN (1, 2)) ); CREATE TABLE course ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 课程ID, course_code VARCHAR(20) NOT NULL COMMENT 课程编号, course_name VARCHAR(100) NOT NULL COMMENT 课程名, PRIMARY KEY (id), UNIQUE KEY uk_course_code (course_code), UNIQUE KEY uk_course_name (course_name) ); CREATE TABLE score ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 成绩记录ID, student_id BIGINT NOT NULL COMMENT 学生ID, course_id BIGINT NOT NULL COMMENT 课程ID, score_value DECIMAL(5,2) NOT NULL COMMENT 成绩分数, PRIMARY KEY (id), UNIQUE KEY uk_stu_course (student_id, course_id), CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT chk_score_range CHECK (score_value 0 AND score_value 100) );这里最值得说的是uk_stu_course这步联合唯一约束保证了同一个学生对同一门课只能有成绩记录。这比先查后插要可靠得多因为并发插入时应用层查不到记录就会插重数据库层直接用唯一约束拒绝第二条。进数据的验证效果插入重复的(student_id, course_id)组合会立刻报Duplicate entry。插入性别为3的学生或者成绩为105的记录数据库会分别报CHECK约束冲突的错。3.3 外键级联策略的选择与说明上例中外键都用了ON DELETE RESTRICT意思是学生或课程一旦有成绩记录就不允许直接删掉父表对应的那行。这个策略适合成绩数据需要长期留存的学籍系统防止误删。如果你业务上是课程被废弃、成绩也要一并删掉的可以考虑ON DELETE CASCADE。但真换的时候要谨慎CASCADE会让删除像雪崩一样一条DELETE从父表传染到子表。我曾经在测试环境不小心执行了DELETE FROM course WHERE id 1结果score表里几千条成绩记录被瞬间清空那感觉很难忘。另一个细节是外键引用的父表列必须有索引或者本身就是主键否则建外键直接报错。大多数情况下引用主键列是最省事的。4. 约束的日常管理查看、添加、删除与兼容性问题4.1 如何查看一张表的全部约束最直接的方式是看建表语句SHOW CREATE TABLE score;结果里会把你所有约束的定义列得明明白白包括主键、唯一键、外键、CHECK。如果你只想看索引唯一约束实际上就是唯一索引SHOW INDEX FROM score;想看更结构化的元数据可以查information_schemaSELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA 你的库名 AND TABLE_NAME score;这个结果会告诉你这条约束是什么类型。排查约束冲突时先把约束找出来对症下药。4.2 使用ALTER TABLE动态调整约束很多情况下表已经上线没法直接删了重建那就必须用ALTER TABLE来加约束。添加唯一约束ALTER TABLE student ADD UNIQUE KEY uk_email (email);添加外键约束ALTER TABLE score ADD CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course(id) ON DELETE RESTRICT;删除外键约束ALTER TABLE score DROP FOREIGN KEY fk_score_course;删除唯一约束注意要用索引的删除方式ALTER TABLE student DROP INDEX uk_email;这里有个很多人翻车的地方删除外键是DROP FOREIGN KEY删除普通唯一约束是DROP INDEX不能搞混。如果一张表既有唯一索引又是外键你要先删外键再删索引否则MySQL会报警告并且删除失败。4.3 约束和索引剪不断理还乱的关系主键约束自动生成主键索引InnoDB里是聚集索引。唯一约束自动生成唯一索引。外键约束如果不手动建索引MySQL会自动为外键列创建一个普通索引用来加速检查。CHECK约束不生成任何索引。这意味着你用约束的同时也获得了一部分查询优化能力。比如唯一键字段做等值查询通常能命中的就是唯一索引效率很高。而外键列即使平时不在业务查询条件里也因为它背后自动有索引在关联查询时沾光了。反过来如果你给一张表乱加很多唯一约束也会累积一堆索引写入时开销同步变大。这个必须评估清楚。5. 约束实战中的错误大全和避坑建议5.1 报错Duplicate entry唯一约束冲突的排查思路典型报错长这样Duplicate entry 10001 for key student.uk_stu_no意思是向student表插入学号10001时唯一约束uk_stu_no冲突了。排查时不要上来就改数据先从三个角度思考是不是应用并发导致重复插入如果是保留约束同时让应用捕获Duplicate entry错误做好幂等处理。是不是数据里已经存在脏数据要用查询确认哪几行撞了然后决定哪条该保留。是不是唯一键定义错了比如想让邮箱唯一结果索引建在了ID上。这里推荐一个清理重复数据并保留最小ID的思路DELETE t1 FROM student t1 JOIN student t2 ON t1.email t2.email AND t1.id t2.id;但执行前千万备份我之前见过有人在生产环境直接这么干把同邮箱的注册用户全删了一轮后面客诉一堆。5.2 外键添加失败的常见原因与修复报错常见就这几种Cannot add foreign key constraint百分之六七十是外键列和被引用列的数据类型不一致比如一个是BIGINT一个是INT或者一个是VARCHAR(20)一个是VARCHAR(50)。父表被引用列必须是有索引的主键或唯一键如果引用一个普通列也会失败。两表的字符集、排序规则不一致特别是utf8mb4和utf8mb4_general_ci、utf8mb4_0900_ai_ci这类差异也会导致建外键失败。遇到这类问题用SHOW CREATE TABLE逐字段比对两边表的定义几乎马上就能定位。5.3 NULL的迷惑行为唯一约束放行多个NULL前面讲过唯一约束允许多个NULL值。有些业务恰恰不允许“多个空手机号”同时存在但又希望没填手机号的用户能保存。这时候有两个方案方案一用一个“哨兵值”代替NULL比如或者00000000000然后加唯一约束。但这样业务语义会被污染统计时也得绕开。方案二MySQL 8.0可以给唯一索引指定“部分唯一”的语义很遗憾MySQL没有部分索引。想实现“NULL可以多非NULL必须唯一”可以直接用生成列。生成一个列当原列为NULL时存一个UUID或固定标识其实不行因为固定标识会导致所有NULL行冲突。更优雅的办法是生成列里对NULL存主键值对非NULL存原值CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, phone VARCHAR(20) NULL, phone_unique VARCHAR(40) GENERATED ALWAYS AS (IF(phone IS NULL, CONCAT(null_, id), phone)) STORED, UNIQUE KEY uk_phone (phone_unique) );这个技巧很冷门但确实能解决“多个空值非空唯一”的真实需求。每次插入时如果phone是NULL生成列会变成null_1、null_2这种各自不同的数据所以不会撞唯一约束如果phone有值就直接参与唯一判断。5.4 大小写与字符集唯一约束不认大小写别被坑了MySQL的默认排序规则一般是utf8mb4_0900_ai_ci或utf8mb4_general_ci这两个都是大小写不敏感、重音不敏感的。这意味着你给邮箱加唯一约束后Testexample.com和testexample.com会被当成同一个值第二个插入直接被拒。这在国内项目里经常引发事故因为很多业务认为邮箱区分大小写实际邮箱本地部分确实区分但大家默认通用化忽略大小写或者认为用户名不该区分大小写。但如果你真的需要区分可以建表时指定bin排序规则CREATE TABLE user ( email VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, UNIQUE KEY uk_email (email) );或者只针对索引列指定ALTER TABLE user ADD UNIQUE INDEX uk_email (email COLLATE utf8mb4_bin);如果你用的是人大金仓Kingbase数据库并处于MySQL兼容模式也可能会遇到类似“字符串不区分大小写”的问题本质上是兼容模式下的排序规则在起作用。遇到这种问题不要硬记某个数据库的答案先查当前数据库、表和列的collation。5.5 删除主键遇到AUTO_INCREMENT的连环坑你想删掉一个自增主键跑这条SQLALTER TABLE student DROP PRIMARY KEY;结果MySQL报错Incorrect table definition; there can be only one auto column and it must be defined as a key因为id列还是AUTO_INCREMENTMySQL不允许把唯一键主键删掉。必须先删掉自增属性再删主键ALTER TABLE student MODIFY id BIGINT NOT NULL; ALTER TABLE student DROP PRIMARY KEY;如果后续还想把别的列设为主键但原来的id列又不再需要自增那还好办。关键是操作前要确认没有别的地方引用这个主键。如果存在外键引用还需要先删掉外键否则依然失败。关于约束设计我最后想说的做MySQL表结构设计这几年我体会到约束设计其实是业务规则的一部分。你愿意把多少规则放进数据库决定了数据的可靠程度和人肉维护的量。我的原则很简单能由数据库保证约束的就不要放到应用层。主键必须有唯一性规则尽量用唯一约束跨表一致性只要有明确的级联语义就大胆用外键。应用层可以做校验提示但数据库应是最后的守门员。合理使用约束确实会让开发初期多写几行SQL但它真正帮你的时间是在上线之后。一次因为缺约束导致的数据重复、关联错乱、统计失真代价可能是一个通宵加一封道歉邮件。设计表的时候多花一分钟后面可能省下一天。
