很多人刚开始学 MySQL 的时候都会觉得建表是一件特别简单的事字段名起好类型选对主键设上就算完事了。我也是这么过来的结果第一张用户表上线还没多久就出了大问题——同一个手机号在表里出现了三次业务方拿着两份互相冲突的用户资料来找我报表统计一跑某个核心字段一堆 NULL汇总数据直接对不上。那时候我才意识到建表的时候没给数据“立规矩”后面所有的填坑都是这笔账的利息。这个“规矩”就是 MySQL 的表约束。它不是什么高深的概念说白了就是数据库替你在数据进表之前把好关哪些字段必须有值、哪些值不能重复、哪些值只能落在指定范围内、表跟表之间必须怎么对应。这篇指南我会从约束类型讲起再做建表实操推演最后把我踩过的几个典型坑和约束设计的思路一起分享出来适合刚学完增删改查、准备自己动手设计表结构的新手。1. 约束到底在替我们盯什么事很多人对约束的第一印象是“限制”觉得这也不能填、那也不能改挺烦的。但反过来想一下如果一张表什么都能往里塞那表里的数据是不是很快就没法用了约束不是给你添麻烦的它是在入口处就把垃圾数据拦下来避免你后面花十倍的时间去清洗和排查。1.1 没有约束的表是怎么一步步变成脏数据仓库的我以前接手过一张老项目里的用户表当时建表的人很随意整张表除了一个自增主键之外其他字段几乎零约束。用了三个月之后表里的数据变成了这个样子同一个手机号注册了三条记录因为根本没有唯一约束随手一插就进去了。姓名、手机号这些核心字段存在大量 NULL大量记录连最基本的联系信息都没有。状态字段填得乱七八糟0、1、2、3 都有业务代码只认 0 和 1一执行就出逻辑错误。用户已经被删掉了但订单表里还留着一堆指向这个已删除用户的订单记录关联查询全查不到人。这些脏数据带来的问题远比想象中严重。报表统计出来是错的业务方拿着错误数据做决策最后挨骂的还是写 SQL 的人程序里到处要加“如果这个字段不是 NULL 再处理”的防御逻辑最痛苦的是你根本不知道哪些数据是假的哪些是真的只能一条条人工核对。1.2 为什么应用层校验代替不了数据库约束不少新手会问我在 Java 或者 Python 里判断一下手机号不能为空、不能重复不就行了吗为什么非要靠数据库约束应用层校验确实能挡住一部分问题但前提是——所有数据都规规矩矩地走你的应用入口。实际情况远没这么理想入口太多了。后台管理脚本、数据导入工具、报表系统直连数据库写入甚至另一个团队的系统直接连你的库表。这些入口全都不经过你的 Java 代码你写的校验在它们面前根本不存在。并发场景下有竞态。就算你在应用层先查一遍“手机号存不存在”再决定是否插入两个请求同时进来的时候可能都查不到记录然后一起插入成功。数据库的唯一约束是原子性的只有它能在这个瞬间拦住重复数据。表之间的关系如果只靠代码维护特别容易断。外键约束会强制数据库去检查子表引用的父表记录存不存在这比你在代码里到处补逻辑可靠得多。应用层校验像小区门口的保安能拦住大部分可疑人员数据库约束就是隔间里的身份核验员再怎么绕最后一步都得过他这关。两者都重要但数据库层是底线。2. 六类表约束逐个拆解语法、原理和适用场景MySQL 里常用的约束其实就六种NOT NULL、UNIQUE、PRIMARY KEY、DEFAULT、CHECK、FOREIGN KEY。我建议你先把它们的关系捋清楚再谈建表。约束控制什么典型场景PRIMARY KEY主键唯一且非空每张表都要有通常自增 IDUNIQUE值不重复但 NULL 可重复手机号、邮箱、订单号NOT NULL字段必须有值姓名、状态、创建时间DEFAULT未指定时使用默认值状态默认 1、创建时间默认当前时间CHECK值的范围或枚举规则年龄 0-150状态只能 0/1FOREIGN KEY表之间的引用完整性订单表的用户 ID 指向用户表2.1 NOT NULL值必须存在但先分清 NULL 和空字符串NOT NULL 的意思很直白插入或更新这条记录的时候这个字段必须有值不允许空着。很多新手分不清 NULL 和空字符串的区别。NULL 表示“这个值压根不存在”它不是一个值而是一种“没有值”的状态空字符串表示“有值只是长度为 0 的字符串”。这两者在数据库处理上完全是两回事判断空值要用IS NULL判断空字符串要用 。COUNT(字段)会跳过 NULL但会统计空字符串。唯一约束遇到 NULL 不会参与比对遇到空字符串会参与比对。比如说建表时写了phone VARCHAR(20) NOT NULL那插入记录时必须给手机号哪怕是也行——因为空字符串也是“有值”。如果想让“没填手机号”的表现更明确很多团队会约定手机号必填用 NOT NULL头像这种东西可空就什么都不加。2.2 UNIQUE 与 PRIMARY KEY防止重复的两个层级UNIQUE 约束保证这一列或这几列的组合的值不能重复插入重复值会报错ERROR 1062 Duplicate entry。它有两个关键点需要记牢主键本质上就是 NOT NULL UNIQUE 的结合体。一张表只能有一个主键但可以有多个 UNIQUE 约束。UNIQUE 约束会顺带创建一个唯一索引。索引是用来加速查询的结构UNIQUE 的约束力就建立在这个索引之上。PRIMARY KEY 一般用自增整数id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT。自增主键的好处是写入时按顺序增长B 树索引插入效率高作为 WHERE 条件查询也很快。如果业务上有自然唯一标识比如订单号可以加上 UNIQUE 约束但不一定要把它当主键——主键保持自增整数订单号做 UNIQUE两个各司其职更舒服。UNIQUE 还支持联合约束。比如防止用户重复购买同一场活动可以写UNIQUE KEY uk_user_event (user_id, event_id)这样同一对 user_id 和 event_id 只能出现一次比在应用层写双重判断靠谱得多。2.3 DEFAULT给“漏填”准备的兜底方案DEFAULT 的含义是插入数据时如果没有指定这个字段的值数据库就自动填上你设定的默认值。它通常和 NOT NULL 配合使用比如status TINYINT NOT NULL DEFAULT 1 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP这样做的好处是业务代码插入数据时可以不关心这些字段数据库自动把状态置为 1、把创建时间记成当前时间既省事又能保证数据完整。新手容易产生一个误解以为 “加了 DEFAULT 之后已有数据会被自动填上默认值”。不是的。DEFAULT 只对“新插入且未指定值”的记录生效对已经存在的行没有影响。如果想让历史数据的空值也补上默认值需要单独写UPDATE语句去处理。另外一个容易混淆的点DEFAULT 是静态兜底不会随记录修改而更新。想让时间字段在每次修改时自动刷新要用ON UPDATE CURRENT_TIMESTAMPupdated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP这俩配合起来就相当于给每条数据盖了一个“最后修改时间”的章。2.4 CHECK范围与枚举校验8.0.16 之后才真正生效CHECK 约束用来限制字段的取值范围比如年龄必须在 0 到 150 之间、状态只能是 0 或 1、性别只能是 0/1/2。CREATE TABLE user ( age TINYINT NOT NULL, CONSTRAINT chk_age CHECK (age BETWEEN 0 AND 150), status TINYINT NOT NULL DEFAULT 1, CONSTRAINT chk_status CHECK (status IN (0, 1)) );这里要特别强调一个版本问题MySQL 8.0.16 之前CHECK 约束会被解析但不会执行。也就是说你在 MySQL 5.7 里写了 CHECK插入一个违反规则的数值数据库根本不会拦你纯粹是“摆设”。从 8.0.16 开始MySQL 才正式强制执行 CHECK 约束。如果你还在用老版本或者公司线上是 5.x就千万别把业务底线压在 CHECK 上。2.5 FOREIGN KEY串起表与表之间的血缘关系外键约束是六类约束里最“进阶”的一个它管的是表跟表之间的关系。比如订单表的 user_id 必须引用用户表里真实存在的 id这种约束就叫外键。语法示例CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE RESTRICT ON UPDATE CASCADE外键背后有几个硬性前提新手特别容易忽略表引擎必须是 InnoDBMyISAM 不支持外键。父表对应的列必须有索引通常就是主键。子表外键列和父表引用列的数据类型必须完全一致包括长度和是否无符号。ON DELETE 和 ON UPDATE 后面可以跟几个动作CASCADE 表示级联删除或更新RESTRICT / NO ACTION 表示阻止SET NULL 表示将外键列置为 NULL。具体怎么选是非常值得谨慎考虑的业务决策后面我专门展开讲。3. 实战建表把规矩一次性立好的完整推演光讲概念没有用我直接带你从一张用户表开始一步步把约束设计完再设计一张引用它的订单表。看完你就能照着写自己项目里的建表语句。3.1 设计用户表字段类型和约束的选择逻辑假设我们现在要给一个普通电商系统建用户表。核心需求是手机号注册、昵称可选、性别选填、账号状态区分正常和禁用。建表 SQL 如下CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户ID, phone VARCHAR(20) NOT NULL COMMENT 手机号, nickname VARCHAR(50) NOT NULL DEFAULT COMMENT 昵称, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, gender TINYINT NOT NULL DEFAULT 0 COMMENT 性别0未知 1男 2女, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常 0禁用, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone), KEY idx_email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;逐个解释一下为什么这么设id自增主键。用 BIGINT UNSIGNED 防止未来数据量太大放不下UNSIGNED 把负数空间让给正数主键纯自增用不到负数。phoneNOT NULL UNIQUE。手机号是登录凭证不允许为空、不允许重复这是整张表最核心的业务约束。nicknameNOT NULL DEFAULT 。昵称可以不填但不让它变成 NULL。这样查询出来永远是字符串代码里不用到处写if nickname ! null。email不加 NOT NULL。邮箱是选填项允许用户没有邮箱保持 NULL 是合理的语义。genderTINYINT NOT NULL DEFAULT 0。性别用枚举值而不是字符串节省存储也方便扩展默认 0 表示未知不用 NULL。statusTINYINT NOT NULL DEFAULT 1。默认正常状态新注册用户不需要显式传状态。created_at / updated_atDEFAULT ON UPDATE。这两个字段配合自动维护业务代码完全不用管。这套设计的核心思路是能不给 NULL 机会的就不给必须可空的才保留 NULL。你会在很多成熟的表结构里看到同样的套路——很少用 NULL而是用 DEFAULT 值或 0 来代表“默认状态”。3.2 设计订单表外键关联与级联删除的取舍用户表建好后再来一张订单表。订单表要关联用户核心字段是订单号、用户 ID、金额、状态CREATE TABLE user_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 订单ID, order_no VARCHAR(32) 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已取消, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 下单时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;这里最值得细说的是外键的 ON DELETE 策略。我选了RESTRICT而不是CASCADE原因很简单订单是核心交易数据用户不能说删就删连带订单一起消失。如果运营误删了用户系统应该阻止这个操作提醒他先处理该用户名下的订单。反过来如果用ON DELETE CASCADE删一个用户他的所有订单一起没了订单明细也一起没了这种连锁反应在交易系统里非常危险。什么情况下用 CASCADE 是合理的当子表数据是从属于父表的附属数据而且确实应该跟随父表一起清除时。比如购物车表和用户表的关系用户注销购物车记录一起清掉这是符合直觉的。再比如订单表和订单明细表删掉主订单明细同步删也说得通。判断标准很简单子表离开了父表还有独立存在的价值吗没有才考虑 CASCADE有就必须 RESTRICT 或软删除。另外我手动加了KEY idx_user_id (user_id)因为外键列通常会被用作查询条件比如“查某用户的所有订单”这个索引能大幅加速这类查询。外键约束本身不会自动给子表建索引需要你手动补上。3.3 表已经建好了怎么用 ALTER TABLE 补约束现实情况往往是表已经上线很久了积累了数据现在才发现约束没建全。这时候要用 ALTER TABLE 来补救。-- 把手机号列改成 NOT NULL ALTER TABLE user MODIFY COLUMN phone VARCHAR(20) NOT NULL; -- 给手机号加唯一约束 ALTER TABLE user ADD CONSTRAINT uk_phone UNIQUE (phone); -- 给状态加 CHECK 约束 ALTER TABLE user ADD CONSTRAINT chk_status CHECK (status IN (0, 1)); -- 给订单表补外键 ALTER TABLE user_order ADD CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE RESTRICT;但直接跑 ALTER 很容易翻车。因为数据库在执行约束之前会先检查现有数据是否满足规则。举个最简单的例子如果历史数据里已经有重复手机号了你执行ADD CONSTRAINT uk_phone UNIQUE (phone)会直接报错。如果有一行记录手机号是 NULL你执行MODIFY COLUMN phone ... NOT NULL也会失败。所以补约束之前必须先做两件事查重复SELECT phone, COUNT(*) FROM user GROUP BY phone HAVING COUNT(*) 1;查空值SELECT id FROM user WHERE phone IS NULL;发现问题后先清洗数据再补约束。对大表执行 DDL 也要小心锁表时间可能很长尽量挑低峰期操作或者用在线变更工具配合处理。这部分后面讲老表补约束的时候再展开。4. 新手最容易翻车的四个约束陷阱这部分我直接用自己的真实经历说话。下面这四个场景每一个都是我在项目里或者给朋友排错时真真切切遇到过的希望你看到的时候能少走一次弯路。4.1 外键插入失败ERROR 1452 的完整排查链路有一次测试环境跑一个下单接口插入订单失败完整报错长这样ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (demo.user_order, CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE RESTRICT ON UPDATE CASCADE)新手看到这么长一串报错直接就懵了。我拆解一下排查链路第一步看报错末尾的主题REFERENCES user (id)意思是外键指向 user 表的 id 列。问题大概率出在插入的 user_id 在 user 表里不存在或者类型不一致。第二步检查刚才插入的 SQL发现传的是user_id 999人眼可能看不出问题。第三步去 user 表确认SELECT id FROM user WHERE id 999;返回空行确认是父表里没有这个用户。第四步再对比两端字段类型SHOW CREATE TABLE user_order; SHOW CREATE TABLE user;发现 user.id 是BIGINT UNSIGNED而 user_order.user_id 碰巧也一致如果当初建表时把其中一列写成了INT插入时也会报错因为 MySQL 要求外键两侧的类型完全匹配长度、符号都要一致。最后定位到根因是测试脚本里硬编码了一个不存在的用户 ID。解决方式很简单要么先去 user 表创建这个用户再下单要么修正脚本。排查这类问题核心思路是把“子表报错”和“父表缺数据”这两件事对应起来。4.2 唯一约束没拦住重复数据又是 NULL 在搞鬼有一次业务反馈明明给邮箱字段加了唯一约束结果表里还是出现了两条邮箱为空的记录。我查了一下这两条记录的 email 都是 NULL。这就涉及一个非常关键、但很多人不知道的规则MySQL 唯一约束允许存在多个 NULL 值。原因也不难理解。NULL 代表“没有值”MySQL 在处理唯一索引时认为 NULL 不等于任何其他 NULL所以两条记录如果 email 都是 NULL它们互不冲突都算“唯一”。那这个行为合理吗大多数情况下是合理的。比如“多个用户都没有邮箱”这在业务上是完全正常的我们不能因为业务上“他们都没有邮箱”就拒绝第二个没邮箱的用户注册。但如果业务需求是“没有邮箱的用户也只能有一个”那就得想办法。比如把 email 设计为NOT NULL DEFAULT 再给 email 加唯一约束这样多个空字符串就会冲突。不过这样做的代价是无邮箱用户也被占了一次唯一名额到底适不适合要看具体业务。这里我想强调一个认知约束不是拍脑袋加的它必须精确表达业务规则。如果你没想清楚“NULL 算不算重复值”这个问题加出来的约束很可能是错的方向。4.3 CHECK 约束被当成摆设版本和引擎都要查有朋友跟我说他在表里加了 CHECK 约束状态字段只允许 0 和 1但插入一条 status 2 的数据居然成功了。我问他 MySQL 版本他说 5.7。这就破案了——MySQL 5.7 和 8.0.16 之前的版本CHECK 约束只是“语法兼容”并不会真正执行。也就是说你写了等于白写数据库一声不吭地接受了违反规则的记录。如果你想确认自己数据库的真实行为做两步验证SELECT VERSION();先查版本再看表的定义SHOW CREATE TABLE user_order\G;然后尝试插入一条非法值INSERT INTO user_order (order_no, user_id, amount, status) VALUES (T001, 1, 99.00, 99);如果 8.0.16 及以上版本这条插入会被拒绝报ERROR 3819如果老版本它会乖乖写进去。还有一个坑是表引擎。外键约束只有在 InnoDB 下才有效如果你的表是 MyISAM外键写了也会被忽略。查引擎的方式SHOW TABLE STATUS LIKE user_order;看 Engine 列即可。4.4 ON DELETE CASCADE 的连锁灾难我一个同事负责过一个活动系统建活动报名表时图省事外键写成了ON DELETE CASCADE。后来运营在后台误删了一位用户结果这个用户的所有报名记录全部被级联删除。这还不是最糟的——报名记录关联的核销记录又被另一张表的级联规则带走了一口气没了三层数据。等同事反应过来数据已经不在库里了只能靠备份恢复。那次之后我给自己定了一条铁律凡是核心业务表之间的外键一律用 RESTRICT不用 CASCADE。CASCADE 只允许出现在“非核心、明确需要级联清数据”的地方。即便要用 CASCADE也要做三件事删除前先SELECT COUNT(*)看一下会牵连多少子表数据。删除前提前备份至少要保证有 binlog 和定时备份可以追溯。权限上严格控制 DELETE 权限避免运营手滑。记住约束是帮你保护数据的不是帮你批量删除数据更方便的。CASCADE 用不好就是一台自动销毁碎纸机。5. 约束不是越多越好老表补约束的实战经验前几节都在鼓励你多用约束但到了真正设计老表补约束的时候你会发现事情没这么简单。约束不是越多越好每一步都要根据业务和数据量来判断。5.1 过度约束的成本索引、写入压力和维护性约束本身是有代价的主要体现在三块写入性能。UNIQUE 和 PRIMARY KEY 会自动建立索引每次插入都要更新索引索引越多写入时的开销越大。CHECK 约束虽然不建索引但每次插入都要做表达式判断。锁与检查开销。外键约束在插入和更新子表时要去检查父表对应的记录这个检查涉及到锁行为在高并发写入的链路上会有可测量的开销。所以很多大型互联网公司的核心交易表其实是不建外键的只保留普通索引靠应用层保证数据关系——这是一种主动放弃数据库约束、换取写入性能的取舍。维护成本。每个约束都是一条业务规则不加注释的约束会让后来接手的人很困惑为什么这里不能传 0为什么这里必须要 NOT NULL所以建约束时一定要写 COMMENT在文档或 README 里说明每条约束的业务含义。对新手来说我建议先从“必须的约束”开始不急着追求“能加的全加上”。必须的约束包括主键、业务唯一键、NOT NULL对必填字段、DEFAULT需要默认值的字段。CHECK 和外键可以看情况加尤其是外键你要先确认团队对性能和约束的态度再决定用不用。5.2 一张被 800 万行脏数据逼出来的约束检查清单我之前处理过一张累计 800 多万行的用户画像表建表那会儿几乎零约束结果是重复 ID、空值、非法枚举值到处都是。补约束的过程非常痛苦但也逼我整理出了下面这份“约束设计检查清单”现在每次设计新表都照着过一遍每张表必须有主键。优先自增 BIGINT 主键业务主键虽然可行但字符串主键在聚簇索引里的性能一般不如自增整数。面向用户的业务唯一标识必须加 UNIQUE。手机号、邮箱、订单号、身份证号这类能定位到具体个人的字段必须防重复。业务上“必须有值”的字段必须写 NOT NULL。可空的字段再考虑要不要给 DEFAULT。取值范围一定要校验。MySQL 8.0.16 以上用 CHECK老版本靠 ENUM 和业务层双重保证。有明确的父子关系才用外键DELETE 策略必须拍板。想清楚子表在父表删除后是要阻止、删除还是置空。时间字段统一建 created_at 和 updated_at。前者用 DEFAULT CURRENT_TIMESTAMP后者加上 ON UPDATE CURRENT_TIMESTAMP这相当于给每条数据留了案底排查问题的时候特别有用。这条清单不是什么高深理论就是从我反复踩的坑里提炼出来的。设计表之前花十分钟过一遍能省掉后面无数加班的夜晚。5.3 立规矩的正确顺序先洗数据再上约束最后说说给老表补约束的正确姿势这部分大概率是你以后会遇到的场景因为几乎每个项目都有一两张“历史遗留问题表”。顺序一定是探数据 → 洗数据 → 备份 → 加约束 → 回归验证。别一上来就 ALTER TABLE。第一步探数据。写几条统计 SQL把问题量化-- 查空值 SELECT COUNT(*) FROM user WHERE phone IS NULL; -- 查重复 SELECT phone, COUNT(*) FROM user GROUP BY phone HAVING COUNT(*) 1; -- 查非法枚举 SELECT COUNT(*) FROM user WHERE gender NOT IN (0, 1, 2);第二步洗数据。空值能补就补比如手机号字段可以用临时占位值不能补的先移到临时表重复值保留主键最小的一条其余标记为废弃。第三步备份。至少把要动的那张表单独导出mysqldump -uroot -p demo user user_before_alter.sql第四步加约束。优先在低峰期执行大表加约束前评估锁表时间。表特别大时考虑用在线 DDL 工具或者分批次处理。第五步回归验证。插入一条合法数据应该成功插入一条非法数据应该被拒绝跑一遍核心业务查询确认没被破坏。这套流程虽然麻烦但比事后清理脏数据要省力得多。补约束失败的代价不只是报错而是数据可能被修改得回不了头。我在实际项目里最深的体会是约束设计得好的表后续写 SQL、改需求、做统计都顺风顺水约束稀烂的表动不动就给你冒出几个重复 ID 或 NULL排查问题像是在泥潭里捞针。所以如果你是 MySQL 新手我真心建议下一步建表之前先停下来问自己三件事这张表的身份证是谁哪些字段绝不能为空哪个字段重复了会让业务直接崩掉把这三个问题想清楚你的表和你的数据就不会差到哪去。