聊MySQL表的约束前一篇已经把非空NOT NULL、唯一UNIQUE、主键PRIMARY KEY这“三座大山”讲完了它们在字段级别把数据的“底线”卡得死死的不能为空、不能重复、每行都有唯一标识。但实际建表时光有这三个还不够。你肯定遇到过这种需求某个状态字段希望不填时默认是0某个年龄字段不允许超过150订单表要关联用户表而且用户删了订单怎么办……这些都属于“约束下”的内容也就是默认值约束、自增约束、检查约束和外键约束。这篇我把它们一次讲透结合建表实操、踩坑记录和排查方法新手可以直接照着抄老手也能看看有没有自己忽略的细节。1. 先盘一盘“约束下”到底还剩什么1.1 上半场与下半场的边界在哪MySQL官方文档里表约束通常按功能分成几类。上半场我们处理的是“字段值本身要满足什么规则”比如字符串不能为空、手机号要唯一、每行要有主键。这些约束特点是它们只盯着“当前这一行”的数据不关心其他表甚至大多数时候不关心同一张表的其他行唯一约束除外它要扫描整个索引。下半场要聊的约束本质上在做两件不一样的事进一步控制“字段值怎么生成”默认值约束DEFAULT决定插入时不给值该填什么自增约束AUTO_INCREMENT决定数值型主键怎么自动生成。它们管的是“数据从哪来”的问题。管“行与行、表与表之间的数据关系”检查约束CHECK可以限制同一行内多个字段的取值逻辑比如结束时间必须晚于开始时间外键约束FOREIGN KEY强制子表引用的父表记录必须存在还解决了“父表记录被删除/更新时子表怎么联动”的问题。所以理解上不能把“约束下”当成一小部分简单语法它是从“字段自治”走向“关系治理”的分水岭。很多初学者建表只写主键和唯一约束结果程序里各种脏数据都进来了就是因为下半场这些约束没用好。1.2 一个建表演进的小故事先说个我当年带新人的例子。有个学生课程成绩系统最开始实习生的建表脚本大概是这样的CREATE TABLE student ( id INT PRIMARY KEY, name VARCHAR(50), gender CHAR(1), age INT );这表跑起来问题一堆不传名字可以直接插入空字符串性别可以写成“X”和“男”混在一起年龄可以填300岁甚至学生都不存在成绩表也能插进去一条记录。后面被迫在业务层写一大坨if else去校验还是挡不住漏网之鱼。后来把约束补齐同样一张表变成这样CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, gender CHAR(1) DEFAULT 男 CHECK (gender IN (男, 女)), age INT DEFAULT 0 CHECK (age BETWEEN 0 AND 120) );一眼就能看出这张表“允许什么数据进来”。这就是约束下部分的价值把数据校验从“应用层的人肉防守”变成“数据库层的自动化防守”。谁写错了SQL数据库直接报错连脏数据进库的机会都没有。2. 默认值约束与自增约束的实操细节2.1 DEFAULT的正确打开方式默认值约束语法很简单但坑非常多。最基本用法就是建表时指定CREATE TABLE t_user ( id INT PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(50) NOT NULL, status TINYINT DEFAULT 0, register_time DATETIME DEFAULT CURRENT_TIMESTAMP );这里status字段设置了默认值为0正好对应很多人搜的“mysql设置默认值为0”。注意DEFAULT 0和DEFAULT 0在MySQL里有时会被隐式转换但强烈建议你要什么类型就给什么类型的默认值别拿字符串糊弄整数列虽然大多数时候不报错但一旦你开始用严格模式类型不匹配的默认值直接建表失败。三个容易踩的坑默认值只在“不给这个字段”时生效。如果你显式插入NULL默认值不会生效结果还是NULL。这是新手最容易误解的地方以为默认值能兜住NULL实际上兜不住。想要“NULL自动变0”得用COALESCE预处理或者干脆把字段设为NOT NULL DEFAULT 0。TEXT/BLOB/JSON等字段不能有默认值。MySQL官方限制BLOB、TEXT、GEOMETRY、JSON类型的字段不能设置DEFAULT。很多人想在JSON字段上设置默认值{}直接报错。解决思路要么在应用层给值要么用触发器要么MySQL 8.0.13以上的版本可以给表达式默认值后面说。表达式默认值是8.0.13才有的能力。在这之前DEFAULT后面只能跟一个字面量或CURRENT_TIMESTAMP。8.0.13以后可以写成CREATE TABLE t_example ( id INT PRIMARY KEY, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, data JSON DEFAULT (JSON_OBJECT()), expire_time DATETIME DEFAULT (DATE_ADD(NOW(), INTERVAL 7 DAY)) );注意表达式默认值外面必须加一层括号否则会被当成语法错误。这个特性特别适合做“创建时间自动填充、过期时间自动计算”这种需求少写多少应用层代码。2.2 自增约束AUTO_INCREMENT不只是“加一”自增约束通常是跟主键搭配的最基础写法就是id INT PRIMARY KEY AUTO_INCREMENT。但用的过程中有几个点需要自己心里有数。第一自增初始值和步长是全局/会话级别的。默认auto_increment_offset是1auto_increment_increment是1。如果你想让某张表从1000开始可以在建表语句里指定CREATE TABLE t_order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL ) AUTO_INCREMENT 1000;也可以建表后用ALTER TABLE t_order AUTO_INCREMENT 1000;修改。注意如果你指定的起始值小于当前最大值加1MySQL不报错也不生效还是沿用当前逻辑。第二自增列不会因为删除数据而回退。比如你插入了1、2、3删除3再插入下一条是4不是3。InnoDB的自增计数是保存在内存里的8.0之前重启后会用SELECT MAX(id)重新计算所以可能会出现“删了最大id重启后主键复用”的极端现象。8.0以后这个计数会持久化到redo log里相对更稳。第三TRUNCATE TABLE和DELETE FROM的区别。TRUNCATE会重置自增计数DELETE不会。如果你清空表后希望id从1开始用TRUNCATE但要注意TRUNCATE不能用于有外键引用的父表。第四批量插入时自增值会跳号。这是因为InnoDB默认对自增锁采用innodb_autoinc_lock_mode28.0默认批量插入时预先分配一批自增值不保证连续。这是正常现象不是bug。如果业务强依赖主键连续自增主键就不是好选择得用序列或者应用层生成。实操建议单机小表用INT就够但凡是可能超过21亿行的表直接上BIGINT。别图省事用INT等到溢出那天你只能改表锁表代价极大。3. 检查约束CHECK8.0.16才真正落地3.1 CHECK约束的语法与正确姿势很多从MySQL 5.7过来的朋友对CHECK约束的印象就是“写了也没用MySQL根本不校验”。这句话说对了一半。MySQL 5.7及以下的版本确实会解析CHECK语法但直接忽略所以表能建出来脏数据照样进。但从MySQL 8.0.16开始CHECK约束被强制执行成了真正能用的约束。语法上CHECK既支持列级约束也支持表级约束。列级约束只针对当前字段表级约束可以涉及多个字段。举个例子CREATE TABLE t_employee ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, age INT, salary DECIMAL(10,2), join_date DATE, leave_date DATE, CHECK (age 18 AND age 60), CHECK (salary 0), CHECK (leave_date IS NULL OR leave_date join_date) );最后一条表级CHECK是列级约束做不到的它校验同一条记录里leave_date和join_date的关系这在业务里特别常见离职日期要么为空要么晚于入职日期。你可以在Workbench里故意插入一条join_date2024-01-01, leave_date2023-06-01的记录8.0.16以上会直接报Check constraint xxx is violated。3.2 为什么以前不生效现在又生效了这里要解释一下历史原因。MySQL很早就把CHECK语法写进了文档但内部优化器一直没有真正实现校验逻辑更像是“预留语法位”。到了8.0.16官方终于补上了这个功能并且默认开启。要注意的是如果你是在MySQL 5.7里建了带CHECK约束的表然后通过某种方式升级到8.0.16旧的CHECK约束依然可能不生效因为表定义里它可能没被完整记录。遇到这种情况最好的做法是重新执行一遍ALTER TABLE ... ADD CONSTRAINT ... CHECK (...)确保约束真正被加载。验证CHECK约束是否生效可以用一个简单办法SHOW CREATE TABLE t_employee;如果输出里有CONSTRAINTxxxCHECK ((age 18))这样的行说明约束在。然后故意插入一条违规数据看会不会报错。3.3 CHECK约束和触发器怎么取舍有些人习惯用BEFORE INSERT/BEFORE UPDATE触发器做复杂的字段校验因为之前CHECK不给力。现在有了强制的CHECK约束两者怎么选我的经验是能写进CHECK的简单条件绝对不用触发器。CHECK的开销极低定义清晰通过information_schema.CHECK_CONSTRAINTS就能查触发器的维护成本高、嵌套复杂、出错不容易排查。但CHECK只能做“当前行数据值”的校验如果校验逻辑需要查其他表比如“订单金额不能超过用户余额”那只能靠触发器或用例程去查CHECK约束做不了跨表查询。另一个CHECK的局限是它不允许使用存储函数也不允许使用子查询。也就是说CHECK ((SELECT ...) 0)这种写法会被拒绝。如果你遇到“需要子查询做校验”的需求还是老老实实走触发器或应用层。4. 外键约束FOREIGN KEY数据库层面的关系完整性与踩坑4.1 外键级联策略到底怎么选外键约束解决的核心问题是“引用完整性”子表里的值要么是空要么在父表里真实存在。先看一个最经典的学生-课程-成绩模型CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, student_no VARCHAR(20) NOT NULL UNIQUE, name VARCHAR(50) NOT NULL, gender CHAR(1) DEFAULT 男, age INT, status TINYINT DEFAULT 1 ); CREATE TABLE course ( id INT PRIMARY KEY AUTO_INCREMENT, course_name VARCHAR(100) NOT NULL, credit TINYINT DEFAULT 0 ); CREATE TABLE score ( id INT PRIMARY KEY AUTO_INCREMENT, student_id INT NOT NULL, course_id INT NOT NULL, score DECIMAL(5,2), exam_date DATE, UNIQUE KEY uk_stu_course (student_id, course_id), CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course(id) ON DELETE CASCADE ON UPDATE CASCADE );这个模型里成绩表通过外键引用了学生表和课程表。它的含义是你不能给一个不存在的学生打分也不能给一门不存在的课程打分。这个约束在数据库层面彻底杜绝了“孤儿数据”。ON DELETE和ON UPDATE后面的策略是重点常见的有策略行为说明CASCADE父表删除或更新时子表跟着删或跟着改SET NULL父表删除或更新时子表对应字段设为NULL子表字段必须允许NULLRESTRICT有子表引用时父表不允许删除或更新直接报错NO ACTION和RESTRICT类似实际是“延迟检查”MySQL里效果等同RESTRICTSET DEFAULT标准SQL里有但MySQL的InnoDB不支持写了也会被忽略实际业务里怎么选我的建议是强父子关系比如“订单明细-订单主表”用户删订单时明细也没意义了用ON DELETE CASCADE最省心。弱依赖关系比如“成绩-学生”删除学生时成绩若是历史数据不能删那就不用CASCADE或者用RESTRICT阻止删除学生或者把student_id改成可空再SET NULL。绝大多数情况ON UPDATE CASCADE可以放心用。因为主键修改频率低但如果业务里存在用“业务编号”当主键且编号允许修改的情况级联更新能省很多事。4.2 外键对性能的影响和架构之争关于外键生产环境一直有两派声音一派坚持数据库外键必须用一派说外键影响性能、高并发下要命。我的观点是外键不是银弹但它不是性能杀手真正的杀手是你乱加索引乱关联。外键带来的性能开销主要在“写入时”因为插入子表记录时数据库要去父表对应的索引上检查主键或唯一值是否存在。这在写入频繁、父表很大的场景下确实会放大写放大。但注意如果你在应用层也能保证引用合法那这个校验是重复的如果你的应用层根本保证不了那么外键是必需品不能拿性能当借口。高并发互联网项目里分库分表后外键基本废弃因为跨库外键无法实现。此时引用完整性全靠应用层事务补偿机制去保证。但如果你做的是企业内部系统、ERP、后台管理这类并发量不高的应用大胆用外键它能帮你省下无数脏数据清理的夜晚。而且外键会要求MySQL自动在子表字段上创建索引这反而能加速联表查询。还有个容易忽略的点外键字段和引用字段的类型必须完全一致包括有无无符号属性。比如父表主键是INT UNSIGNED子表外键写INT建表直接报错Cannot add foreign key constraint。这种错误很隐蔽肉眼看不出来但错误信息不会明说类型不一致你只能去核对两边的字段定义。4.3 外键实操中的典型报错与处理外键最经典的报错有两个。第一个是Cannot delete or update a parent row: a foreign key constraint fails错误码1451。意思是你要删的父表记录已经有子表引用了。解决办法不是硬删而是先看清需求如果该删就先把子表数据删掉、或者改成ON DELETE CASCADE、或者先把外键临时关掉。这里特别提醒别轻易用“临时关掉外键检查”来硬删因为孤儿数据一旦生成后面查起来非常被动。第二个是Cannot add or update a child row: a foreign key constraint fails错误码1452。插入或更新子表时值在父表不存在。通常是你程序里并发问题或者是父表数据被其他会话删了。排查手段是先把SQL里的外键值拿出来去父表查一遍基本就能定位。临时关闭外键检查的语法SET FOREIGN_KEY_CHECKS 0; -- 执行你的删除/导入操作 SET FOREIGN_KEY_CHECKS 1;这个开关在mysqldump导入导出时尤其常用因为导出的数据可能是乱序的如果不关外键检查很容易因为父子表导入顺序不对直接报错。但生产环境千万别开着0跑业务那是自己给自己埋雷。5. 约束设计实战一个完整的业务表设计案例5.1 从业务规则反推约束清单设计表的约束最忌讳的是“抄模板”正确顺序应该是先列业务规则再定字段最后一条条对应约束。我们拿前面提到的学生课程成绩系统举例假设业务规则如下学生必须有姓名学号全局唯一且不能为空。性别只允许“男、女”默认男。学生年龄合理范围0到120默认0表示未知。课程名称不能为空学分默认1学分合理范围0到10。成绩分数范围0到100允许两位小数。成绩表里同一个学生同一门课只能有一条成绩联合唯一。删除学生或课程时关联的成绩记录一起删除。把规则转成约束就是这么一张对应表业务规则约束实现姓名必须填name VARCHAR(50) NOT NULL学号唯一且不为空student_no VARCHAR(20) NOT NULL UNIQUE性别限定CHECK (gender IN (男,女))DEFAULT 男年龄范围CHECK (age 0 AND age 120)DEFAULT 0课程名称必填course_name VARCHAR(100) NOT NULL学分范围与默认值TINYINT DEFAULT 1 CHECK (credit BETWEEN 0 AND 10)成绩范围CHECK (score 0 AND score 100)学生课程唯一UNIQUE KEY uk_stu_course (student_id, course_id)引用存在性两个外键ON DELETE CASCADE你会发现约束一旦梳理完表结构基本水到渠成根本不纠结。5.2 完整建表SQL与每一条约束的意图下面给一套可以直接跑的SQL字符集和排序规则也一并写上避免在Windows和Linux之间搬库出现乱码或比较问题DROP TABLE IF EXISTS score; DROP TABLE IF EXISTS course; DROP TABLE IF EXISTS student; CREATE TABLE student ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 学生内部主键, student_no VARCHAR(20) NOT NULL COMMENT 学号, name VARCHAR(50) NOT NULL COMMENT 学生姓名, gender CHAR(1) NOT NULL DEFAULT 男 COMMENT 性别, age INT NOT NULL DEFAULT 0 COMMENT 年龄, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1在读0离校, create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_student_no (student_no), CHECK (gender IN (男, 女)), CHECK (age BETWEEN 0 AND 120), CHECK (status IN (0, 1)) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 学生表; CREATE TABLE course ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 课程内部主键, course_no VARCHAR(20) NOT NULL COMMENT 课程编号, course_name VARCHAR(100) NOT NULL COMMENT 课程名称, credit TINYINT NOT NULL DEFAULT 1 COMMENT 学分, PRIMARY KEY (id), UNIQUE KEY uk_course_no (course_no), CHECK (credit BETWEEN 0 AND 10) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 课程表; CREATE TABLE score ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 成绩内部主键, student_id INT UNSIGNED NOT NULL COMMENT 学生ID, course_id INT UNSIGNED NOT NULL COMMENT 课程ID, score DECIMAL(5, 2) NOT NULL DEFAULT 0 COMMENT 分数, exam_date DATE NOT NULL DEFAULT (CURRENT_DATE) COMMENT 考试日期, create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 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 CASCADE ON UPDATE CASCADE, CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (id) ON DELETE CASCADE ON UPDATE CASCADE, CHECK (score BETWEEN 0 AND 100) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 成绩表;这里有几个细节值得展开说id为什么要用INT UNSIGNED学生表、课程表数据量都不会到几十亿INT够用但加上UNSIGNED可以杜绝“负数主键”这种低级问题而且容量翻倍。不过注意外键字段类型必须和父表完全一致所以score里的student_id、course_id也必须是INT UNSIGNED我上面已经保持了一致。score字段为什么用DECIMAL(5,2)而不是FLOAT浮点数的精度问题在成绩存储上是大忌用DECIMAL才能精确保存0到100之间的两位小数。成绩表的联合唯一键UNIQUE KEY uk_stu_course (student_id, course_id)保证了同一个学生对同一门课只允许一条成绩记录。这是业务规则中最容易漏的一条。exam_date默认值DEFAULT (CURRENT_DATE)用到了表达式默认值语法注意括号。这个建表SQL必须在MySQL 8.0.13及以上版本才能跑5.7会报语法错误。CHECK和DEFAULT结合很多刚入门的人只写了CHECK没写DEFAULT插入时由于strict模式可能报“字段没有默认值”错误。合理的做法是能给出安全默认值的字段尽量给默认值让INSERT语句只写最核心的字段。5.3 约束之间的配合与设计步骤清单好的约束设计不是孤立加条件而是让它们互相配合。比如上面例子中status字段用CHECK限制取值用DEFAULT给默认值用NOT NULL防止漏值年龄字段同理。而外键的ON DELETE CASCADE又把“学生-成绩”“课程-成绩”的联动关系挂在数据库层面而不是靠应用层写循环删除。我给自己总结的约束设计五步法分享出来列出所有业务规则能穷尽多少穷尽多少包括“默认状态”“取值范围”“唯一性要求”“父子表生命周期关系”。先定字段类型和是否允许NULL再谈约束。字段类型选错后面所有约束都像建在沙滩上。对每个字段问三个问题能不能为空必须有默认值吗取值范围是什么以此产出NOT NULL、DEFAULT、CHECK。找出“唯一性”粒度是单个字段唯一还是多个字段组合唯一产出UNIQUE或联合唯一索引。最后识别表与表之间的关系决定外键以及级联策略。注意外键字段必须在子表中建索引MySQL会自动建但如果你自己手工建过索引要注意索引顺序和字段匹配。6. 常见问题与排查技巧实录6.1 约束相关的报错代码速查表实战里我几乎每天都会在答疑群里看到各种约束报错这里整理一份速查表建议收藏错误码错误信息关键词触发约束排查方向1048Column xxx cannot be nullNOT NULL是否有程序传了NULL默认值救不了显式NULL1062Duplicate entry xxx for key uk_xxxUNIQUE / PRIMARY数据重复检查唯一键字段或者查历史数据是否包含NULL1264Out of range value for column xxx字段溢出数据超类型范围比如TINYINT存了1281451Cannot delete or update a parent rowFOREIGN KEY有子表数据引用父行按业务决定删子表还是用Set Null1452Cannot add or update a child rowFOREIGN KEY子表引用的父行不存在检查外键值3819Check constraint xxx is violatedCHECK查最新插入/更新语句哪一行违反CHECK条件1822Duplicate foreign key constraint nameFOREIGN KEY外键约束名重复约束命名也需要全局唯一意识其中1062这个错误在唯一索引包含NULL列时会有个陷阱MySQL唯一索引允许多个NULL值因为NULL不等于NULL。比如你在phone VARCHAR(20) UNIQUE上插两条NULL不会报错。很多人不知道这点以为唯一约束连NULL都管结果线上出现多行电话为空的用户。如果业务要求“手机号要么不填填了就唯一”直接加一个UNIQUE(phone)是不够的还要配合其他手段比如用空字符串代替NULL或者用生成列处理。6.2 如何快速查看一张表上到底有哪些约束表一多你不可能记住每一张表加了哪些约束尤其是别人交接过来的库。查看约束的办法有几种-- 方式一看建表语句最直观 SHOW CREATE TABLE score; -- 方式二查表级别约束信息 SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA 你的数据库名 AND TABLE_NAME score; -- 方式三查列级密钥使用情况 SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA 你的数据库名 AND TABLE_NAME score; -- 方式四MySQL 8.0.16可以查CHECK约束明细 SELECT TABLE_NAME, CONSTRAINT_NAME, CHECK_CLAUSE FROM information_schema.CHECK_CONSTRAINTS;写自动化巡检脚本时我基本靠方式二和方式四。比如公司规范要求“所有表必须有主键、所有金额字段必须有CHECK约束”就可以写一段SQL把这些元信息拉出来对比比一张张表肉眼检查靠谱得多。6.3 给已有表追加约束的正确姿势项目上线后才发现漏了约束是家常便饭。给已有表加约束最怕的是表里已经存在脏数据ALTER直接失败。所以我的流程一定是“先查脏数据再改结构”。以给成绩表加CHECK约束为例-- 第一步找出违反约束的数据 SELECT * FROM score WHERE score 0 OR score 100; -- 第二步开发票或删除脏数据 -- UPDATE score SET score 0 WHERE score 0; -- DELETE FROM score WHERE score 100; -- 第三步追加约束 ALTER TABLE score ADD CONSTRAINT chk_score_range CHECK (score BETWEEN 0 AND 100);给已有表加唯一约束同样要先查重-- 按联合字段分组找出出现次数大于1的 SELECT student_id, course_id, COUNT(*) FROM score GROUP BY student_id, course_id HAVING COUNT(*) 1;不排查重复直接加唯一索引会直接报1062而且ALTER会失败几乎不会损坏数据但会浪费你调试时间。另外在大表上执行ALTER TABLE加约束MySQL 8.0虽然支持在线DDL但最好还是选在低峰期执行避免长时间锁表影响业务。可以先用ALTER TABLE ... ALGORITHMINPLACE, LOCKNONE来评估是否能在线执行。还有个经验之谈MySQL 8.0里加CHECK约束时如果表里已有违反约束的数据ALTER会失败并报3819。如果只想“对以后数据生效历史脏数据暂时不处理”标准SQL里有WITH NOCHECK的选项但MySQL不支持。所以只能先把历史数据清理干净这是一条硬道理。6.4 约束命名规范和团队协作建议约束名字看起来只是小事但到了排查问题的时候约束名就是给你指路的牌子。我相信每个人都遇到过这种报错Duplicate entry 1 for key PRIMARY一眼就知道是主键冲突但如果报的是for key uk_xxx你不看表结构根本猜不到是哪个字段。所以我建议团队里定一个约束命名规范主键约束PRIMARY不用管。唯一约束uk_表名缩写_字段名比如uk_stu_course。外键约束fk_表名缩写_引用表名缩写比如fk_score_student。检查约束chk_表名缩写_规则简述比如chk_score_range。注意约束名在一个数据库里通常要求唯一尤其是外键约束名。经常有人从某个表复制建表语句改一改结果外键约束名忘记改直接报Duplicate foreign key constraint name。任何错误都不是偶然规范能让你少踩一半这种坑。最后再分享一个小技巧如果你在建表时吃不准某个约束到底能不能这么写最快的方式是开一个临时表做实验。CREATE TABLE tmp_test ...插入几条正常和异常数据看数据库报不报错。所有验证通过后再把建表语句固化进正式表。这比直接在生产库上试错要安全得多。约束这个东西设计时多花五分钟运维时能省五小时。
