前阵子有个朋友让我帮忙看一张线上用户表表里有二十多万行数据问题却很扎眼同一个手机号能注册出三个账号性别字段一半是空的订单表里甚至出现过负数金额。我打开建表语句一看干干净净的几行字段定义一个约束都没写。如果你对MySQL建表还停留在“能跑就行”的阶段那么“表的约束”这四个字值得好好研究一下。约束不是什么高深理论它就是数据库在写入数据之前给你设的几道关卡把脏数据挡在门外。这篇内容我会从约束的本质讲起把六大约束逐个拆开再用一张完整的订单表带你从零设计到落地最后附上高频报错的排查思路。适合刚接触MySQL的同学也适合那些建表全凭感觉、等到线上出问题才开始头疼的开发。1. 约束到底在解决什么问题先想清楚这三件事先说个最直观的场景。你去超市买东西收银员打出的小票上必然有几个关键信息商品名称、单价、数量、总价、交易时间、单号。你会默认这张小票不会出现单价是负数、交易时间是2月30号、单号连续重复的情况。这些“默认不会出现”落到数据库里就是约束。数据库里存的数据比小票复杂得多如果没有规则约束业务代码里漏掉一个判断垃圾数据就会钻进表里而且一旦进去了就很难清理。约束的本质可以归纳为三句话字段的值不能瞎填、每一行都得能区分、表与表之间的关系不能乱。展开说就是数据库完整性理论里的三个层面。域完整性管的是字段取值比如年龄不能是负数、状态只能在一组枚举值里选实体完整性管的是记录本身的唯一性每一行都要有能标识自己的主键引用完整性管的是表之间的关系订单里引用的用户ID必须是真实存在的用户。MySQL提供的NOT NULL、DEFAULT、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK这六大约束恰好把这三种完整性全部覆盖了。1.1 数据库完整性模型域、实体、引用三层域完整性听起来抽象其实对应的是SQL里的“域”概念。你可以把一列理解成一个小区每个字段值就是一个住户小区规定“这个位置只能住符合条件的人”这就是域约束。比如生日字段设计了DATE类型那就不能写入2023-02-30这种不存在的日期数量字段设计了INT就不能写入“三斤”这种文本。NOT NULL和DEFAULT是最基础的域约束CHECK约束则是更灵活的域规则。实体完整性对应的是“行”这个维度。一张表里如果两行数据根本无法区分那这张表就没有意义。主键是实体完整性最典型的手段它要求每一行都有唯一标识而且这个标识不能为空。UNIQUE约束是主键的补充用来约束那些业务上不允许重复但又不适合做主键的字段比如身份证号、订单编号。引用完整性关注的是表与表之间的“外键关系”。从表里存的外键值必须在主表里有对应记录。这个概念可以类比成核酸采样管和人员信息的关系采样管条形码指向的必须是一个真实登记过的人不能指向一个不存在的记录。没有引用完整性就很容易出现孤儿数据比如订单指向一个已被删除的用户。1.2 MySQL的六大约束体系一张表看清全部手段MySQL把约束分成了两大类列级约束和表级约束。列级约束写在字段定义后面只管当前这一列表级约束独立写在所有字段定义之后可以同时关联多列。下面这张表把六大约束的作用范围和使用阶段整理清楚了。约束类型分类核心作用对应完整性类型NOT NULL列级字段值不允许为空域完整性DEFAULT列级未显式赋值时使用默认值域完整性UNIQUE表级/列级字段或多个字段组合不允许重复实体完整性PRIMARY KEY表级/列级每行数据的唯一标识隐含NOT NULL实体完整性FOREIGN KEY表级字段值必须存在于关联表引用完整性CHECK表级/列级字段值必须满足指定表达式用户定义完整性理解这张表之后你会发现约束其实不复杂。设计表的时候你只需要对着每个字段问三个问题这个字段能空吗、能重复吗、取值范围有要求吗。三个问题问完该用哪些约束基本就定下来了。2. 六大约束逐一拆解语法、原理与坑纸上谈兵没有意义接下来我把六大约束逐个拆开每个都给出语法示例并重点说说那些文档里不会写清楚的坑。2.1 NOT NULL与DEFAULT空值与默认值的组合拳NOT NULL是最好理解的约束意思就是这一列不允许存NULL。但很多新手对NULL本身有误解。NULL不是0不是空字符串它的语义是“未知”。在数据库的三值逻辑里NULL参与任何比较运算结果都是UNKNOWN而不是TRUE或FALSE。所以一张表里如果大量字段允许NULL查询的时候就要处处小心一个WHERE条件没写对NULL数据就会被悄悄漏掉。DEFAULT约束负责兜底。当INSERT语句没有显式指定某一列的值时数据库自动写入DEFAULT定义的值。MySQL里最常用的写法是给时间列设置默认值比如CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP );这么写的好处是应用层插入数据时完全不用管创建时间数据库自动填当前时间。在MySQL 8.0.13之前的版本DEFAULT后面只能接常量8.0.13之后支持带括号的表达式比如DEFAULT (UUID())但注意必须加括号。实际开发里最常见的需求是“mysql设置默认值为0”。比如订单状态字段希望不传时默认是0可以这样写ALTER TABLE orders ALTER COLUMN status SET DEFAULT 0;或者用MODIFY整列重定义ALTER TABLE orders MODIFY COLUMN status TINYINT NOT NULL DEFAULT 0;这里有一个非常典型的坑如果一张表里的某个字段已经存在NULL数据你想直接给它加NOT NULL约束会直接报ERROR 1138。数据库不会帮你猜NULL应该替换成什么值你得先手动把历史数据处理掉再改约束。这也是为什么我一直强调表结构设计应该在业务上线前完成而不是等数据脏了再补救。2.2 UNIQUE唯一约束为什么“唯一”仍允许重复的NULLUNIQUE约束保证一列或者多列组合的值不重复。语法比较简单列级写法是字段后面直接跟UNIQUE表级写法是独立的UNIQUE KEY语句CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, phone VARCHAR(20) UNIQUE, email VARCHAR(100), UNIQUE KEY uk_email (email) );UNIQUE约束最反直觉的地方在于NULL可以重复。原因还是三值逻辑数据库判断“是否重复”时两个NULL互相比较的结果是UNKNOWN不判定为冲突。所以上面这张表你可以插入100行phone为NULL的数据不会报唯一冲突。这一点在很多业务场景里会造成错觉比如你以为手机号字段加了唯一约束就不会有脏数据但用户不填手机号时NULL记录可以无限堆积。复合唯一约束是UNIQUE最有价值的使用场景。比如选课表里每个学生每门课只能选一次就应该建立UNIQUE KEY uk_student_course (student_id, course_id)。这样即使单看student_id有重复、course_id也有重复组合起来也不会重复。UNIQUE约束和唯一索引的关系也需要说清楚。UNIQUE约束在InnoDB里本质上就是创建一个唯一索引约束是语义层的东西索引是实现层的载体。所以在Navicat这类图形工具里你会发现添加唯一索引和添加唯一约束的操作是同一个地方。修改唯一约束的SQL也很简单先删索引再建索引ALTER TABLE users DROP INDEX uk_email; ALTER TABLE users ADD UNIQUE KEY uk_email (email);2.3 PRIMARY KEY主键一张表只能有一个身份标识主键是表设计里最重要的一个约束。它隐含了两层意思值不能为NULL值不能重复。这相当于NOT NULL和UNIQUE的组合但主键在InnoDB里有更特殊的地位——它是聚簇索引的入口。表里的数据按主键顺序物理排列通过主键查询可以直接定位到数据行不需要回表。这也是“辅助索引如何避免回表”这个问题的关键想避免回表要么直接用主键查询要么让辅助索引覆盖你需要的所有字段。设计主键有几点建议。第一用自增整数主键最省心INT AUTO_INCREMENT或者BIGINT AUTO_INCREMENT写入性能好索引占用空间小。第二不要用业务字段做主键比如手机号、身份证号这类字段虽然唯一但会有变动风险一旦改了主键值所有引用它的表都要跟着改。第三不要用超长字符串做主键聚簇索引会复制主键到每一个二级索引里主键越长所有索引占用的空间就越大。复合主键在业务表里偶尔会出现比如关系表里用两个ID联合作为主键。但复合主键会带来一个隐患只要其中一个字段的值需要修改就得先删旧行再插新行。所以现在很多设计宁可加一个自增ID做主键再把业务上唯一的组合用UNIQUE约束解决。一张表只能有一个主键这一点不需要纠结。如果建表时忘记设置主键InnoDB会先找一个非空唯一索引作为聚簇索引的替代品实在找不到就生成一个隐藏的ROW_ID。但隐藏主键对开发者不可见查询效率也打了折扣还是老老实实显式定义更稳妥。2.4 FOREIGN KEY外键引用完整性由谁来守外键约束用来保证子表某列的值必须存在于父表被引用列里。先看一个标准建表语句CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) );这个约束的意义在于插入订单时user_id必须是users表里真实存在的ID删除users表里的用户时如果这个用户还有订单删除操作会被阻止除非你定义了级联规则。外键的级联行为有五种CASCADE表示父表删除或更新时子表同步操作SET NULL表示父表变更时子表对应列置空RESTRICT和NO ACTION都表示存在关联记录时拒绝操作SET DEFAULT在InnoDB里会被解析但实际不生效。外键是把双刃剑。好处是数据库层面保证了引用完整性应用层少写很多校验逻辑。坏处也很明显每次插入子表数据都要去父表做一次存在性检查并且要给父表对应行加共享锁在高并发场景下这会让写入变慢还容易引发锁等待。微服务架构拆分数据库之后跨库外键根本没法建大家普遍就把外键校验挪到应用层了。如果你的项目决定不用外键至少要在子表的外键列上建普通索引。因为按user_id查询订单是很高频的操作没有索引就是全表扫描。用外键的时候InnoDB会帮你自动建索引不用外键就要记得手动建。外键建不上最常见的报错是ERROR 1005。报错提示看起来像是语法问题实际十有八九是这几种情况父表被引用列没有索引、子表外键列和父表被引用列的数据类型不一致、或者表里已有数据违反外键约束。2.5 CHECK约束MySQL从形同虚设到真正执法CHECK约束的历史比较曲折。MySQL 8.0.16之前CHECK子句会被数据库解析但直接被忽略掉不会做任何校验。也就是说你写了CHECK表照样建成功但脏数据也照样能进去。这导致很多老教程里直接告诉你“MySQL不支持CHECK约束”这个说法在8.0.16之后已经过时了。现在的CHECK约束是真正执行的CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, age INT NOT NULL, CONSTRAINT chk_age CHECK (age 0 AND age 150), CONSTRAINT chk_status CHECK (status IN (0, 1, 2)) );CHECK的表达能力比ENUM强很多。ENUM只能列一组固定取值而且修改枚举值需要重建表CHECK可以做范围判断、逻辑运算、多列联合判断。比如订单金额必须大于等于0用ENUM根本做不到CHECK一句话就搞定。年龄、金额、状态、评分这类字段只要业务规则稳定用CHECK做的收益很直接。用CHECK有几个需要注意的点。第一CHECK不会阻止NULL写入除非约束表达式里显式包含 IS NOT NULL 判断。第二8.0.16以下版本不要指望CHECK需要靠应用层校验兜底。第三对已有大量数据的表加CHECK约束要谨慎只要历史上存在一条违反约束的数据ALTER TABLE就会失败。3. 实操从需求到完整DDL手把手设计一张订单表前面讲了理论这一节我们拿一张真实的订单表练手。目标是设计一张电商订单表能承载用户下单、查询订单、修改状态、数据统计这些基本需求同时把该有的约束全部设计进去。3.1 需求拆解一张业务表需要哪些约束拿到需求先别急着写CREATE TABLE把每个字段的业务规则列清楚。这张orders表大概需要这些字段主键ID、订单编号、用户ID、订单金额、订单状态、备注、创建时间、更新时间。逐个分析约束需求。订单编号order_no是用户能看到的核心业务字段它在整个表里不能重复但订单编号不是自增ID而是由业务规则生成的比如时间戳加随机数。所以这个字段不适合做主键但必须加UNIQUE约束。用户ID user_id引用users表的主键。如果项目是中小规模直接加外键最省心如果已经确定要分库分表那就只建普通索引不加外键校验交给应用层。订单金额amount是业务的关键数据不允许为负数用CHECK约束 amount 0 挡住异常数据。金额用DECIMAL(10,2)不要用FLOAT或者DOUBLE浮点数存金额会有精度问题这是老生常谈的坑。订单状态status用TINYINT类型0代表待支付1代表已支付2代表已取消3代表已退款。用CHECK约束限制取值必须在这些范围内。有人喜欢直接存字符串但TINYINT占用空间小、查询快配合注释完全可以。创建时间created_at和更新时间updated_at都交给数据库维护用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP应用层完全不用传值。备注remark是唯一一个真正允许NULL的字段。用户可能不写备注用NULL表示“没有备注”是合理的不需要给默认空字符串。3.2 完整建表SQL与逐字段解析下面是完整DDL你可以直接复制改一改用到自己项目里。CREATE TABLE orders ( id BIGINT UNSIGNED AUTO_INCREMENT COMMENT 主键ID, order_no VARCHAR(40) NOT NULL COMMENT 订单编号, user_id BIGINT UNSIGNED NOT NULL COMMENT 用户ID, amount DECIMAL(10,2) NOT NULL COMMENT 订单金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态 0待支付 1已支付 2已取消 3已退款, remark VARCHAR(255) NULL COMMENT 备注, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT chk_amount CHECK (amount 0), CONSTRAINT chk_status CHECK (status IN (0, 1, 2, 3)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT订单表;逐条说明这样设计的理由。主键选BIGINT UNSIGNED自增订单表数据量增长快INT最大只能到21亿多而订单表动辄上亿行BIGINT更稳。order_no加唯一索引是为了应对用户查询订单编号的场景也让业务上“一个订单号只能有一条记录”的规则落到数据库层面。user_id加普通索引是因为按用户查订单是最常见的查询条件这里加了外键后索引其实会自动生成但显式写出来更清晰也方便以后取消外键时保留索引。amount的CHECK约束和status的CHECK约束是数据库层的最后防线。应用层校验写漏了数据库还能兜底。created_at和updated_at用默认值省去应用层维护时间的麻烦。如果你用的MySQL版本低于8.0.16CHECK约束不会生效这属于版本约束需要知晓并提前在应用层补校验。3.3 约束变更与Navicat操作实录表上线之后难免要调整约束。最常见的两个操作是给已有表加唯一约束和修改默认值。给已有表加唯一约束的SQL很简单但执行前一定要先查数据。假如你要给user_phone加唯一约束先跑一句分组查重SELECT phone, COUNT(*) FROM users GROUP BY phone HAVING COUNT(*) 1;有重复数据时直接加约束会报ERROR 1062。处理完重复数据后再执行ALTER TABLE users ADD UNIQUE KEY uk_phone (phone);Navicat里设置唯一约束的路径是鼠标右键表名选“设计表”切到“索引”选项卡点击“添加”名字填uk_phone字段选phone索引类型选“UNIQUE”保存执行。注意“普通”和“UNIQUE”的区别普通索引允许重复UNIQUE索引才带唯一约束。设置默认值为0的两种方式前面提过Navicat里更简单。设计表界面找到目标字段在“默认”那一栏直接填0保存即可。如果字段本身是NOT NULL且没有默认值加默认值之后老数据的显示不受影响因为默认值只对新增数据生效。大批量修改约束有一个必须注意的点MySQL 8.0之前的ALTER TABLE大概率会锁表数据量大时可能把业务堵死。生产环境执行结构变更前先确认版本8.0.12之后的版本才支持INSTANT算法快速加列但加约束这种操作仍然可能需要重建表最好在低峰期执行或者用gh-ost这类在线变更工具。4. 建表异常与线上问题排查实录这一节把建表过程中最常见的报错整理成速查表每个都给出排查思路和解决办法。这些报错我在工作里都遇到过有些是开发环境踩的有些是在生产环境大半夜排查的。4.1 高频建表报错速查表报错信息典型场景根本原因排查思路ERROR 1067 Invalid default value给日期列设置默认值默认值格式与字段类型不匹配比如DATE列默认0000-00-00检查字段类型和默认值格式TIMESTAMP用CURRENT_TIMESTAMPERROR 1005 Cant create table创建外键失败父表被引用列无索引、字段类型不一致、已有数据违反约束查看SHOW ENGINE INNODB STATUS对比两个表的字段定义ERROR 1170 BLOB/TEXT column used in key specification给超长字段加唯一约束utf8mb4下VARCHAR(255)做索引键超过767字节限制缩小字段长度或改用前缀唯一索引ERROR 1138 Invalid use of NULL value给含NULL数据的列加NOT NULL历史数据里有NULL值数据库无法自动处理先UPDATE填充合法值再加NOT NULLERROR 1062 Duplicate entry插入数据违反唯一约束表中已有相同值查询重复数据保留合法行删除或合并重复行ERROR 3819 Check constraint violatedINSERT或UPDATE违反CHECK约束写入值不满足CHECK表达式检查写入值确认应用层校验和数据库约束一致ERROR 1170这个坑在utf8mb4普及之后很常见。utf8mb4字符集下VARCHAR(255)的字节数是255乘以4等于1020字节超过旧版本InnoDB的767字节索引限制建唯一索引时会直接报错。解决办法一是把字段长度缩短到190以内二是用前缀索引但前缀索引在唯一约束场景下语义会变可能把两行前N个字符相同但后面不同的数据也判定为重复需要非常谨慎。4.2 唯一约束冲突与数据修复场景还原线上users表已经跑了半年手机号字段出现大量重复现在要加唯一约束。先别急着执行ALTER TABLE按这套流程来。先查出所有重复的手机号看清楚分布SELECT phone, COUNT(*) AS cnt FROM users WHERE phone IS NOT NULL GROUP BY phone HAVING COUNT(*) 1;假设手机号13800138000有3条记录需要决定保留哪条。通常保留最早创建的那一条其他记录更新手机号为NULL或者合并业务数据。更新时用子查询锁定最小IDUPDATE users SET phone NULL WHERE phone 13800138000 AND id NOT IN ( SELECT id FROM ( SELECT MIN(id) AS id FROM users WHERE phone 13800138000 ) tmp );处理完所有重复数据后再加唯一约束。加完约束后插入重复手机号会直接报错ERROR 1062。实际业务中如果确实需要对重复数据做“存在则更新”的幂等插入有三种写法INSERT IGNORE、ON DUPLICATE KEY UPDATE、REPLACE INTO。但要分场景。INSERT IGNORE会静默忽略冲突适合批处理ON DUPLICATE KEY UPDATE可以在冲突时更新指定字段适合业务幂等REPLACE INTO会先删旧行再插新行副作用是主键会变不适合有外键关联的表。4.3 约束对性能的影响与批量导入技巧约束是保护伞同时也是开销来源。唯一约束在写入时要立刻检查索引是否存在相同值外键约束要查父表并加锁CHECK约束要逐行求值表达式。平时流量不大感觉不到做批量数据导入或者大促数据回填时性能差异就非常明显。批量导入前可以临时关闭外键检查SET FOREIGN_KEY_CHECKS 0;导入完成后再打开SET FOREIGN_KEY_CHECKS 1;关掉外键检查可以让导入快很多因为省掉了逐行校验父表的过程但前提是你自己确保数据质量。注意这个设置只在当前会话生效不影响其他连接。还有一类问题是批量导入时唯一约束冲突导致整个事务中止。解决办法是先导入到临时表做一遍去重清洗再用INSERT INTO ... SELECT把干净数据导进正式表。约束和主从复制的关系也值得提一句。我遇到过从库建表时多了一个唯一约束主库正常写入但同步到从库时复制线程直接中断报错Duplicate entry。这种情况排查时看SHOW SLAVE STATUS\G里的Last_SQL_Error字段定位具体SQL然后对比主从两边的SHOW CREATE TABLE。根本思路是保持主从表结构完全一致尤其是约束定义。如果不一致宁可重建从库也不要手工跳过错误否则数据偏差会越来越大。5. 我踩过的约束设计坑几条长期有效的建表习惯老规矩最后分享几条这些年踩坑踩出来的经验。第一条习惯是写建表语句之前先把字段清单过一遍问题清单。这个字段能不能是空的能空就允许NULL不能空就NOT NULL这个字段有没有默认值业务上不传时要填什么这个字段能不能重复能重复就不用管绝对不能重复就UNIQUE这个字段的取值范围是什么有明确范围就加CHECK。四个问题问完DDL基本就出来了。第二条习惯是外键不要无脑加。小项目、内部系统、数据量可控外键省心。大流量、高并发、迟早要拆库的系统外键会变成枷锁。很多团队的表结构根本看不到外键但对应的逻辑在应用层都有严格校验数据库只负责存数据。加不加外键没有绝对答案想清楚你的系统五年后长什么样再决定。第三条习惯是CHECK约束要当成最后一道防线不是第一道。应用层校验必须先做把大部分异常挡在入口数据库的约束兜住那些漏网之鱼。反过来也别因为数据库有了CHECK就在应用层完全不校验那会给数据库造成不必要的写入压力。第四条习惯是关于时间字段的。创建时间和更新时间这种字段全部交给数据库默认值处理应用层不要自己传。手动传时间很容易出现各服务时间不一致、时区混乱的问题。用数据库统一的CURRENT_TIMESTAMP至少保证同一张表里的时间来源一致。如果你现在正在维护一张没有任何约束的表我的第一个建议不是什么高级技巧而是从NOT NULL和UNIQUE开始补起。先清理一遍历史数据给该非空的字段加上NOT NULL给业务上不该重复的字段加上唯一约束。改完之后你大概率会发现数据库报警变少了应用日志里的异常数据也少了。约束这东西前期设计花十分钟后期能帮你省下无数个熬夜排查脏数据的夜晚。 
长河君 · 2026/9/28 7:05:35 · 阅读 1.2k MySQL建表约束详解：从六大约束到订单表设计实战
前阵子有个朋友让我帮忙看一张线上用户表表里有二十多万行数据问题却很扎眼同一个手机号能注册出三个账号性别字段一半是空的订单表里甚至出现过负数金额。我打开建表语句一看干干净净的几行字段定义一个约束都没写。如果你对MySQL建表还停留在“能跑就行”的阶段那么“表的约束”这四个字值得好好研究一下。约束不是什么高深理论它就是数据库在写入数据之前给你设的几道关卡把脏数据挡在门外。这篇内容我会从约束的本质讲起把六大约束逐个拆开再用一张完整的订单表带你从零设计到落地最后附上高频报错的排查思路。适合刚接触MySQL的同学也适合那些建表全凭感觉、等到线上出问题才开始头疼的开发。1. 约束到底在解决什么问题先想清楚这三件事先说个最直观的场景。你去超市买东西收银员打出的小票上必然有几个关键信息商品名称、单价、数量、总价、交易时间、单号。你会默认这张小票不会出现单价是负数、交易时间是2月30号、单号连续重复的情况。这些“默认不会出现”落到数据库里就是约束。数据库里存的数据比小票复杂得多如果没有规则约束业务代码里漏掉一个判断垃圾数据就会钻进表里而且一旦进去了就很难清理。约束的本质可以归纳为三句话字段的值不能瞎填、每一行都得能区分、表与表之间的关系不能乱。展开说就是数据库完整性理论里的三个层面。域完整性管的是字段取值比如年龄不能是负数、状态只能在一组枚举值里选实体完整性管的是记录本身的唯一性每一行都要有能标识自己的主键引用完整性管的是表之间的关系订单里引用的用户ID必须是真实存在的用户。MySQL提供的NOT NULL、DEFAULT、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK这六大约束恰好把这三种完整性全部覆盖了。1.1 数据库完整性模型域、实体、引用三层域完整性听起来抽象其实对应的是SQL里的“域”概念。你可以把一列理解成一个小区每个字段值就是一个住户小区规定“这个位置只能住符合条件的人”这就是域约束。比如生日字段设计了DATE类型那就不能写入2023-02-30这种不存在的日期数量字段设计了INT就不能写入“三斤”这种文本。NOT NULL和DEFAULT是最基础的域约束CHECK约束则是更灵活的域规则。实体完整性对应的是“行”这个维度。一张表里如果两行数据根本无法区分那这张表就没有意义。主键是实体完整性最典型的手段它要求每一行都有唯一标识而且这个标识不能为空。UNIQUE约束是主键的补充用来约束那些业务上不允许重复但又不适合做主键的字段比如身份证号、订单编号。引用完整性关注的是表与表之间的“外键关系”。从表里存的外键值必须在主表里有对应记录。这个概念可以类比成核酸采样管和人员信息的关系采样管条形码指向的必须是一个真实登记过的人不能指向一个不存在的记录。没有引用完整性就很容易出现孤儿数据比如订单指向一个已被删除的用户。1.2 MySQL的六大约束体系一张表看清全部手段MySQL把约束分成了两大类列级约束和表级约束。列级约束写在字段定义后面只管当前这一列表级约束独立写在所有字段定义之后可以同时关联多列。下面这张表把六大约束的作用范围和使用阶段整理清楚了。约束类型分类核心作用对应完整性类型NOT NULL列级字段值不允许为空域完整性DEFAULT列级未显式赋值时使用默认值域完整性UNIQUE表级/列级字段或多个字段组合不允许重复实体完整性PRIMARY KEY表级/列级每行数据的唯一标识隐含NOT NULL实体完整性FOREIGN KEY表级字段值必须存在于关联表引用完整性CHECK表级/列级字段值必须满足指定表达式用户定义完整性理解这张表之后你会发现约束其实不复杂。设计表的时候你只需要对着每个字段问三个问题这个字段能空吗、能重复吗、取值范围有要求吗。三个问题问完该用哪些约束基本就定下来了。2. 六大约束逐一拆解语法、原理与坑纸上谈兵没有意义接下来我把六大约束逐个拆开每个都给出语法示例并重点说说那些文档里不会写清楚的坑。2.1 NOT NULL与DEFAULT空值与默认值的组合拳NOT NULL是最好理解的约束意思就是这一列不允许存NULL。但很多新手对NULL本身有误解。NULL不是0不是空字符串它的语义是“未知”。在数据库的三值逻辑里NULL参与任何比较运算结果都是UNKNOWN而不是TRUE或FALSE。所以一张表里如果大量字段允许NULL查询的时候就要处处小心一个WHERE条件没写对NULL数据就会被悄悄漏掉。DEFAULT约束负责兜底。当INSERT语句没有显式指定某一列的值时数据库自动写入DEFAULT定义的值。MySQL里最常用的写法是给时间列设置默认值比如CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP );这么写的好处是应用层插入数据时完全不用管创建时间数据库自动填当前时间。在MySQL 8.0.13之前的版本DEFAULT后面只能接常量8.0.13之后支持带括号的表达式比如DEFAULT (UUID())但注意必须加括号。实际开发里最常见的需求是“mysql设置默认值为0”。比如订单状态字段希望不传时默认是0可以这样写ALTER TABLE orders ALTER COLUMN status SET DEFAULT 0;或者用MODIFY整列重定义ALTER TABLE orders MODIFY COLUMN status TINYINT NOT NULL DEFAULT 0;这里有一个非常典型的坑如果一张表里的某个字段已经存在NULL数据你想直接给它加NOT NULL约束会直接报ERROR 1138。数据库不会帮你猜NULL应该替换成什么值你得先手动把历史数据处理掉再改约束。这也是为什么我一直强调表结构设计应该在业务上线前完成而不是等数据脏了再补救。2.2 UNIQUE唯一约束为什么“唯一”仍允许重复的NULLUNIQUE约束保证一列或者多列组合的值不重复。语法比较简单列级写法是字段后面直接跟UNIQUE表级写法是独立的UNIQUE KEY语句CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, phone VARCHAR(20) UNIQUE, email VARCHAR(100), UNIQUE KEY uk_email (email) );UNIQUE约束最反直觉的地方在于NULL可以重复。原因还是三值逻辑数据库判断“是否重复”时两个NULL互相比较的结果是UNKNOWN不判定为冲突。所以上面这张表你可以插入100行phone为NULL的数据不会报唯一冲突。这一点在很多业务场景里会造成错觉比如你以为手机号字段加了唯一约束就不会有脏数据但用户不填手机号时NULL记录可以无限堆积。复合唯一约束是UNIQUE最有价值的使用场景。比如选课表里每个学生每门课只能选一次就应该建立UNIQUE KEY uk_student_course (student_id, course_id)。这样即使单看student_id有重复、course_id也有重复组合起来也不会重复。UNIQUE约束和唯一索引的关系也需要说清楚。UNIQUE约束在InnoDB里本质上就是创建一个唯一索引约束是语义层的东西索引是实现层的载体。所以在Navicat这类图形工具里你会发现添加唯一索引和添加唯一约束的操作是同一个地方。修改唯一约束的SQL也很简单先删索引再建索引ALTER TABLE users DROP INDEX uk_email; ALTER TABLE users ADD UNIQUE KEY uk_email (email);2.3 PRIMARY KEY主键一张表只能有一个身份标识主键是表设计里最重要的一个约束。它隐含了两层意思值不能为NULL值不能重复。这相当于NOT NULL和UNIQUE的组合但主键在InnoDB里有更特殊的地位——它是聚簇索引的入口。表里的数据按主键顺序物理排列通过主键查询可以直接定位到数据行不需要回表。这也是“辅助索引如何避免回表”这个问题的关键想避免回表要么直接用主键查询要么让辅助索引覆盖你需要的所有字段。设计主键有几点建议。第一用自增整数主键最省心INT AUTO_INCREMENT或者BIGINT AUTO_INCREMENT写入性能好索引占用空间小。第二不要用业务字段做主键比如手机号、身份证号这类字段虽然唯一但会有变动风险一旦改了主键值所有引用它的表都要跟着改。第三不要用超长字符串做主键聚簇索引会复制主键到每一个二级索引里主键越长所有索引占用的空间就越大。复合主键在业务表里偶尔会出现比如关系表里用两个ID联合作为主键。但复合主键会带来一个隐患只要其中一个字段的值需要修改就得先删旧行再插新行。所以现在很多设计宁可加一个自增ID做主键再把业务上唯一的组合用UNIQUE约束解决。一张表只能有一个主键这一点不需要纠结。如果建表时忘记设置主键InnoDB会先找一个非空唯一索引作为聚簇索引的替代品实在找不到就生成一个隐藏的ROW_ID。但隐藏主键对开发者不可见查询效率也打了折扣还是老老实实显式定义更稳妥。2.4 FOREIGN KEY外键引用完整性由谁来守外键约束用来保证子表某列的值必须存在于父表被引用列里。先看一个标准建表语句CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) );这个约束的意义在于插入订单时user_id必须是users表里真实存在的ID删除users表里的用户时如果这个用户还有订单删除操作会被阻止除非你定义了级联规则。外键的级联行为有五种CASCADE表示父表删除或更新时子表同步操作SET NULL表示父表变更时子表对应列置空RESTRICT和NO ACTION都表示存在关联记录时拒绝操作SET DEFAULT在InnoDB里会被解析但实际不生效。外键是把双刃剑。好处是数据库层面保证了引用完整性应用层少写很多校验逻辑。坏处也很明显每次插入子表数据都要去父表做一次存在性检查并且要给父表对应行加共享锁在高并发场景下这会让写入变慢还容易引发锁等待。微服务架构拆分数据库之后跨库外键根本没法建大家普遍就把外键校验挪到应用层了。如果你的项目决定不用外键至少要在子表的外键列上建普通索引。因为按user_id查询订单是很高频的操作没有索引就是全表扫描。用外键的时候InnoDB会帮你自动建索引不用外键就要记得手动建。外键建不上最常见的报错是ERROR 1005。报错提示看起来像是语法问题实际十有八九是这几种情况父表被引用列没有索引、子表外键列和父表被引用列的数据类型不一致、或者表里已有数据违反外键约束。2.5 CHECK约束MySQL从形同虚设到真正执法CHECK约束的历史比较曲折。MySQL 8.0.16之前CHECK子句会被数据库解析但直接被忽略掉不会做任何校验。也就是说你写了CHECK表照样建成功但脏数据也照样能进去。这导致很多老教程里直接告诉你“MySQL不支持CHECK约束”这个说法在8.0.16之后已经过时了。现在的CHECK约束是真正执行的CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, age INT NOT NULL, CONSTRAINT chk_age CHECK (age 0 AND age 150), CONSTRAINT chk_status CHECK (status IN (0, 1, 2)) );CHECK的表达能力比ENUM强很多。ENUM只能列一组固定取值而且修改枚举值需要重建表CHECK可以做范围判断、逻辑运算、多列联合判断。比如订单金额必须大于等于0用ENUM根本做不到CHECK一句话就搞定。年龄、金额、状态、评分这类字段只要业务规则稳定用CHECK做的收益很直接。用CHECK有几个需要注意的点。第一CHECK不会阻止NULL写入除非约束表达式里显式包含 IS NOT NULL 判断。第二8.0.16以下版本不要指望CHECK需要靠应用层校验兜底。第三对已有大量数据的表加CHECK约束要谨慎只要历史上存在一条违反约束的数据ALTER TABLE就会失败。3. 实操从需求到完整DDL手把手设计一张订单表前面讲了理论这一节我们拿一张真实的订单表练手。目标是设计一张电商订单表能承载用户下单、查询订单、修改状态、数据统计这些基本需求同时把该有的约束全部设计进去。3.1 需求拆解一张业务表需要哪些约束拿到需求先别急着写CREATE TABLE把每个字段的业务规则列清楚。这张orders表大概需要这些字段主键ID、订单编号、用户ID、订单金额、订单状态、备注、创建时间、更新时间。逐个分析约束需求。订单编号order_no是用户能看到的核心业务字段它在整个表里不能重复但订单编号不是自增ID而是由业务规则生成的比如时间戳加随机数。所以这个字段不适合做主键但必须加UNIQUE约束。用户ID user_id引用users表的主键。如果项目是中小规模直接加外键最省心如果已经确定要分库分表那就只建普通索引不加外键校验交给应用层。订单金额amount是业务的关键数据不允许为负数用CHECK约束 amount 0 挡住异常数据。金额用DECIMAL(10,2)不要用FLOAT或者DOUBLE浮点数存金额会有精度问题这是老生常谈的坑。订单状态status用TINYINT类型0代表待支付1代表已支付2代表已取消3代表已退款。用CHECK约束限制取值必须在这些范围内。有人喜欢直接存字符串但TINYINT占用空间小、查询快配合注释完全可以。创建时间created_at和更新时间updated_at都交给数据库维护用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP应用层完全不用传值。备注remark是唯一一个真正允许NULL的字段。用户可能不写备注用NULL表示“没有备注”是合理的不需要给默认空字符串。3.2 完整建表SQL与逐字段解析下面是完整DDL你可以直接复制改一改用到自己项目里。CREATE TABLE orders ( id BIGINT UNSIGNED AUTO_INCREMENT COMMENT 主键ID, order_no VARCHAR(40) NOT NULL COMMENT 订单编号, user_id BIGINT UNSIGNED NOT NULL COMMENT 用户ID, amount DECIMAL(10,2) NOT NULL COMMENT 订单金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态 0待支付 1已支付 2已取消 3已退款, remark VARCHAR(255) NULL COMMENT 备注, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT chk_amount CHECK (amount 0), CONSTRAINT chk_status CHECK (status IN (0, 1, 2, 3)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT订单表;逐条说明这样设计的理由。主键选BIGINT UNSIGNED自增订单表数据量增长快INT最大只能到21亿多而订单表动辄上亿行BIGINT更稳。order_no加唯一索引是为了应对用户查询订单编号的场景也让业务上“一个订单号只能有一条记录”的规则落到数据库层面。user_id加普通索引是因为按用户查订单是最常见的查询条件这里加了外键后索引其实会自动生成但显式写出来更清晰也方便以后取消外键时保留索引。amount的CHECK约束和status的CHECK约束是数据库层的最后防线。应用层校验写漏了数据库还能兜底。created_at和updated_at用默认值省去应用层维护时间的麻烦。如果你用的MySQL版本低于8.0.16CHECK约束不会生效这属于版本约束需要知晓并提前在应用层补校验。3.3 约束变更与Navicat操作实录表上线之后难免要调整约束。最常见的两个操作是给已有表加唯一约束和修改默认值。给已有表加唯一约束的SQL很简单但执行前一定要先查数据。假如你要给user_phone加唯一约束先跑一句分组查重SELECT phone, COUNT(*) FROM users GROUP BY phone HAVING COUNT(*) 1;有重复数据时直接加约束会报ERROR 1062。处理完重复数据后再执行ALTER TABLE users ADD UNIQUE KEY uk_phone (phone);Navicat里设置唯一约束的路径是鼠标右键表名选“设计表”切到“索引”选项卡点击“添加”名字填uk_phone字段选phone索引类型选“UNIQUE”保存执行。注意“普通”和“UNIQUE”的区别普通索引允许重复UNIQUE索引才带唯一约束。设置默认值为0的两种方式前面提过Navicat里更简单。设计表界面找到目标字段在“默认”那一栏直接填0保存即可。如果字段本身是NOT NULL且没有默认值加默认值之后老数据的显示不受影响因为默认值只对新增数据生效。大批量修改约束有一个必须注意的点MySQL 8.0之前的ALTER TABLE大概率会锁表数据量大时可能把业务堵死。生产环境执行结构变更前先确认版本8.0.12之后的版本才支持INSTANT算法快速加列但加约束这种操作仍然可能需要重建表最好在低峰期执行或者用gh-ost这类在线变更工具。4. 建表异常与线上问题排查实录这一节把建表过程中最常见的报错整理成速查表每个都给出排查思路和解决办法。这些报错我在工作里都遇到过有些是开发环境踩的有些是在生产环境大半夜排查的。4.1 高频建表报错速查表报错信息典型场景根本原因排查思路ERROR 1067 Invalid default value给日期列设置默认值默认值格式与字段类型不匹配比如DATE列默认0000-00-00检查字段类型和默认值格式TIMESTAMP用CURRENT_TIMESTAMPERROR 1005 Cant create table创建外键失败父表被引用列无索引、字段类型不一致、已有数据违反约束查看SHOW ENGINE INNODB STATUS对比两个表的字段定义ERROR 1170 BLOB/TEXT column used in key specification给超长字段加唯一约束utf8mb4下VARCHAR(255)做索引键超过767字节限制缩小字段长度或改用前缀唯一索引ERROR 1138 Invalid use of NULL value给含NULL数据的列加NOT NULL历史数据里有NULL值数据库无法自动处理先UPDATE填充合法值再加NOT NULLERROR 1062 Duplicate entry插入数据违反唯一约束表中已有相同值查询重复数据保留合法行删除或合并重复行ERROR 3819 Check constraint violatedINSERT或UPDATE违反CHECK约束写入值不满足CHECK表达式检查写入值确认应用层校验和数据库约束一致ERROR 1170这个坑在utf8mb4普及之后很常见。utf8mb4字符集下VARCHAR(255)的字节数是255乘以4等于1020字节超过旧版本InnoDB的767字节索引限制建唯一索引时会直接报错。解决办法一是把字段长度缩短到190以内二是用前缀索引但前缀索引在唯一约束场景下语义会变可能把两行前N个字符相同但后面不同的数据也判定为重复需要非常谨慎。4.2 唯一约束冲突与数据修复场景还原线上users表已经跑了半年手机号字段出现大量重复现在要加唯一约束。先别急着执行ALTER TABLE按这套流程来。先查出所有重复的手机号看清楚分布SELECT phone, COUNT(*) AS cnt FROM users WHERE phone IS NOT NULL GROUP BY phone HAVING COUNT(*) 1;假设手机号13800138000有3条记录需要决定保留哪条。通常保留最早创建的那一条其他记录更新手机号为NULL或者合并业务数据。更新时用子查询锁定最小IDUPDATE users SET phone NULL WHERE phone 13800138000 AND id NOT IN ( SELECT id FROM ( SELECT MIN(id) AS id FROM users WHERE phone 13800138000 ) tmp );处理完所有重复数据后再加唯一约束。加完约束后插入重复手机号会直接报错ERROR 1062。实际业务中如果确实需要对重复数据做“存在则更新”的幂等插入有三种写法INSERT IGNORE、ON DUPLICATE KEY UPDATE、REPLACE INTO。但要分场景。INSERT IGNORE会静默忽略冲突适合批处理ON DUPLICATE KEY UPDATE可以在冲突时更新指定字段适合业务幂等REPLACE INTO会先删旧行再插新行副作用是主键会变不适合有外键关联的表。4.3 约束对性能的影响与批量导入技巧约束是保护伞同时也是开销来源。唯一约束在写入时要立刻检查索引是否存在相同值外键约束要查父表并加锁CHECK约束要逐行求值表达式。平时流量不大感觉不到做批量数据导入或者大促数据回填时性能差异就非常明显。批量导入前可以临时关闭外键检查SET FOREIGN_KEY_CHECKS 0;导入完成后再打开SET FOREIGN_KEY_CHECKS 1;关掉外键检查可以让导入快很多因为省掉了逐行校验父表的过程但前提是你自己确保数据质量。注意这个设置只在当前会话生效不影响其他连接。还有一类问题是批量导入时唯一约束冲突导致整个事务中止。解决办法是先导入到临时表做一遍去重清洗再用INSERT INTO ... SELECT把干净数据导进正式表。约束和主从复制的关系也值得提一句。我遇到过从库建表时多了一个唯一约束主库正常写入但同步到从库时复制线程直接中断报错Duplicate entry。这种情况排查时看SHOW SLAVE STATUS\G里的Last_SQL_Error字段定位具体SQL然后对比主从两边的SHOW CREATE TABLE。根本思路是保持主从表结构完全一致尤其是约束定义。如果不一致宁可重建从库也不要手工跳过错误否则数据偏差会越来越大。5. 我踩过的约束设计坑几条长期有效的建表习惯老规矩最后分享几条这些年踩坑踩出来的经验。第一条习惯是写建表语句之前先把字段清单过一遍问题清单。这个字段能不能是空的能空就允许NULL不能空就NOT NULL这个字段有没有默认值业务上不传时要填什么这个字段能不能重复能重复就不用管绝对不能重复就UNIQUE这个字段的取值范围是什么有明确范围就加CHECK。四个问题问完DDL基本就出来了。第二条习惯是外键不要无脑加。小项目、内部系统、数据量可控外键省心。大流量、高并发、迟早要拆库的系统外键会变成枷锁。很多团队的表结构根本看不到外键但对应的逻辑在应用层都有严格校验数据库只负责存数据。加不加外键没有绝对答案想清楚你的系统五年后长什么样再决定。第三条习惯是CHECK约束要当成最后一道防线不是第一道。应用层校验必须先做把大部分异常挡在入口数据库的约束兜住那些漏网之鱼。反过来也别因为数据库有了CHECK就在应用层完全不校验那会给数据库造成不必要的写入压力。第四条习惯是关于时间字段的。创建时间和更新时间这种字段全部交给数据库默认值处理应用层不要自己传。手动传时间很容易出现各服务时间不一致、时区混乱的问题。用数据库统一的CURRENT_TIMESTAMP至少保证同一张表里的时间来源一致。如果你现在正在维护一张没有任何约束的表我的第一个建议不是什么高级技巧而是从NOT NULL和UNIQUE开始补起。先清理一遍历史数据给该非空的字段加上NOT NULL给业务上不该重复的字段加上唯一约束。改完之后你大概率会发现数据库报警变少了应用日志里的异常数据也少了。约束这东西前期设计花十分钟后期能帮你省下无数个熬夜排查脏数据的夜晚。