1. 约束到底是干嘛的先从一个血泪事故说起去年年中我们线上有个订单系统的报表数据突然对不上账。排查到最后问题出在一张运营临时用的活动记录表上因为没有唯一约束同一个活动、同一个用户被重复写入了三次后边统计营收的 SQL 一关联就把金额翻了三倍。更离谱的是有一行关键记录的业务状态字段是 NULL前端拿到直接判空出 bug给用户发了错误优惠券。那段时间我反复跟团队强调一句话能在数据库层解决的数据质量问题不要指望应用层代码兜底。这就是表的约束存在的根本意义——它是一套由数据库强制执行的数据校验规则在数据落盘之前就把不合法、不完整、有歧义、相互矛盾的数据拦截下来。哪怕代码写得再烂、接口到处漏只要约束建得够严数据就不会坏。这篇内容适合谁正在学 MySQL 的新人写了几年 SQL 但没系统整理过约束的老手以及想把手头项目的表结构底子打得更扎实的开发者。我会把 MySQL 里常见的几类约束讲透每一种约束解决什么问题、怎么写、藏在底下容易踩的坑以及实战中怎么设计才不会把自己锁死。先建立一个大概念约束不是数据库的附加功能它本身就是表结构设计的一部分。你建表时写的每一列类型、每一个约束条件都在定义什么样的数据才配进入这张表。想通了这一点很多设计上的纠结就会变得明朗。2. 六类约束逐个拆解怎么用、为什么这么用、坑在哪2.1 主键约束 PRIMARY KEY一张表的身份证主键约束是最基础也最不能少的约束。它的作用有两层唯一标识一行记录作为 InnoDB 聚簇索引的入口。在 MySQL 的 InnoDB 引擎里表数据本身就按主键顺序物理存储所以主键选得对不对直接决定这张表的读写性能。建主键有几种方式-- 方式一建表时直接指定 CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL ); -- 方式二表定义最后追加 CREATE TABLE user ( id BIGINT, name VARCHAR(50) NOT NULL, PRIMARY KEY (id) ); -- 方式三复合主键多列联合唯一 CREATE TABLE order_item ( order_id BIGINT NOT NULL, item_id BIGINT NOT NULL, quantity INT, PRIMARY KEY (order_id, item_id) );这里最容易忽略的细节是主键自带非空 唯一 索引三重属性。你不需要再额外给主键列加 NOT NULL 或者 UNIQUE加了也白加反而让定义冗余。还有两个关于主键的经典经验第一建议使用自增整数或雪花算法生成的分布式 ID 作为主键而不要用业务字段当主键。比如有人拿身份证号当用户表主键一旦业务规则调整比如允许匿名用户注册、身份证校验规则变化改主键会牵一发动全身。业务字段可以加唯一约束但主键最好是跟业务解耦的纯标识符。第二自增主键在 InnoDB 中有个不连续特性事务回滚后自增计数不会回退。别误以为这是 bug这是 InnoDB 为了保证并发插入性能做出的设计取舍。提示如果拿 UUID 字符串当主键会带来两个问题——存储空间大、随机写入导致页分裂严重。在单表数据量大的场景下性能差距会非常明显。优先考虑 BIGINT 自增或者使用雪花算法。2.2 非空约束与默认值约束NULL 是万恶之源我见过太多线上事故最后都能追溯到某个字段不该是 NULL 但却是 NULL。NULL 在 SQL 里的语义非常特殊它与任意值比较包括 NULL NULL结果都是未知在 WHERE 条件、索引使用、聚合函数里都会产生微妙且隐蔽的行为差异。很多人写的 bug本质上是没想清楚 NULL 和空字符串的区别。CREATE TABLE customer ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL COMMENT 用户名必填, phone VARCHAR(20) NOT NULL DEFAULT COMMENT 手机号默认空串, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1启用 0禁用, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP );几个实战判断标准业务上必须有值的字段直接 NOT NULL。例子用户名、订单金额、创建时间。业务上可能没有值的字段用空字符串或 0 表达无而不是放任为 NULL。例子手机号可以允许注册时不填那就存 查询时WHERE phone ! 清晰明了。时间字段必须 NOT NULL DEFAULT CURRENT_TIMESTAMP否则后续统计经常会遇到时间轴断裂的情况。为什么我这么排斥 NULL举个具体例子统计用户总数时COUNT(*) 和 COUNT(phone) 结果可能不一样因为 COUNT(字段) 会自动忽略 NULL 值。如果你没意识到某列存在 NULL你写统计 SQL 时会得到直觉之外的结果而且排查起来特别难受。能用默认值表达没有就绝不放 NULL。2.3 唯一约束 UNIQUE防重复数据的主力回到开头的活动记录表事故解决方案就是加上唯一约束CREATE TABLE activity_record ( id BIGINT PRIMARY KEY AUTO_INCREMENT, activity_id BIGINT NOT NULL, user_id BIGINT NOT NULL, participate_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_activity_user (activity_id, user_id) ) COMMENT 活动参与记录;加了uk_activity_user这个联合唯一索引后同一个用户参与同一个活动就只能有一条记录重复插入直接报错 Duplicate entry彻底从源头堵住脏数据。唯一约束和主键约束的区别很多人说不清楚。整理一张对比表维度主键约束唯一约束字段数量一张表只能一个主键可多列组成一张表可有多个唯一约束是否允许 NULL不允许允许且多个 NULL 可以共存自动建索引是聚簇索引或辅助索引是辅助索引语义定位行的唯一标识业务规则上的不得重复那个多个 NULL 共存值得单独划重点MySQL 认为 NULL 是未知值每个 NULL 都不相等所以唯一约束不会阻止多行 NULL。这既是坑也是特性。如果业务要求除了 NULL 之外不能重复MySQL 原生唯一约束做不到需要配合函数索引或者生成列来实现。-- 利用生成列把 NULL 转为唯一值实现非 NULL 字段唯一 ALTER TABLE customer ADD COLUMN phone_unique VARCHAR(20) GENERATED ALWAYS AS (IFNULL(phone, UUID())) VIRTUAL, ADD UNIQUE KEY uk_phone_unique (phone_unique);当然绝大多数业务根本不需要这么复杂先把普通唯一约束用好再说。2.4 CHECK 约束被忽略多年的把关者很多 MySQL 老用户对 CHECK 约束完全无感因为在 8.0.16 版本之前MySQL 虽然支持 CHECK 语法但只会解析、不会真正执行。你建了也白建数据照样能非法写入。这个历史包袱让很多人养成了写完 CHECK 就不管的坏习惯。从 8.0.16 开始MySQL 终于让 CHECK 真正生效了。CREATE TABLE product ( id BIGINT PRIMARY KEY AUTO_INCREMENT, price DECIMAL(10,2) NOT NULL, stock INT NOT NULL, status TINYINT NOT NULL DEFAULT 1, CONSTRAINT chk_price_positive CHECK (price 0), CONSTRAINT chk_stock_non_negative CHECK (stock 0), CONSTRAINT chk_status_valid CHECK (status IN (0, 1)) );有了 CHECK 约束应用层少写很多判断逻辑比如库存不能为负数、价格不能低于 0、状态只能在枚举范围内。虽然这些校验在业务代码里也能做但数据库层兜底的价值在于任何入口写入的数据都必须通过校验包括临时 SQL、数据订正脚本、运维手工改数据。CHECK 的两个小坑第一老项目升级到 8.0.16 之前建的表如果有假 CHECK需要手动重建才会真正生效第二CHECK 里不要写复杂的子查询或存储过程调用一方面是性能问题另一方面是维护难度直线上升。简单、直接、可枚举的条件才适合做 CHECK。2.5 外键约束 FOREIGN KEY关系型数据库的灵魂也是争议的起点外键约束保证的是引用完整性子表里的某个字段必须是父表主键或唯一键里真实存在的值。它解决的是订单表里的 user_id 在用户表里根本查不到这个人这类关系错乱问题。CREATE TABLE order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user(id) );外键有三个核心规则需要理解透彻约束双方的类型必须完全匹配。user_id 在父表是 BIGINT子表也必须是 BIGINT且字符集、排序规则一致。否则建表直接报错。外键列必须加索引。MySQL 会自动为外键列创建索引但如果父表被引用的列本身就是主键或唯一键这里通常没有问题要注意的是如果子表外键列在多列联合索引的后面位置性能会有隐患此时建议单独加索引。级联行为由 ON DELETE / ON UPDATE 指定这个我在下一章单独展开。外键的使用争议非常大背后的权衡我在第 3 节详细说。先只放一句话小团队、小项目、数据一致性要求高放心用外键大并发、分布式分库分表基本都不再用外键改为应用层保证。3. 外键约束是把双刃剑什么时候该用它、什么时候该果断放弃3.1 为什么很多大厂都在禁用外键在阿里、《Java 开发手册》这类业界规范里有一句广为流传的规矩禁止使用外键约束一切外键关联必须在应用层解决。很多新人看了不理解外键不是数据库的基本能力吗为什么不用理由其实很现实。第一外键约束会降低写入性能。每一次 INSERT 或 UPDATE 触发外键校验时数据库都要去父表做一次加锁查询这在批量导数据、高并发写入时是明显的瓶颈。第二外键让表之间的耦合变得很紧。做分库分表时跨数据库实例的外键根本没法生效等于白建。第三外键的级联删除风险极高。一条 DELETE 语句可能因为 CASCADE 把关联表里几十万条记录一并删掉DBA 看报警都来不及拦。所以技术圈逐渐形成了一种默契互联网高并发场景下让应用层做一致性校验数据库只负责存储和最简单的约束。而传统企业应用、后台管理系统、ERP 这类数据量不大但正确性要求极高的场景外键依然是强烈推荐的选择。它的存在能让数据关系长期保持稳定减少应用层逻辑出漏子的概率。3.2 外键级联操作的四个选项写外键时ON DELETE 和 ON UPDATE 有四类动作选择不同行为完全不同动作含义适用场景CASCADE父表删除/更新子表自动同步删除/更新子表记录是父表的附属物没有独立存在价值SET NULL父表删除/更新子表外键列改为 NULL子表记录需要保留但父表不在时语义为无归属RESTRICT / NO ACTION存在子表引用时禁止父表删除/更新默认行为最安全防误删SET DEFAULT父表删除/更新子表列改为默认值需要子表字段有默认值实际用得少我的推荐是默认用 RESTRICT / NO ACTION除非你真的明确知道级联是想要的。像用户删了他所有订单也删掉这种需求用 CASCADE 其实很危险——订单可能关联着发票、物流、退款等更深一层的数据级联会引发连锁反应。举个实际踩坑案例。之前做过一个 CRM 系统联系人表外键关联客户表删客户时联系人跟着 CASCADE 删了。后来业务方提出删除客户要保留联系记录用于审计那批历史数据已经被级联删得干干净净恢复成本极高。从那以后凡是涉及金融、日志、审计、订单这类不能丢的数据我全部用软删除标记 外键 RESTRICT。3.3 外键当成最后一道防线来用现在我的实践策略非常明确能建外键就建外键但绝不依赖外键承担核心的正确性校验。核心业务的不变量由应用层事务和显式查询来保证外键只是那个万一应用层漏了的时候兜底的存在。比如创建订单时应用层先查用户表确认 user_id 存在再插入订单如果代码出现并发情况或者查询逻辑漏了外键在最后一刻拦截住非法数据让写入报错而不是让脏数据落库。这个策略兼顾了性能和安全的性价比正常链路没有任何额外查询开销异常时数据库还能守住底线。4. 约束相关的经典报错与完整排查路线4.1 Duplicate entry唯一键冲突的几种隐蔽场景最常见的约束报错就是Duplicate entry xxx for key uk_xxx。初级情况是重复插入同一条记录这个大家都会看。麻烦的是这两种隐蔽场景业务键确实发生了变化。比如用户手机号换绑A 用户的手机号替换成了原来 B 用户正在用的号此时更新就需要事务内先解绑再绑定否则必然撞唯一约束。幂等逻辑没做好。订单回调、消息重试时忘记先查询是否已处理导致重复写入。排查思路很直接把报错信息里的重复值拿出来到表里查一遍看已有数据的产生时间和来源。如果是历史脏数据导致的唯一索引新增失败先清洗数据再建索引。我用过最快的清洗 SQL 长这样-- 保留每组重复中 id 最小的删掉其他 DELETE c FROM customer c JOIN ( SELECT phone, MIN(id) AS keep_id FROM customer WHERE phone ! AND phone IS NOT NULL GROUP BY phone HAVING COUNT(*) 1 ) t ON c.phone t.phone AND c.id ! t.keep_id;注意删完再做ALTER TABLE ... ADD UNIQUE KEY顺序不能反否则索引建一半报错很尴尬。4.2 Cannot add foreign key constraint外键建不上的五个检查点这个报错的外键新手成功率极低经常是一顿操作报错后不知所措。按我的经验按顺序查下面五个点九成问题出在这里父表和子表字段的数据类型是否完全一致。BIGINT 和 INT 就不行VARCHAR(50) 和 VARCHAR(64) 也不行。字段的字符集与排序规则是否一致。最常见的是父表用 utf8mb4_general_ci子表用 utf8mb4_unicode_ci或者一张表 utf8、一张表 utf8mb4。父表被引用字段是否为主键或唯一索引。外键必须引用父表的唯一键。存储引擎是否都是 InnoDB。MyISAM 不支持真正的外键约束一个 MyISAM 表上建外键虽然不报错但实际不生效混合在一起就容易出幻觉。子表外键列与父表字段的默认值是否冲突。比如父表列是 NOT NULL DEFAULT 0子表外键列是 NOT NULL 无默认值在外键校验时会表现得很诡异。排查时直接执行SHOW CREATE TABLE 表名;把父表、子表的完整建表语句并排对比一眼就能看出类型、字符集、引擎是否匹配。这个习惯能帮你省掉大量试错时间。4.3 Data too long / Incorrect integer value字段约束与写入数据的拉扯Data too long for column name at row 1这类报错本质上是字符串长度超出 VARCHAR 或字符集限制。很多人第一反应是把 VARCHAR 长度加大但深一层的问题是设计时有没有预估字段的最长长度以手机号为例为什么要留 VARCHAR(20) 而不是 VARCHAR(11)因为手机号规则会变历史上还出现过加 86 前缀的写法你根本不知道上游系统会传什么格式进来。留 20 不是浪费存储是给数据格式变化留缓冲。而状态码、年龄这类字段用 TINYINT 就够了没必要给 VARCHAR(10)。字段类型的挑选本身就是一种隐式约束类型选大了约束就松垃圾数据更容易混进来类型选小了正常数据都可能被拒误伤业务。平衡点是预留合理余量但不过度。另一个常见场景是Incorrect integer value: abc for column age。这往往不是数据库的锅而是应用层没做类型校验。在严格 SQL 模式下MySQL 会直接拒绝非法类型转换在非严格模式下它只会把 abc 转成 0 然后存进去。所以项目一定要开启严格模式确保类型错误直接暴露而不是悄悄被修正。SET sql_mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION;4.4 ALTER TABLE 添加约束失败的迁移思路给线上大表加约束是 DBA 和开发都怕的操作。直接ALTER TABLE ... ADD UNIQUE KEY在大数据量下会锁住整表写入业务直接卡死。我的经验是按这个节奏来先在从库或者低峰期执行一遍记录耗时和锁表时长。如果表数据量不大百万级以内业务低峰期直接加问题不大。超过千万级建议用在线 DDL 工具比如gh-ost或pt-online-schema-change发布后再做一次数据校验确认新旧表数据一致。还有一个经常被忽略的问题给已有大量数据的表加新约束之前先做一遍数据体检。逻辑很简单约束是对所有历史数据生效的如果表里已经存在重复数据加唯一索引必然失败。所以提前跑一遍 SQL 查重复、查非法值、查 NULL 分布是大表加约束前的标准动作。5. 约束设计的系统方法论别把约束当成建表的附加题5.1 先梳理业务规则再动手建表很多人一拿到需求就打开 Navicat 开始点选字段建表全靠手感和经验这其实是把顺序搞反了。表结构设计的输入应该是一份完整的业务规则清单约束的选择完全由规则推导而来。我通常按下面这个思路走一条数据靠什么唯一标识—— 主键约束哪些字段在业务上不允许重复—— 唯一约束哪些字段必须有值—— 非空约束哪些字段不填时该有什么默认值—— 默认值约束哪些字段的取值范围需要限定—— CHECK 约束哪些字段引用其他表—— 外键约束这套问题问完表和约束的草图基本就出来了。很多时候约束设计就是在帮业务方理清自己到底想要什么。别人来问这个字段要不要允许为空时我的标准答案是如果连业务方都说不清空值代表什么语义就坚决 NOT NULL DEFAULT。5.2 软删除场景下的唯一约束难题业务上做了逻辑删除is_deleted1后唯一约束会变得特别棘手假设用户手机号唯一用户删了之后想要重新注册同一个手机号但表里还留着那条软删除记录唯一索引直接拦住新注册。我的解决方案是给唯一索引加上删除标记列形成联合唯一-- 把删除标记设计为 0 或 1唯一索引 (phone, is_deleted) 无法满足上面的需求一个用户只能一个手机号但删除后可以复用 -- 推荐方案唯一索引 (phone)软删除时把 phone 改写为 原值 随机后缀更通用的做法是在业务代码里软删除时把 phone 置为旧值#deleted#时间戳保证物理上不再与现存值冲突。这个技巧看着简单但在很多系统里能避免一整套复杂的替代方案。5.3 约束命名规范别让 DBA 骂你约束命名在国内团队里普遍不受重视默认名称要么是 PRIMARY、要么是字段名要么是 MySQL 自动生成的随机名。一旦线上出现问题想精准定位是哪个约束在拦截往往要一条条去查。我建议的规范如下主键pk_表名缩写_字段名比如pk_usr_id唯一uk_表名缩写_字段名组合字段多时用下划线连比如uk_usr_phone普通索引idx_表名缩写_字段名外键fk_子表名缩写_父表名缩写_字段名CHECKchk_表名缩写_规则描述命名清晰的价值在排查问题时才会体现看到uk_activity_user立刻知道是活动用户唯一约束连查都不用查。提示MySQL 的 INFORMATION_SCHEMA 提供了全部约束元数据排查约束问题时的标准查询是SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE table_name order; SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE table_name order;5.4 一份可以直接抄的建表模板结合前面所有讲到的点我给出一个综合建表实例CREATE TABLE user_account ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(50) NOT NULL COMMENT 用户名, phone VARCHAR(20) NOT NULL DEFAULT COMMENT 手机号未绑定为空串, email VARCHAR(100) NOT NULL DEFAULT COMMENT 邮箱, age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 年龄未知为 0, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态 1正常 0禁用, register_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 注册时间, is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT 逻辑删除1为已删除, PRIMARY KEY (id), UNIQUE KEY uk_ua_username (username), UNIQUE KEY uk_ua_phone (phone), KEY idx_ua_register_time (register_time), CONSTRAINT chk_ua_status_valid CHECK (status IN (0, 1)), CONSTRAINT chk_ua_age_valid CHECK (age 0 AND age 150) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 用户账户表;这个模板覆盖了主键、唯一、非空、默认值、CHECK 和索引几大类。实际业务可以按需裁剪但每条约束背后的设计理由要能说出来。如果别人问为什么 phone 不用 NOT NULL你说不上来那大概率这个约束就设计得有问题。5.5 最后的实战心得跟 MySQL 打了这么多年交道我对约束的态度经历了三个阶段刚工作时嫌弃约束麻烦、能不加就不加后来被线上脏数据教育过后开始疯狂加约束再到如今会带着分析每个约束的真实成本和收益的心态去做设计。约束不是越严越好。你给表加了太多 CHECK 和唯一约束应用层每次写入都要多扛一层校验出错的概率也更高。好的约束设计是克制但关键——核心的不变量一条不少边缘的业务规则留给应用层灵活处理。比如状态字段如果业务方每个月都可能加新状态「状态只能属于枚举值」这种 CHECK 尽量别写。你可能只加三个月报表第四个月业务一改开发就得来 DBA 这边联调改约束这个成本很高。反过来价格不能为负数、用户名不能重复、外键必须存在——这些长期不变量一定要在数据库层锁死。把约束当成一张表质量的生命线它自然会成为你系统设计里最稳健的那道防线。
MySQL约束实战指南：主键、唯一、外键与CHECK的用法和避坑
1. 约束到底是干嘛的先从一个血泪事故说起去年年中我们线上有个订单系统的报表数据突然对不上账。排查到最后问题出在一张运营临时用的活动记录表上因为没有唯一约束同一个活动、同一个用户被重复写入了三次后边统计营收的 SQL 一关联就把金额翻了三倍。更离谱的是有一行关键记录的业务状态字段是 NULL前端拿到直接判空出 bug给用户发了错误优惠券。那段时间我反复跟团队强调一句话能在数据库层解决的数据质量问题不要指望应用层代码兜底。这就是表的约束存在的根本意义——它是一套由数据库强制执行的数据校验规则在数据落盘之前就把不合法、不完整、有歧义、相互矛盾的数据拦截下来。哪怕代码写得再烂、接口到处漏只要约束建得够严数据就不会坏。这篇内容适合谁正在学 MySQL 的新人写了几年 SQL 但没系统整理过约束的老手以及想把手头项目的表结构底子打得更扎实的开发者。我会把 MySQL 里常见的几类约束讲透每一种约束解决什么问题、怎么写、藏在底下容易踩的坑以及实战中怎么设计才不会把自己锁死。先建立一个大概念约束不是数据库的附加功能它本身就是表结构设计的一部分。你建表时写的每一列类型、每一个约束条件都在定义什么样的数据才配进入这张表。想通了这一点很多设计上的纠结就会变得明朗。2. 六类约束逐个拆解怎么用、为什么这么用、坑在哪2.1 主键约束 PRIMARY KEY一张表的身份证主键约束是最基础也最不能少的约束。它的作用有两层唯一标识一行记录作为 InnoDB 聚簇索引的入口。在 MySQL 的 InnoDB 引擎里表数据本身就按主键顺序物理存储所以主键选得对不对直接决定这张表的读写性能。建主键有几种方式-- 方式一建表时直接指定 CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL ); -- 方式二表定义最后追加 CREATE TABLE user ( id BIGINT, name VARCHAR(50) NOT NULL, PRIMARY KEY (id) ); -- 方式三复合主键多列联合唯一 CREATE TABLE order_item ( order_id BIGINT NOT NULL, item_id BIGINT NOT NULL, quantity INT, PRIMARY KEY (order_id, item_id) );这里最容易忽略的细节是主键自带非空 唯一 索引三重属性。你不需要再额外给主键列加 NOT NULL 或者 UNIQUE加了也白加反而让定义冗余。还有两个关于主键的经典经验第一建议使用自增整数或雪花算法生成的分布式 ID 作为主键而不要用业务字段当主键。比如有人拿身份证号当用户表主键一旦业务规则调整比如允许匿名用户注册、身份证校验规则变化改主键会牵一发动全身。业务字段可以加唯一约束但主键最好是跟业务解耦的纯标识符。第二自增主键在 InnoDB 中有个不连续特性事务回滚后自增计数不会回退。别误以为这是 bug这是 InnoDB 为了保证并发插入性能做出的设计取舍。提示如果拿 UUID 字符串当主键会带来两个问题——存储空间大、随机写入导致页分裂严重。在单表数据量大的场景下性能差距会非常明显。优先考虑 BIGINT 自增或者使用雪花算法。2.2 非空约束与默认值约束NULL 是万恶之源我见过太多线上事故最后都能追溯到某个字段不该是 NULL 但却是 NULL。NULL 在 SQL 里的语义非常特殊它与任意值比较包括 NULL NULL结果都是未知在 WHERE 条件、索引使用、聚合函数里都会产生微妙且隐蔽的行为差异。很多人写的 bug本质上是没想清楚 NULL 和空字符串的区别。CREATE TABLE customer ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL COMMENT 用户名必填, phone VARCHAR(20) NOT NULL DEFAULT COMMENT 手机号默认空串, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1启用 0禁用, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP );几个实战判断标准业务上必须有值的字段直接 NOT NULL。例子用户名、订单金额、创建时间。业务上可能没有值的字段用空字符串或 0 表达无而不是放任为 NULL。例子手机号可以允许注册时不填那就存 查询时WHERE phone ! 清晰明了。时间字段必须 NOT NULL DEFAULT CURRENT_TIMESTAMP否则后续统计经常会遇到时间轴断裂的情况。为什么我这么排斥 NULL举个具体例子统计用户总数时COUNT(*) 和 COUNT(phone) 结果可能不一样因为 COUNT(字段) 会自动忽略 NULL 值。如果你没意识到某列存在 NULL你写统计 SQL 时会得到直觉之外的结果而且排查起来特别难受。能用默认值表达没有就绝不放 NULL。2.3 唯一约束 UNIQUE防重复数据的主力回到开头的活动记录表事故解决方案就是加上唯一约束CREATE TABLE activity_record ( id BIGINT PRIMARY KEY AUTO_INCREMENT, activity_id BIGINT NOT NULL, user_id BIGINT NOT NULL, participate_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_activity_user (activity_id, user_id) ) COMMENT 活动参与记录;加了uk_activity_user这个联合唯一索引后同一个用户参与同一个活动就只能有一条记录重复插入直接报错 Duplicate entry彻底从源头堵住脏数据。唯一约束和主键约束的区别很多人说不清楚。整理一张对比表维度主键约束唯一约束字段数量一张表只能一个主键可多列组成一张表可有多个唯一约束是否允许 NULL不允许允许且多个 NULL 可以共存自动建索引是聚簇索引或辅助索引是辅助索引语义定位行的唯一标识业务规则上的不得重复那个多个 NULL 共存值得单独划重点MySQL 认为 NULL 是未知值每个 NULL 都不相等所以唯一约束不会阻止多行 NULL。这既是坑也是特性。如果业务要求除了 NULL 之外不能重复MySQL 原生唯一约束做不到需要配合函数索引或者生成列来实现。-- 利用生成列把 NULL 转为唯一值实现非 NULL 字段唯一 ALTER TABLE customer ADD COLUMN phone_unique VARCHAR(20) GENERATED ALWAYS AS (IFNULL(phone, UUID())) VIRTUAL, ADD UNIQUE KEY uk_phone_unique (phone_unique);当然绝大多数业务根本不需要这么复杂先把普通唯一约束用好再说。2.4 CHECK 约束被忽略多年的把关者很多 MySQL 老用户对 CHECK 约束完全无感因为在 8.0.16 版本之前MySQL 虽然支持 CHECK 语法但只会解析、不会真正执行。你建了也白建数据照样能非法写入。这个历史包袱让很多人养成了写完 CHECK 就不管的坏习惯。从 8.0.16 开始MySQL 终于让 CHECK 真正生效了。CREATE TABLE product ( id BIGINT PRIMARY KEY AUTO_INCREMENT, price DECIMAL(10,2) NOT NULL, stock INT NOT NULL, status TINYINT NOT NULL DEFAULT 1, CONSTRAINT chk_price_positive CHECK (price 0), CONSTRAINT chk_stock_non_negative CHECK (stock 0), CONSTRAINT chk_status_valid CHECK (status IN (0, 1)) );有了 CHECK 约束应用层少写很多判断逻辑比如库存不能为负数、价格不能低于 0、状态只能在枚举范围内。虽然这些校验在业务代码里也能做但数据库层兜底的价值在于任何入口写入的数据都必须通过校验包括临时 SQL、数据订正脚本、运维手工改数据。CHECK 的两个小坑第一老项目升级到 8.0.16 之前建的表如果有假 CHECK需要手动重建才会真正生效第二CHECK 里不要写复杂的子查询或存储过程调用一方面是性能问题另一方面是维护难度直线上升。简单、直接、可枚举的条件才适合做 CHECK。2.5 外键约束 FOREIGN KEY关系型数据库的灵魂也是争议的起点外键约束保证的是引用完整性子表里的某个字段必须是父表主键或唯一键里真实存在的值。它解决的是订单表里的 user_id 在用户表里根本查不到这个人这类关系错乱问题。CREATE TABLE order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user(id) );外键有三个核心规则需要理解透彻约束双方的类型必须完全匹配。user_id 在父表是 BIGINT子表也必须是 BIGINT且字符集、排序规则一致。否则建表直接报错。外键列必须加索引。MySQL 会自动为外键列创建索引但如果父表被引用的列本身就是主键或唯一键这里通常没有问题要注意的是如果子表外键列在多列联合索引的后面位置性能会有隐患此时建议单独加索引。级联行为由 ON DELETE / ON UPDATE 指定这个我在下一章单独展开。外键的使用争议非常大背后的权衡我在第 3 节详细说。先只放一句话小团队、小项目、数据一致性要求高放心用外键大并发、分布式分库分表基本都不再用外键改为应用层保证。3. 外键约束是把双刃剑什么时候该用它、什么时候该果断放弃3.1 为什么很多大厂都在禁用外键在阿里、《Java 开发手册》这类业界规范里有一句广为流传的规矩禁止使用外键约束一切外键关联必须在应用层解决。很多新人看了不理解外键不是数据库的基本能力吗为什么不用理由其实很现实。第一外键约束会降低写入性能。每一次 INSERT 或 UPDATE 触发外键校验时数据库都要去父表做一次加锁查询这在批量导数据、高并发写入时是明显的瓶颈。第二外键让表之间的耦合变得很紧。做分库分表时跨数据库实例的外键根本没法生效等于白建。第三外键的级联删除风险极高。一条 DELETE 语句可能因为 CASCADE 把关联表里几十万条记录一并删掉DBA 看报警都来不及拦。所以技术圈逐渐形成了一种默契互联网高并发场景下让应用层做一致性校验数据库只负责存储和最简单的约束。而传统企业应用、后台管理系统、ERP 这类数据量不大但正确性要求极高的场景外键依然是强烈推荐的选择。它的存在能让数据关系长期保持稳定减少应用层逻辑出漏子的概率。3.2 外键级联操作的四个选项写外键时ON DELETE 和 ON UPDATE 有四类动作选择不同行为完全不同动作含义适用场景CASCADE父表删除/更新子表自动同步删除/更新子表记录是父表的附属物没有独立存在价值SET NULL父表删除/更新子表外键列改为 NULL子表记录需要保留但父表不在时语义为无归属RESTRICT / NO ACTION存在子表引用时禁止父表删除/更新默认行为最安全防误删SET DEFAULT父表删除/更新子表列改为默认值需要子表字段有默认值实际用得少我的推荐是默认用 RESTRICT / NO ACTION除非你真的明确知道级联是想要的。像用户删了他所有订单也删掉这种需求用 CASCADE 其实很危险——订单可能关联着发票、物流、退款等更深一层的数据级联会引发连锁反应。举个实际踩坑案例。之前做过一个 CRM 系统联系人表外键关联客户表删客户时联系人跟着 CASCADE 删了。后来业务方提出删除客户要保留联系记录用于审计那批历史数据已经被级联删得干干净净恢复成本极高。从那以后凡是涉及金融、日志、审计、订单这类不能丢的数据我全部用软删除标记 外键 RESTRICT。3.3 外键当成最后一道防线来用现在我的实践策略非常明确能建外键就建外键但绝不依赖外键承担核心的正确性校验。核心业务的不变量由应用层事务和显式查询来保证外键只是那个万一应用层漏了的时候兜底的存在。比如创建订单时应用层先查用户表确认 user_id 存在再插入订单如果代码出现并发情况或者查询逻辑漏了外键在最后一刻拦截住非法数据让写入报错而不是让脏数据落库。这个策略兼顾了性能和安全的性价比正常链路没有任何额外查询开销异常时数据库还能守住底线。4. 约束相关的经典报错与完整排查路线4.1 Duplicate entry唯一键冲突的几种隐蔽场景最常见的约束报错就是Duplicate entry xxx for key uk_xxx。初级情况是重复插入同一条记录这个大家都会看。麻烦的是这两种隐蔽场景业务键确实发生了变化。比如用户手机号换绑A 用户的手机号替换成了原来 B 用户正在用的号此时更新就需要事务内先解绑再绑定否则必然撞唯一约束。幂等逻辑没做好。订单回调、消息重试时忘记先查询是否已处理导致重复写入。排查思路很直接把报错信息里的重复值拿出来到表里查一遍看已有数据的产生时间和来源。如果是历史脏数据导致的唯一索引新增失败先清洗数据再建索引。我用过最快的清洗 SQL 长这样-- 保留每组重复中 id 最小的删掉其他 DELETE c FROM customer c JOIN ( SELECT phone, MIN(id) AS keep_id FROM customer WHERE phone ! AND phone IS NOT NULL GROUP BY phone HAVING COUNT(*) 1 ) t ON c.phone t.phone AND c.id ! t.keep_id;注意删完再做ALTER TABLE ... ADD UNIQUE KEY顺序不能反否则索引建一半报错很尴尬。4.2 Cannot add foreign key constraint外键建不上的五个检查点这个报错的外键新手成功率极低经常是一顿操作报错后不知所措。按我的经验按顺序查下面五个点九成问题出在这里父表和子表字段的数据类型是否完全一致。BIGINT 和 INT 就不行VARCHAR(50) 和 VARCHAR(64) 也不行。字段的字符集与排序规则是否一致。最常见的是父表用 utf8mb4_general_ci子表用 utf8mb4_unicode_ci或者一张表 utf8、一张表 utf8mb4。父表被引用字段是否为主键或唯一索引。外键必须引用父表的唯一键。存储引擎是否都是 InnoDB。MyISAM 不支持真正的外键约束一个 MyISAM 表上建外键虽然不报错但实际不生效混合在一起就容易出幻觉。子表外键列与父表字段的默认值是否冲突。比如父表列是 NOT NULL DEFAULT 0子表外键列是 NOT NULL 无默认值在外键校验时会表现得很诡异。排查时直接执行SHOW CREATE TABLE 表名;把父表、子表的完整建表语句并排对比一眼就能看出类型、字符集、引擎是否匹配。这个习惯能帮你省掉大量试错时间。4.3 Data too long / Incorrect integer value字段约束与写入数据的拉扯Data too long for column name at row 1这类报错本质上是字符串长度超出 VARCHAR 或字符集限制。很多人第一反应是把 VARCHAR 长度加大但深一层的问题是设计时有没有预估字段的最长长度以手机号为例为什么要留 VARCHAR(20) 而不是 VARCHAR(11)因为手机号规则会变历史上还出现过加 86 前缀的写法你根本不知道上游系统会传什么格式进来。留 20 不是浪费存储是给数据格式变化留缓冲。而状态码、年龄这类字段用 TINYINT 就够了没必要给 VARCHAR(10)。字段类型的挑选本身就是一种隐式约束类型选大了约束就松垃圾数据更容易混进来类型选小了正常数据都可能被拒误伤业务。平衡点是预留合理余量但不过度。另一个常见场景是Incorrect integer value: abc for column age。这往往不是数据库的锅而是应用层没做类型校验。在严格 SQL 模式下MySQL 会直接拒绝非法类型转换在非严格模式下它只会把 abc 转成 0 然后存进去。所以项目一定要开启严格模式确保类型错误直接暴露而不是悄悄被修正。SET sql_mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION;4.4 ALTER TABLE 添加约束失败的迁移思路给线上大表加约束是 DBA 和开发都怕的操作。直接ALTER TABLE ... ADD UNIQUE KEY在大数据量下会锁住整表写入业务直接卡死。我的经验是按这个节奏来先在从库或者低峰期执行一遍记录耗时和锁表时长。如果表数据量不大百万级以内业务低峰期直接加问题不大。超过千万级建议用在线 DDL 工具比如gh-ost或pt-online-schema-change发布后再做一次数据校验确认新旧表数据一致。还有一个经常被忽略的问题给已有大量数据的表加新约束之前先做一遍数据体检。逻辑很简单约束是对所有历史数据生效的如果表里已经存在重复数据加唯一索引必然失败。所以提前跑一遍 SQL 查重复、查非法值、查 NULL 分布是大表加约束前的标准动作。5. 约束设计的系统方法论别把约束当成建表的附加题5.1 先梳理业务规则再动手建表很多人一拿到需求就打开 Navicat 开始点选字段建表全靠手感和经验这其实是把顺序搞反了。表结构设计的输入应该是一份完整的业务规则清单约束的选择完全由规则推导而来。我通常按下面这个思路走一条数据靠什么唯一标识—— 主键约束哪些字段在业务上不允许重复—— 唯一约束哪些字段必须有值—— 非空约束哪些字段不填时该有什么默认值—— 默认值约束哪些字段的取值范围需要限定—— CHECK 约束哪些字段引用其他表—— 外键约束这套问题问完表和约束的草图基本就出来了。很多时候约束设计就是在帮业务方理清自己到底想要什么。别人来问这个字段要不要允许为空时我的标准答案是如果连业务方都说不清空值代表什么语义就坚决 NOT NULL DEFAULT。5.2 软删除场景下的唯一约束难题业务上做了逻辑删除is_deleted1后唯一约束会变得特别棘手假设用户手机号唯一用户删了之后想要重新注册同一个手机号但表里还留着那条软删除记录唯一索引直接拦住新注册。我的解决方案是给唯一索引加上删除标记列形成联合唯一-- 把删除标记设计为 0 或 1唯一索引 (phone, is_deleted) 无法满足上面的需求一个用户只能一个手机号但删除后可以复用 -- 推荐方案唯一索引 (phone)软删除时把 phone 改写为 原值 随机后缀更通用的做法是在业务代码里软删除时把 phone 置为旧值#deleted#时间戳保证物理上不再与现存值冲突。这个技巧看着简单但在很多系统里能避免一整套复杂的替代方案。5.3 约束命名规范别让 DBA 骂你约束命名在国内团队里普遍不受重视默认名称要么是 PRIMARY、要么是字段名要么是 MySQL 自动生成的随机名。一旦线上出现问题想精准定位是哪个约束在拦截往往要一条条去查。我建议的规范如下主键pk_表名缩写_字段名比如pk_usr_id唯一uk_表名缩写_字段名组合字段多时用下划线连比如uk_usr_phone普通索引idx_表名缩写_字段名外键fk_子表名缩写_父表名缩写_字段名CHECKchk_表名缩写_规则描述命名清晰的价值在排查问题时才会体现看到uk_activity_user立刻知道是活动用户唯一约束连查都不用查。提示MySQL 的 INFORMATION_SCHEMA 提供了全部约束元数据排查约束问题时的标准查询是SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE table_name order; SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE table_name order;5.4 一份可以直接抄的建表模板结合前面所有讲到的点我给出一个综合建表实例CREATE TABLE user_account ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(50) NOT NULL COMMENT 用户名, phone VARCHAR(20) NOT NULL DEFAULT COMMENT 手机号未绑定为空串, email VARCHAR(100) NOT NULL DEFAULT COMMENT 邮箱, age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 年龄未知为 0, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态 1正常 0禁用, register_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 注册时间, is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT 逻辑删除1为已删除, PRIMARY KEY (id), UNIQUE KEY uk_ua_username (username), UNIQUE KEY uk_ua_phone (phone), KEY idx_ua_register_time (register_time), CONSTRAINT chk_ua_status_valid CHECK (status IN (0, 1)), CONSTRAINT chk_ua_age_valid CHECK (age 0 AND age 150) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 用户账户表;这个模板覆盖了主键、唯一、非空、默认值、CHECK 和索引几大类。实际业务可以按需裁剪但每条约束背后的设计理由要能说出来。如果别人问为什么 phone 不用 NOT NULL你说不上来那大概率这个约束就设计得有问题。5.5 最后的实战心得跟 MySQL 打了这么多年交道我对约束的态度经历了三个阶段刚工作时嫌弃约束麻烦、能不加就不加后来被线上脏数据教育过后开始疯狂加约束再到如今会带着分析每个约束的真实成本和收益的心态去做设计。约束不是越严越好。你给表加了太多 CHECK 和唯一约束应用层每次写入都要多扛一层校验出错的概率也更高。好的约束设计是克制但关键——核心的不变量一条不少边缘的业务规则留给应用层灵活处理。比如状态字段如果业务方每个月都可能加新状态「状态只能属于枚举值」这种 CHECK 尽量别写。你可能只加三个月报表第四个月业务一改开发就得来 DBA 这边联调改约束这个成本很高。反过来价格不能为负数、用户名不能重复、外键必须存在——这些长期不变量一定要在数据库层锁死。把约束当成一张表质量的生命线它自然会成为你系统设计里最稳健的那道防线。
