做MySQL这些年接手过不少“看起来能跑、细看全是坑”的库十个里有八个问题出在建表时的约束设计上该唯一的没唯一该非空的默认允许NULL外键干脆就没建等线上数据一脏再想回头补代价翻好几倍。今天认真把mysql基础里的“约束”这部分讲透。需要先说明一下这里聊的是MySQL的CONSTRAINT就是主键、唯一、外键这些都算不是FPGA里的IO约束、时序约束也不是web.xml里那类schema校验约束别混淆。这篇适合刚入门的同学建立正确认知也适合那种建表全凭感觉、后来被脏数据折磨过的半路选手照着后面的实操和排查思路走能少踩很多坑。1. 约束的整体认知不是限制业务是给数据兜底1.1 约束和数据类型到底有什么区别很多人建表时只关注数据类型觉得INT就是整数VARCHAR(32)就够用了然后就开始写业务。这个认知不能算错但漏掉了一半关键设计。数据类型管的是“这一列能装什么形状的数据”INT不会让你塞进去一个字符串DATETIME不会让你存一个不存在的日期这是单列层面的基础体检。而约束管的是“整张表、多张表之间的规则”它告诉你这一列“不能为空”“不能重复”“必须存在于另一张表里”它体现的是业务规则不是存储格式。举个例子就明白了一个user表age字段你可以用INT或者TINYINT这是数据类型但“age必须大于0且小于150”这就是约束过去MySQL中容易被人忽略8.0.16之后才真正生效的CHECK约束能干这件事。同理phone字段用VARCHAR(20)没问题但“同一张表里不能出现两条相同手机号”的唯一约束才是真正防止重复注册的闸门。数据类型决定了一个人可以长成什么样约束决定了这个人说的话做的事必须符合规矩后者的重要性一点不比前者低。还有一个非常容易被低估的点约束是数据库层面的强制规则它不依赖任何应用端代码。我见过不少团队只在接口层做参数校验比如写入前用PHP、Java、Go各写一遍重复校验结果上线后发现漏了一个批量导入的入口脏数据照样灌进去。如果你在数据库上加了唯一约束、外键约束那不管数据是从APP、后台、管理端还是脚本批量任务进来的只要违反规则数据库一律拒绝。这才是约束最核心的价值——它是所有写入路径的共同兜底而不是某个业务模块的自觉。1.2 一张表看清六类核心约束MySQL里常用的约束说到底就这么几类我把它们的核心作用、场景和注意点整理成了一张表建议新人先整体过一遍再逐个击破。约束类型作用典型场景最容易踩的坑PRIMARY KEY唯一标识一行记录一张表只能有一个主键所有业务表的主键ID把有业务含义的字段当主键改业务字段时要连带改所有关联表NOT NULL字段不允许为空用户昵称、订单金额、状态字段该加非空约束的没加应用层到处出现None判断DEFAULT不填时自动给一个默认值创建时间、状态默认值、删除标记忘记设置导致插入时缺字段报错UNIQUE保证一列或组合列的值唯一手机号、身份证、订单号没搞清楚NULL不与NULL冲突插入了多条全NULL记录FOREIGN KEY保证子表引用的父表记录真实存在订单表关联用户表、明细表关联订单表高并发场景盲目使用引发性能瓶颈和锁竞争CHECK限制列的值必须满足条件年龄范围、状态枚举、金额非负8.0.16之前被语法解析但直接忽略有“假约束”嫌疑选型逻辑其实不复杂。拿一张新表问自己四个问题第一哪一列或者哪几列合在一起能唯一确定一条记录这决定主键怎么建。第二有没有业务上不允许重复的字段这决定要不要加唯一约束。第三哪些字段缺失会导致业务异常这决定NOT NULL和DEFAULT往哪里加。第四这张表和已有表之间有没有父子引用关系这决定外键的设计取舍。把这四个问题顺下来一张表的约束方案基本就成型了后面就是细节写法的问题。2. 核心约束逐个拆解从语法到应用场景2.1 主键约束选一个靠谱的“身份证”主键约束是表设计里优先级最高的一环它的语法很简单难的是选哪列当主键。最推荐的做法是使用自增ID或者类似逻辑主键而不是直接用手机号、身份证号、订单号这类业务字段。为什么我举一个踩过的真实案例。早期做过一个会员系统当时图省事直接用手机号做主键查询写起来确实爽后来产品出了个“用户自助修改绑定手机号”需求直接gg。手机号一改member表还好但关联的订单表、积分表、日志表全部要联动更新外键关系乱成一锅粥。那次之后我彻底明白主键必须是“无业务含义、稳定、单调”的字段它只负责在表内唯一标识一行记录不该承担任何业务语义。主键还有一个常被忽略的性质它会自动创建一个唯一索引。所以主键选择也直接影响查询性能。自增主键是B树顺序写入性能最友好UUID做主键如果规整成有序格式问题不大乱序UUID会导致叶子节点频繁分裂写入性能会有明显下降。另外联合主键虽然允许但列顺序要仔细考虑最常查询、区分度最高的列往前放这跟联合索引的优化原则完全一致。语法上建表时最简单的写法是CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, PRIMARY KEY (id) );如果表已经存在想追加主键ALTER TABLE user ADD PRIMARY KEY (id);有一点务必记住主键列不允许为NULL这一点不需要额外写NOT NULL也成立因为主键的定义就隐含了非空加唯一。删除主键也很少用但真要移除就是ALTER TABLE user DROP PRIMARY KEY;这个操作在大表上会锁表得选在低峰期做。2.2 非空约束与默认值约束数据质量的性价比之王这是我个人最想劝大家“用足”的两个约束。很多开发有个习惯建表时没有明确思路字段统统不加NOT NULL觉得“反正我有代码判断”。结果就是数据库里躺着一堆NULL查询时到处都是IS NULL的判断聚合函数COUNT、SUM、AVG遇到NULL时行为还各不相同应用层读到None还得做兜底转换苦不堪言。我的经验是几乎每个字段都应该有NOT NULL加DEFAULT除非这个字段确实具备“没有值”这种业务语义。字符串字段给默认空串数字字段给0时间字段给CURRENT_TIMESTAMP状态字段给一个明确初始值。这样做的好处是写入路径简单查询路径也简单你永远不需要面对“这个字段到底是Null还是空串”这种灵魂拷问。看一个经常用的写法CREATE TABLE order_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL COMMENT 业务订单号, user_id INT UNSIGNED NOT NULL COMMENT 用户ID, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2已取消, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单总金额, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) );这里update_time用ON UPDATE CURRENT_TIMESTAMP是MySQL一个很实用的小特性行数据一旦发生UPDATE这个字段会自动刷新比在应用层手动维护更新时间方便太多。另外注意MySQL 8.0中DATETIME类型已经支持DEFAULT CURRENT_TIMESTAMP不需要再纠结用TIMESTAMP才有这个能力了。2.3 唯一约束保住业务规则的“纪律委员”唯一约束通常加在业务上不允许重复的列上比如手机号、身份证号、订单号、流水号。它的语法类似CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, phone VARCHAR(20) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone) );很多人在这个环节容易踩一个隐蔽的坑唯一约束不会拦截多条NULL。因为InnoDB的规则是“NULL不与任何值相等包括NULL自己”所以phone VARCHAR(20)就算加了唯一约束你照样能插入十行phone为NULL的数据。要真正实现“手机号不允许重复且必填”必须配合NOT NULL一起使用。这也是我为什么一直强调约束和约束之间是配合关系单条约束只是局部规则组合起来才算完整的业务规则。组合唯一约束也很有用。比如一个签到表业务要求“同一个用户一天只能签到一次”那就可以建CREATE TABLE sign_log ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, sign_date DATE NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_user_date (user_id, sign_date) );这里(user_id, sign_date)联合唯一就保证了同一用户同一天不会出现两条签到记录。这种约束在幂等写入场景中特别好用配合INSERT ... ON DUPLICATE KEY UPDATE可以写出非常简洁的“有则更新、无则插入”逻辑我在做数据同步和推送任务时经常这么干。还有一点要提醒唯一约束会自动创建索引所以它不仅约束数据还会影响查询效率。你给哪列加唯一约束相当于默认也给这列建了一个索引但这不意味着可以无限加表上唯一索引太多写入时每次都要去查一遍索引确认唯一性写放大会很难看这问题后面专门开一节细说。2.4 外键约束双刃剑用之前想清楚业务场景外键约束保证的是两张表之间的引用完整性。比如一张orders表的user_id只能引用customer表里真实存在的id这是从数据模型上杜绝“孤儿订单”。建外键的基本语法是CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, amount DECIMAL(10,2) NOT NULL DEFAULT 0, PRIMARY KEY (id), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES customer (id) );外键还能定义引用动作最常用的是ON DELETE CASCADE和ON DELETE SET NULL。前者当你删除父表记录时子表关联记录一并删除适合“订单明细跟着订单删”的场景后者是把子表外键字段设为NULL适合“用户被删但订单需要保留”的场景。另外还有RESTRICT和NO ACTION意思都是“有子表引用时禁止删除父表记录”这也是默认行为用来保护历史数据不丢。但这里必须说一句得罪同行的实话外键在互联网高并发系统里是不被推荐的这个“不推荐”不是外键本身垃圾而是场景匹配问题。外键在每次DML操作时都要做引用完整性检查会额外产生锁和开销写入频繁时这个成本会被明显放大一旦分库分表外键关系在物理上就断裂了只能靠应用层去维护。所以你会看到很多大厂规范直接写“禁用外键”做报表系统、数据分析平台时更是基本看不到外键大家都改用应用层校验加定时任务兜底。那外键就完全不用了吗也不是。如果你做的是内部管理系统、ERP、进销存这类并发量不大、但数据一致性要求极高的系统外键反而是最好的保证。我在做一个小型订单系统时就用了外键虽然写入时有一点额外开销但省掉了一大堆“删除客户后发现还有一堆孤儿订单”的历史包袱实时一致性带来的安全感是无价的。外键选择本质是一个工程权衡题没有绝对的对错只有合不合适的场景。2.5 CHECK约束与自增约束功能差异不小的细节CHECK约束在MySQL里有个特别坑的历史8.0.16之前MySQL虽然接受CHECK子句的语法却只是解析一下然后直接忽略不会真正做校验。也就是说你在一张老版本MySQL上建了CHECK (age 0)数据照样能插入负值这个“假约束”骗过了很多开发。从8.0.16开始MySQL终于真正支持了CHECK约束条件不满足时插入会报错这才算有了和其他数据库一样的能力。现在的用法就很正常了CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, age TINYINT UNSIGNED NOT NULL, status ENUM(active,disabled) NOT NULL DEFAULT active, PRIMARY KEY (id), CONSTRAINT chk_age CHECK (age BETWEEN 0 AND 120) );如果你还在用老版本MySQL并且已经在建表语句里写了CHECK我劝你赶紧去线上测一下该约束是不是真的在生效。我之前排查过一个诡异问题程序里年龄数据乱写服务层校验拦了一部分还有一部分从批量脚本写入就直接进了库后来一查就是老版本MySQL的CHECK被静默忽略所谓校验根本就是个摆设。自增约束AUTO_INCREMENT虽然不算标准约束但因为和主键绑定太紧密一起说掉。它要求字段必须是索引列通常就是主键并且是数值类型。它的特点是只增不减删除最大行之后再插入新行一般不会复用被删除的自增值这是为了保证并发环境下ID不被错乱分配。还有一个容易忽略的点事务回滚时自增ID也会被消耗掉这会导致ID之间出现空洞这是正常现象别试图去补洞补洞反倒可能引发主键冲突。如果非要自增从一个指定值开始可以用ALTER TABLE user AUTO_INCREMENT 1000;这在初始化分库分表区间时非常有用。3. 约束实操全流程建表、变更与元数据查看3.1 完整案例从零设计一张带全套约束的用户订单表光讲单个语法不够过瘾我们从头设计一个尽量完整的场景一张customer客户表、一张orders订单表两者之间有外键引用订单里有唯一约束防重复支付单有时间字段的默认值有金额的CHECK约束。下面是完整的建表语句。-- 客户表 CREATE TABLE customer ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 客户ID, phone VARCHAR(20) NOT NULL COMMENT 手机号, real_name VARCHAR(50) NOT NULL COMMENT 客户姓名, level TINYINT NOT NULL DEFAULT 1 COMMENT 客户等级 1普通 2黄金 3钻石, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT客户表; -- 订单表 CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 订单ID, order_no VARCHAR(64) NOT NULL COMMENT 订单唯一编号, customer_id INT UNSIGNED NOT NULL COMMENT 关联客户ID, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单总额, pay_status TINYINT NOT NULL DEFAULT 0 COMMENT 0未支付 1已支付 2已退款, pay_time DATETIME NULL COMMENT 支付时间, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_customer_id (customer_id), CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customer (id), CONSTRAINT chk_amount CHECK (total_amount 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;这个表结构里要注意三个细节。第一我手动给customer_id单独建了一个idx_customer_id索引。虽然外键定义时会自动检查并且创建索引但在一些版本和引擎组合下显式声明索引更稳妥同时这个索引正好用来加速“查某客户的所有订单”这类高频查询。第二pay_time用了NULL这是合理的因为订单未支付时“支付时间”确实不存在这不是偷懒而是业务语义如此。第三外键名、唯一索引名都给了清晰可读的名字比如fk_orders_customer、uk_order_no这些名字在后续排查和删除约束时会非常有用别让数据库给你随机生成一堆莫名其妙的名字。3.2 表结构已经存在时如何追加和移除约束多数情况我们是在建表之初就把约束设计好但免不了遇到表已经上线、要补约束的场景。核心语法就是ALTER TABLE来逐个过一遍。追加主键前提是原表里没有重复数据ALTER TABLE customer ADD PRIMARY KEY (id);追加唯一约束ALTER TABLE customer ADD UNIQUE KEY uk_phone (phone);追加外键约束ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customer (id);追加CHECK约束ALTER TABLE orders ADD CONSTRAINT chk_amount CHECK (total_amount 0);给字段追加非空约束本质是MODIFY列定义ALTER TABLE customer MODIFY COLUMN real_name VARCHAR(50) NOT NULL;移除约束的语法也放这里-- 删除主键 ALTER TABLE customer DROP PRIMARY KEY; -- 删除唯一索引 ALTER TABLE customer DROP INDEX uk_phone; -- 删除外键 ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer; -- 删除CHECK约束 ALTER TABLE orders DROP CHECK chk_amount;这里有个非常关键的经验在线为大表添加约束之前务必先评估数据量和表锁影响。ALTER TABLE在加约束时往往需要扫描全表数据做校验比如加唯一约束时检查重复值大表上可能执行很久。虽然MySQL 8.0提供了一些InnoDB在线DDL特性某些操作可以做到INSTANT算法但并不是所有约束变更都能走这个算法加唯一约束会建索引肯定是要扫描数据的。我建议的操作顺序是低峰期执行、先备份、预估执行时间、准备好回滚方案。生产环境千万别手一抖就在业务高峰期补约束我见过一次加唯一约束把主库卡爆的现场记忆犹新。3.3 怎么查看当前表上都有哪些约束排错和核对结构时最常用的命令就是SHOW CREATE TABLE它会把建表语句原样打出来主键、唯一约束、外键、CHECK全在里面一目了然。SHOW CREATE TABLE orders;如果想更结构化的查询MySQL的元数据表也记录得很清楚。information_schema.TABLE_CONSTRAINTS可以看到表上有哪些约束类型information_schema.KEY_COLUMN_USAGE可以看到约束涉及哪些列information_schema.REFERENTIAL_CONSTRAINTS专门记录外键的引用规则。例子如下-- 查看orders表所有约束 SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA 你的库名 AND TABLE_NAME orders; -- 查看orders表所有外键引用关系 SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA 你的库名 AND TABLE_NAME orders AND REFERENCED_TABLE_NAME IS NOT NULL;还有一条实用命令是SHOW INDEX FROM orders;注意从MySQL 8.0开始不能用\G之外的旧版FROM语法写可选FROM子句但基础用法没问题。它能显示出所有索引而主键和唯一约束本质上都会体现为索引所以看索引列表也能反推约束情况。排查外键失效、约束丢失之类的问题时这三条查询能很快帮你定位。4. 约束踩坑实录高频问题与排查思路4.1 外键创建失败的典型原因外键是约束里出错率最高的一个报错经常是Cant create table xxx (errno: 150)或者直接抛Foreign key constraint is incorrectly formed让人摸不着头脑。我把常见原因列出来方便对号入座。首要原因是被引用的父表字段不是索引或者不是主键MySQL要求外键引用的列必须是索引列且引用的列类型必须和子表列类型完全一致。这个“完全一致”包括字符集和排序规则比如父表字段是utf8mb4_0900_ai_ci子表字段是utf8mb4_general_ci看着都能存中文但外键就是建不上。另一个隐蔽原因是INT和INT UNSIGNED的区别父表主键是无符号整型子表外键字段忘了加UNSIGNED类型判定不一致直接拒绝创建。还有小伙伴问我为什么用MyISAM建外键报错答案是MyISAM引擎本身不支持外键必须用InnoDB。排查时先确认两张表的引擎再确认字符集再确认字段类型基本能解决九成问题。定位问题的进阶招数是用这个查询看看外键有没有真的建成功SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA 你的库名;如果建表时报错、查不到记录说明外键压根没落地。还有一种情况是外键建成功后子表插入一条customer_id在父表中不存在的记录报错是Cannot add or update a child row: a foreign key constraint fails这不是结构问题是数据问题用一条SELECT查一下父表有没有对应ID即可确认。4.2 唯一约束和NULL值的关系很多人栽在这前面提到过唯一约束不限制多行NULL这里再展开一个实际场景。比如在做用户表时设计了一个email字段业务逻辑是“没有填邮箱的用户可以注册但填了的不能重复”。这种情况下给email加唯一约束是合理的因为没填的用户实际会是NULL可以多条共存填了邮箱的用户会被拦截重复。这其实是灵活应用了“NULL不与任何值冲突”的特性。但如果你想表达“邮箱必填且不能重复”那必须加NOT NULL再加UNIQUE。我实际排查过一个案例运营后台可以创建客户但客户邮箱列是空的结果同一个空值客户被创建了十几个代码里已经做了“先查重再插入”架不住并发请求同时通过查重最终靠数据库唯一约束和NOT NULL组合才兜住。用一个小表格总结这个知识点表设计两条email为NULL的记录两条email相同的记录email VARCHAR(100)允许允许email VARCHAR(100) UNIQUE允许正是坑点拒绝email VARCHAR(100) NOT NULL UNIQUE拒绝拒绝所以业务上要求“空值也算重复”时通常不能依赖NULL要在应用层把空串转成NULL、或者在插入前统一赋默认值再靠唯一约束拦截这一步需要根据业务灵活处理。4.3 约束带来的性能陷阱索引与写放大主键、唯一约束、外键都会自动建索引这些索引在查询场景是加分项但在写入场景就是实打实的成本。每插入一条记录所有唯一索引都要查一遍确认没有重复每多一个唯一索引写入就多一次索引查找如果表上有五六个唯一索引写入性能会被拖得很明显。外键也一样每次UPDATE或DELETE父表记录都要检查所有子表引用行锁范围会被放大。这里我建议控制约束数量。业务上真正需要唯一性的字段通常就一两个什么字段都加唯一约束看着数据安全实际上是把数据库的性能白白烧在无用校验上。另外一个技巧是如果一个字段加了唯一约束又被经常查询那这个字段的索引就已经存在了你去建普通索引就是重复造轮子直接复用唯一约束的索引即可。比如orders表的order_no做了唯一约束那按order_no查单子就走这个索引不需要再单独加idx_order_no。这个复用逻辑很多人没注意到白白多建一堆冗余索引。4.4 DML操作常见冲突报错速查表平时和约束相关的报错集中在插入、更新、删除三个阶段我把它们整理成速查表争取让读者看完之后遇到类似报错不慌。报错信息触发场景常见解决思路Duplicate entry 1001 for key PRIMARY插入重复主键确认ID策略改自增或使用INSERT IGNORE/ON DUPLICATE KEY UPDATEDuplicate entry 13800138000 for key uk_phone插入重复唯一值业务上应提示“手机号已注册”或做幂等更新Column real_name cannot be null违反NOT NULL约束检查写入数据给字段补充默认值Field status doesnt have a default value插入时没给字段赋值且无默认值调整DEFAULT设置或显式赋初始值Cannot add or update a child row外键子表引用不存在的父记录先插入父记录或检查父表ID是否被删Cannot delete or update a parent row删除父表记录但子表仍在引用确认级联规则先删/改子表或改外键ON DELETE规则Check constraint chk_amount is violated违反CHECK约束检查写入值是否满足条件范围批量导入数据时我建议先把约束检查严格性问题处理掉。如果导入的源数据有少量脏数据可以先在临时表里清洗再用INSERT IGNORE或ON DUPLICATE KEY UPDATE做幂等导入避免一条脏数据让整个批量任务中断。也可以用SHOW WARNINGS查看被忽略的具体原因定位哪些行没插进去。最后再分享一个用约束做幂等写入的通用模板在同步场景非常好用INSERT INTO customer (phone, real_name, level) VALUES (13800138000, 张三, 1) ON DUPLICATE KEY UPDATE real_name VALUES(real_name), level VALUES(level);这里的幂等依赖就是phone列上的唯一约束没有它ON DUPLICATE KEY UPDATE就不知道该按哪个键去匹配。所以约束不光是数据的“纪律委员”更是很多高级写法的前提这一层认知我觉得比单纯背几个语法有价值得多。