做后端开发的谁没被数据库里的脏数据坑过同样的用户注册了两个账号、订单表里挂着不存在的用户ID、字符串字段里莫名其妙塞进一个空字符串……这些问题在项目初期几乎感觉不到等数据量上来、业务复杂了每一处“当时偷懒没加约束”的地方都会变成一个需要写一堆兜底逻辑来填的坑。MySQL表的约束就是数据库在写入时帮你把住数据质量的第一道关卡。这篇文章不打算复述文档而是把我这些年实际建表、踩坑、排查的经验完整拆开讲清楚六大约束分别解决什么问题、底层是怎么工作的、实操中哪些坑是文档里不会告诉你的。无论是刚学SQL的初学者还是写了好几年业务代码但没系统梳理过约束的老手这篇都值得花十分钟看完。1. 容不得脏数据约束到底在守护什么1.1 约束的本质是一份“写入契约”很多人对约束的第一反应是“限制”——限制这不能填、那不能改写起来烦。但换个角度看约束本质上是数据库和应用程序之间的一份契约应用端保证按规则写入数据库端在写入时强制执行校验。这份契约保护的不是开发者的代码而是数据本身的可靠性。举个生活化的例子。你去快递站寄包裹工作人员会让你填一份面单收件人电话不能为空、地址不能瞎写、保价金额不能是负数。这些规则不是故意为难你而是为了保证包裹在后续的转运、派送、理赔环节不会出乱子。数据库里的约束干的完全是同一件事NOT NULL保证关键字段有值CHECK保证取值范围合理UNIQUE保证业务上不允许重复的记录不会出现第二份。没有约束的数据库是什么样的我见过一个老项目用户表里既没有主键也没有唯一约束运营手动导入数据时把同一个手机号导入了三遍结果用户收到了三条注册成功短信登录时都不知道该匹配哪条。排查的时候根本没法说是哪条记录“错了”因为数据库层面没有定义什么叫“对”。这就是约束缺失的代价——错误不是被拦截在源头而是在下游爆雷。1.2 约束拦截的是“协作环节”的错误为什么说约束对多人协作尤其重要因为一套系统不会只有一个开发者在维护。你写的插入逻辑可能很规范但同事写的批量导入脚本、运营直接执行的SQL、第三方系统的接口回调可不一定都遵守同样的规则。约束是最后一道物理防线无论错误来自哪里MySQL 都会在写入时把它挡下来。举个我实际遇到过的情况。某个订单系统接入了支付回调回调消息里带了一个user_id由于上游系统的 bug某个环境里回调里传的是0。如果订单表的user_id没有外键约束这条订单就会带着一个不存在的用户ID写入库中后续做用户维度统计时这笔订单就变成了“幽灵订单”。但如果当初建表时给user_id加了外键指向用户表MySQL 会直接拒绝插入。与其在应用层堆一堆 if 判断不如在数据库层用约束一了百了——这既是纪律也是效率。2. MySQL 六大约束逐个拆开讲MySQL 里常用的约束就六种非空约束、默认值约束、唯一约束、主键约束、外键约束、检查约束。拆开来说每一种都有自己的脾气。2.1 非空约束 NOT NULL最便宜也最容易被忽略NOT NULL的语义很好理解这一列不允许存NULL。但实际建模时很多人的第一反应是“这个字段可能没有值那就允许 NULL 吧”。这句话需要重新审视。一个字段到底允不允许 NULL不应该看“现在有没有值”而应该看“业务上允不允许没有值”。举例说明用户的昵称注册时没填很常见业务上允许没有那就可以允许 NULL但订单的支付金额任何一笔合法订单都必须有一个明确的金额这就是业务上的“必须有”就应该加NOT NULL。把“数据可能缺失”当成“业务允许缺失”来建模是数据库设计里很常见的方向性错误。还有一个容易踩坑的点NULL 和空字符串是完全不同的概念。NULL表示“未知/没有值”空字符串表示“有值值是空字符串”。这带来两个实际影响一是聚合函数COUNT(col)会忽略 NULL 但会计入空字符串导致统计结果和你想象的不一样二是在查询条件里WHERE col 匹配不到 NULL 值必须用IS NULL。在设计阶段就把这两个语义分清楚后面能少排很多莫名其妙的 bug。2.2 唯一约束 UNIQUE业务防重的物理防线UNIQUE约束保证一列或一组列的组合值在表内唯一。和主键不同一个表可以有多个唯一约束而且唯一约束允许 NULL——这是很多人忽略的特性在 MySQL 里唯一约束允许多个 NULL 值共存因为NULL ! NULL数据库认为“未知”和“未知”并不是同一个值。这个特性有好处也有坑。好处是你可以放心地对“手机号”这种可空字段加唯一约束未填手机号的用户可以存多条填了手机号的用户则不允许重复坑在于如果你希望“NULL 也不允许重复”那必须额外用生成列或者业务层逻辑来兜底单纯靠UNIQUE是做不到的。唯一约束和后面讲的主键一样底层都会自动创建一个唯一索引。所以很多时候唯一约束不仅是数据校验手段还能作为查询索引来用——例如在用户表里对email建唯一约束登录按邮箱查询时就能直接走这个索引。2.3 主键约束 PRIMARY KEY每一行的身份证主键约束是唯一约束的加强版它不允许 NULL且一个表只能有一个主键。主键可以是单列也可以是联合主键。主键的选择是建模时最值得纠结的问题之一我一般的原则是能用自增就用自增业务字段当主键要万分谨慎。自增主键的好处是插入性能好B树按顺序写不需要频繁页分裂缺点是暴露了数据量且跨库合并时可能冲突。UUID 主键解决了合并冲突问题但随机字符串在 InnoDB 里插入时容易造成页分裂和碎片化性能上要配合优化业务字段当主键比如用身份证号做用户主键在业务上省事但一旦业务规则变化比如允许一个身份证对应多个账户改主键的成本会非常痛苦。凡是业务上可能变化的值都不要轻易当主键。联合主键用的场景相对少但有一类场景是绕不开的——关联表。例如“用户收藏商品”表自然的主键就是(user_id, product_id)这一对组合它天然表达了“一个用户对一个商品的收藏关系只有一条”。联合主键在建表时可以直接声明也可以在后续用ALTER TABLE ADD PRIMARY KEY补上。2.4 默认值约束 DEFAULT能少写代码就少写DEFAULT用来给字段指定默认值当插入时没有显式传值数据库会自动写入默认值。最典型的应用是“创建时间”create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP这样应用层完全不用管这个字段插入时自动带上当前时间。默认值的设计原则是把“大概率要写的静态值”下沉到数据库。比如订单状态新建订单默认是“待支付”这个字段完全可以在数据库中默认成 0应用层不用反复传。但需要注意默认值并不等于“可以随便不传”。如果字段同时加了NOT NULL插入时业务没有提供一个合理的值数据库会默默用默认值顶上去——这种“静默处理”有时候反而掩盖了应用层的 bug。所以我更倾向的做法是状态这类字段用默认值没问题但金额、ID 这类业务关键字段不要依赖默认值必须由应用显式传入。2.5 外键约束 FOREIGN KEY关系型数据库的“关系”所在外键约束是六种约束里最重、也是争议最大的一种。它保证子表的某个字段取值必须存在于父表的主键或唯一键中另外还支持级联操作ON DELETE CASCADE表示父表删了子表一起删ON DELETE SET NULL表示父表删了子表外键列置为 NULL还有RESTRICT和NO ACTION表示父表有子表引用时不允许删除。先说结论在业务系统开发中外键该不该用业界有完全对立的两派。坚持用的人认为外键是数据库保证引用完整性的最佳手段可以在应用层少写一堆“先查父表是否存在”的代码反对的人则认为外键会带来额外的锁开销、影响写入性能而且互联网高并发场景下应用层往往比数据库层更能灵活控制数据一致性。我个人的实践原则是对一致性要求极高、写入并发不高的内部系统用外键省心对高并发互联网业务、或者数据有分库分表诉求的系统不用外键但必须在应用层做完整的关联校验和清理逻辑。需要提醒的一点是如果决定不用外键就必须手动处理“孤儿数据”问题——例如用户删了他名下的订单怎么办是在应用层先删订单再删用户还是把订单的用户ID置为 NULL这些问题如果没有规范时间长了库里面一定会出现“查不到用户的订单记录”到时候做数据清洗比当初加一个外键痛苦十倍。2.6 检查约束 CHECK数据库替你执行业务规则CHECK约束允许你指定一个布尔表达式写入时数据库会校验表达式是否为真。比如年龄不能为负age INT CHECK (age 0)状态只能是 0 或 1status TINYINT CHECK (status IN (0, 1))。这里有一个必须明确的坑MySQL 8.0.16 之前CHECK约束是“解析但不执行”的也就是说你建表时写了它不会报错但它不生效数据照样能插入非法值。8.0.16 之后才真正开始强制执行。如果你的生产库是 5.7 或更早的版本不要以为加了CHECK就万事大吉。即使是 8.0 之后也要谨慎设计CHECK表达式避免过于复杂的表达式影响写入性能。我的经验是能用ENUM或TINYINT 应用层枚举校验的场景不必依赖CHECK但它对于那些“数据库直接被人用 SQL 操作”的场合仍是很好的兜底。3. 实操从零设计一张带约束的用户订单表3.1 需求梳理与建表语句光说概念不过瘾直接上一套完整的表设计。假设我们需要做一个电商系统最小模型用户表 订单表 订单明细表。需求点很简单用户必须有手机号且唯一订单必须有用户、有金额、状态有限制订单明细属于某一笔订单且商品数量必须为正。下面是完整的建表语句我会在关键地方加注释CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户ID, phone VARCHAR(20) NOT NULL COMMENT 手机号, nickname VARCHAR(50) DEFAULT NULL COMMENT 昵称允许为空, status TINYINT NOT NULL DEFAULT 1 COMMENT 1-正常 0-禁用, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone), CONSTRAINT ck_user_status CHECK (status IN (0, 1)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表; CREATE TABLE orders ( 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-已取消, create_time 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_orders_user FOREIGN KEY (user_id) REFERENCES user (id), CONSTRAINT ck_orders_status CHECK (status IN (0, 1, 2)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表; CREATE TABLE order_item ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 明细ID, order_id BIGINT UNSIGNED NOT NULL COMMENT 所属订单ID, product_id BIGINT UNSIGNED NOT NULL COMMENT 商品ID, quantity INT UNSIGNED NOT NULL COMMENT 购买数量, price DECIMAL(10,2) NOT NULL COMMENT 成交单价, PRIMARY KEY (id), KEY idx_order_id (order_id), CONSTRAINT fk_order_item_orders FOREIGN KEY (order_id) REFERENCES orders (id), CONSTRAINT ck_order_item_quantity CHECK (quantity 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单明细表;这套设计覆盖了六种约束中的五种主键、唯一、非空、默认、检查外加外键。实际跑一遍插入测试就能发现无论应用层写多少校验MySQL 这层都能在写入时再次把关。3.2 给约束起名这件事值得多说两句建表时很多新手会忽略给约束命名让 MySQL 自动生成名字。结果就是后面报错的时候错误信息长这样CONSTRAINTorders_chk_1failed——你完全不知道这个chk_1对应的是哪个字段的什么规则。所以我的习惯是显式命名规范统一主键不用额外命名PRIMARY KEY本身就是名字唯一约束用uk_字段名外键用fk_表名_字段名检查约束用ck_表名_字段名。这样报错的时候能一眼定位问题。虽然多敲几个字符但在排查问题上的回报非常可观。另外说一个容易被忽略的点外键约束要求被引用的列必须是索引列。上面的创建语句里user.id是主键天然有索引所以没问题。但如果想引用一个有唯一约束的列比如用用户表的phone做外键那个列也已经有唯一索引了同样满足条件。换句话说外键不能随便指向某个没有索引的普通列这一点建表失败时 MySQL 会直接给出提示但提前知道能少走弯路。3.3 表建完了后面还能不能加约束很多项目是先上线跑着后面才发现需要补约束。这时就要用到ALTER TABLE。比如给订单表的user_id补外键约束ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES user (id);简单吗语法很简单但小表和大表完全是两个世界。对小表执行这条语句是毫秒级对千万级数据的表执行MySQL 的默认行为是拿元数据锁可能长时间阻塞该表的写入线上就等着报警吧。大表加约束的正确姿势是用在线变更工具比如某常用的开源 MySQL 在线 DDL 工具在低峰期操作并且先在从库上验证。另一个常见场景是“补唯一约束去重”。表里已经存在重复数据时直接加唯一约束会失败MySQL 会报出重复的键值。这时候得先写查询把重复记录找出来决定保留哪一条、删除哪一条数据清理干净之后再加约束。这一步没有捷径纯靠 SQL 写条件和业务方确认保留规则。所以最稳妥的策略永远是建表阶段就把约束想清楚而不是上线之后再亡羊补牢。4. 约束和索引剪不断理还乱的“兄弟关系”4.1 哪些约束会自动创建索引很多人没意识到约束不只是一个“规则”它在底层还会自动产生索引结构。主键约束和唯一约束创建时都会自动生成一个对应的唯一索引。这意味着给email加了唯一约束之后顺带获得了一个可用于精确查找的索引这是意外之喜。外键约束的情况略复杂。InnoDB 要求被引用的父表列必须有索引子表的外键列则是另一回事——如果子表的外键列本身没有索引MySQL 会在创建外键约束时自动为它建一个普通索引。这个自动索引的意义在于删除或更新父表记录时需要快速定位子表中有没有关联记录子表外键列没有索引InnoDB 就不得不做全表扫描锁的范围会大得多。我自己就在生产环境踩过这个坑有一张日志表的外键指向主表建表时没主动给外键列建索引MySQL 自动建了当时没注意。后来每天定时清理主表过期数据每次清理都导致日志表被大面积锁定主业务写入被拖慢。排查了一圈才意识到自动建的索引命名不规范且没走对联合索引组合手动调整了索引之后锁问题才消失。所以给外键列建索引不只是性能优化更是避免锁竞争的基础设施。4.2 约束索引不只是“校验工具”还是查询利器退一步讲即使不考虑 SQL 执行时对约束的校验约束带来的索引也天然可以被查询优化器使用。唯一约束索引字段上的等值查询、主键上的范围查询、外键列上的关联查询全都走了索引。这里分享一个经验设计索引时不要把约束和索引完全割裂开。比如你打算给“订单表”的user_id和status建联合索引用于“查某个用户某状态下的订单”列表页但同时user_id又被外键约束引用为外键列。此时你其实不需要再为user_id单独建一个单列索引让外键引用的列直接成为联合索引的最左前缀即可。建表时可以把两种诉求合并成一个索引省一份空间也省一份写入开销。不过要注意约束要求的“外键列有索引”约束条件并不要求索引类型一定是 BTreeInnoDB 下基本无差异也不要求索引一定只有该列。只要存在一个以该列作为最左前缀的索引就能满足外键约束的要求。这是我在整理冗余索引时才发现的一个实用点先满足外键约束的索引需求再考虑查询场景做联合索引两者不冲突只是要想清楚顺序。5. 高频问题排查与避坑手册5.1 插入/更新失败时的三类典型报错约束在校验失败时会直接抛错终止 SQL。常见的报错大概逃不出这三类。第一类是唯一键冲突。错误信息通常是Duplicate entry xxx for key uk_xxx。排查思路很简单先搞清楚业务上为什么会产生重复数据是并发下重复提交还是历史数据本身就有重复。如果是并发写入“先查后插”的逻辑并不能完全防重正确做法是直接靠唯一约束兜底应用层捕获到冲突后做对应提示或重试。第二类是外键关联失败。错误信息通常是Cannot add or update a child row: a foreign key constraint fails。这表示你插入或更新时传入的外键值在父表中找不到对应的记录。最常见的场景是子表先插入了数据父表才插入顺序反了或者批量导入时没有按父子顺序导入。若只是临时排数据可以先用SET foreign_key_checks 0暂时关闭外键检查导完再打开但生产库上不要随意这么做关闭期间写入的脏数据不会在重新打开时自动清理。第三类是非空或检查约束失败。非空失败报Column xxx cannot be null检查约束失败报Check constraint ck_xxx is violated。这类问题通常不是偶发而是某个新调用方没按业务规则传值排查时别只在代码里找还要看看是不是有外部系统直接写库、运营手工导数据之类的非正常写入路径。5.2 隐式转换与字符集两个容易忽视的“隐形杀手”约束报错里还有两类问题不细细排查很容易被表象误导。第一类是隐式类型转换。比如外键列是VARCHAR你插入时传的是个整数MySQL 会把整数转成字符串再比对。大多数情况下转换没问题但一旦字符集排序规则不一致比对就可能失败。第二类是字符集不一致父表的列是utf8mb4_unicode_ci子表的外键列是utf8mb4_general_ci虽然都能存中文但 MySQL 在创建外键时会直接报错提示字符集排序规则不匹配。字符集问题在建表时就该统一规范。一套系统所有表统一用utf8mb4排序规则统一省掉后面无数兼容性烦恼。老库里有历史遗留的latin1表和外键关联的字段需要先转换成一致的字符集再建约束。这类问题报错信息比较晦涩我排查过不止一次都是看着“foreign key constraint fails”的报错最后发现根本不是数据问题而是两个字段的字符集定义不同。5.3 自增主键用完了怎么办自增主键默认用BIGINT最多可以到 922亿亿正常业务根本用不完。但很多团队早期图省事用了INT UNSIGNED最大值也就 42 亿左右对高并发流水表来说并非遥不可及。一旦主键达到上限AUTO_INCREMENT不会再往上走插入直接报错而且这个报错很隐蔽看起来像“数据满了”而不是“主键满了”。一张表的主键用完了最彻底的办法是重建表换成BIGINT但这在大表上代价很高。更常见的处理是把表改成归档分区、换新表并且把AUTO_INCREMENT重置继续用。这个问题的真正教训在于建表时不要省那三个字符INT改写成BIGINT的成本几乎为零但将来能省掉的麻烦却是巨大的。6. 关于约束的更深一层思考6.1 约束不是越多越好也不是越少越好约束是把双刃剑。加得太多每一条插入更新都要额外做校验写入性能会打折扣加得太少数据质量没有兜底后期清洗成本更高。我的经验是按三层来考虑一是核心业务主键和唯一性保障这是必须的防重复没有替代方案二是优先级高的非空与业务取值范围规则比如金额、状态、时间这类人人在写但容易写错的值用约束兜底非常划算三是层级之间的引用完整性这个取决于团队对性能的容忍度和架构形态没有绝对的对错。实际项目里我见过有人连“备注”字段也加CHECK (备注字段长度 500)真出了问题报错信息五花八门运维同学查半天才知道是哪个字段的哪条规则拦下来的。约束是为了让错误尽早暴露但暴露的错误也应该能被轻松读懂——所以命名规范、注释齐全是不可省的基本功。6.2 高并发场景下的约束取舍最后聊一个偏架构的话题。很多高并发的互联网业务里整个数据库设计是不用外键的而是把数据一致性交给应用层通过事务补偿、消息队列等方式来保证。这种选择有它的道理外键约束在每次插入更新时都需要额外检查父表还会在父表、子表上增加锁的复杂度在极端高并发下确实会放大数据库的压力。但很多团队是在没必要“高并发”的阶段就盲目跟风把外键禁了。中后台系统、运营系统、内部管理系统QPS 可能都不到一百数据一致性却要求极高这种场景禁用外键纯粹是给自己挖坑——应用层做关联校验的代码容易漏漏一次就是一笔脏数据后期清洗一次的成本远超当初加外键的维护成本。所以我的建议是架构改造不是目的约束选型应该服务于真实的业务场景。你有十万级 QPS、有分库分表计划那外键确实可以不用你只是一个几万用户的内部系统老老实实把外键用起来省心得多。一点个人经验收尾做了这么久的数据库设计和维护我越来越觉得“约束”这东西本质上是把对数据质量的要求写成了“代码”让数据库替我们把关。它不像索引优化那样能直接带来性能跃升也不像 SQL 调优那样有立竿见影的成就感但它对所有下游系统、所有数据使用方的保护是长期的、沉默的。建表阶段多花十分钟仔细定义每一个约束定好命名规范、想清每个字段的业务语义后续系统演进的时候你就会发现这些当时“多写的几行”能够在无数个深夜帮你省下排查脏数据的精力。如果你现在正打算建一张新表不妨把六种约束挨个过一遍看看哪些被你随手省略了——很可能你省略的恰恰是后面最让你头疼的那一条。 做后端开发的谁没被数据库里的脏数据坑过同样的用户注册了两个账号、订单表里挂着不存在的用户ID、字符串字段里莫名其妙塞进一个空字符串……这些问题在项目初期几乎感觉不到等数据量上来、业务复杂了每一处“当时偷懒没加约束”的地方都会变成一个需要写一堆兜底逻辑来填的坑。MySQL表的约束就是数据库在写入时帮你把住数据质量的第一道关卡。这篇文章不打算复述文档而是把我这些年实际建表、踩坑、排查的经验完整拆开讲清楚六大约束分别解决什么问题、底层是怎么工作的、实操中哪些坑是文档里不会告诉你的。无论是刚学SQL的初学者还是写了好几年业务代码但没系统梳理过约束的老手这篇都值得花十分钟看完。1. 容不得脏数据约束到底在守护什么1.1 约束的本质是一份“写入契约”很多人对约束的第一反应是“限制”——限制这不能填、那不能改写起来烦。但换个角度看约束本质上是数据库和应用程序之间的一份契约应用端保证按规则写入数据库端在写入时强制执行校验。这份契约保护的不是开发者的代码而是数据本身的可靠性。举个生活化的例子。你去快递站寄包裹工作人员会让你填一份面单收件人电话不能为空、地址不能瞎写、保价金额不能是负数。这些规则不是故意为难你而是为了保证包裹在后续的转运、派送、理赔环节不会出乱子。数据库里的约束干的完全是同一件事NOT NULL保证关键字段有值CHECK保证取值范围合理UNIQUE保证业务上不允许重复的记录不会出现第二份。没有约束的数据库是什么样的我见过一个老项目用户表里既没有主键也没有唯一约束运营手动导入数据时把同一个手机号导入了三遍结果用户收到了三条注册成功短信登录时都不知道该匹配哪条。排查的时候根本没法说是哪条记录“错了”因为数据库层面没有定义什么叫“对”。这就是约束缺失的代价——错误不是被拦截在源头而是在下游爆雷。1.2 约束拦截的是“协作环节”的错误为什么说约束对多人协作尤其重要因为一套系统不会只有一个开发者在维护。你写的插入逻辑可能很规范但同事写的批量导入脚本、运营直接执行的SQL、第三方系统的接口回调可不一定都遵守同样的规则。约束是最后一道物理防线无论错误来自哪里MySQL 都会在写入时把它挡下来。举个我实际遇到过的情况。某个订单系统接入了支付回调回调消息里带了一个user_id由于上游系统的 bug某个环境里回调里传的是0。如果订单表的user_id没有外键约束这条订单就会带着一个不存在的用户ID写入库中后续做用户维度统计时这笔订单就变成了“幽灵订单”。但如果当初建表时给user_id加了外键指向用户表MySQL 会直接拒绝插入。与其在应用层堆一堆 if 判断不如在数据库层用约束一了百了——这既是纪律也是效率。2. MySQL 六大约束逐个拆开讲MySQL 里常用的约束就六种非空约束、默认值约束、唯一约束、主键约束、外键约束、检查约束。拆开来说每一种都有自己的脾气。2.1 非空约束 NOT NULL最便宜也最容易被忽略NOT NULL的语义很好理解这一列不允许存NULL。但实际建模时很多人的第一反应是“这个字段可能没有值那就允许 NULL 吧”。这句话需要重新审视。一个字段到底允不允许 NULL不应该看“现在有没有值”而应该看“业务上允不允许没有值”。举例说明用户的昵称注册时没填很常见业务上允许没有那就可以允许 NULL但订单的支付金额任何一笔合法订单都必须有一个明确的金额这就是业务上的“必须有”就应该加NOT NULL。把“数据可能缺失”当成“业务允许缺失”来建模是数据库设计里很常见的方向性错误。还有一个容易踩坑的点NULL 和空字符串是完全不同的概念。NULL表示“未知/没有值”空字符串表示“有值值是空字符串”。这带来两个实际影响一是聚合函数COUNT(col)会忽略 NULL 但会计入空字符串导致统计结果和你想象的不一样二是在查询条件里WHERE col 匹配不到 NULL 值必须用IS NULL。在设计阶段就把这两个语义分清楚后面能少排很多莫名其妙的 bug。2.2 唯一约束 UNIQUE业务防重的物理防线UNIQUE约束保证一列或一组列的组合值在表内唯一。和主键不同一个表可以有多个唯一约束而且唯一约束允许 NULL——这是很多人忽略的特性在 MySQL 里唯一约束允许多个 NULL 值共存因为NULL ! NULL数据库认为“未知”和“未知”并不是同一个值。这个特性有好处也有坑。好处是你可以放心地对“手机号”这种可空字段加唯一约束未填手机号的用户可以存多条填了手机号的用户则不允许重复坑在于如果你希望“NULL 也不允许重复”那必须额外用生成列或者业务层逻辑来兜底单纯靠UNIQUE是做不到的。唯一约束和后面讲的主键一样底层都会自动创建一个唯一索引。所以很多时候唯一约束不仅是数据校验手段还能作为查询索引来用——例如在用户表里对email建唯一约束登录按邮箱查询时就能直接走这个索引。2.3 主键约束 PRIMARY KEY每一行的身份证主键约束是唯一约束的加强版它不允许 NULL且一个表只能有一个主键。主键可以是单列也可以是联合主键。主键的选择是建模时最值得纠结的问题之一我一般的原则是能用自增就用自增业务字段当主键要万分谨慎。自增主键的好处是插入性能好B树按顺序写不需要频繁页分裂缺点是暴露了数据量且跨库合并时可能冲突。UUID 主键解决了合并冲突问题但随机字符串在 InnoDB 里插入时容易造成页分裂和碎片化性能上要配合优化业务字段当主键比如用身份证号做用户主键在业务上省事但一旦业务规则变化比如允许一个身份证对应多个账户改主键的成本会非常痛苦。凡是业务上可能变化的值都不要轻易当主键。联合主键用的场景相对少但有一类场景是绕不开的——关联表。例如“用户收藏商品”表自然的主键就是(user_id, product_id)这一对组合它天然表达了“一个用户对一个商品的收藏关系只有一条”。联合主键在建表时可以直接声明也可以在后续用ALTER TABLE ADD PRIMARY KEY补上。2.4 默认值约束 DEFAULT能少写代码就少写DEFAULT用来给字段指定默认值当插入时没有显式传值数据库会自动写入默认值。最典型的应用是“创建时间”create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP这样应用层完全不用管这个字段插入时自动带上当前时间。默认值的设计原则是把“大概率要写的静态值”下沉到数据库。比如订单状态新建订单默认是“待支付”这个字段完全可以在数据库中默认成 0应用层不用反复传。但需要注意默认值并不等于“可以随便不传”。如果字段同时加了NOT NULL插入时业务没有提供一个合理的值数据库会默默用默认值顶上去——这种“静默处理”有时候反而掩盖了应用层的 bug。所以我更倾向的做法是状态这类字段用默认值没问题但金额、ID 这类业务关键字段不要依赖默认值必须由应用显式传入。2.5 外键约束 FOREIGN KEY关系型数据库的“关系”所在外键约束是六种约束里最重、也是争议最大的一种。它保证子表的某个字段取值必须存在于父表的主键或唯一键中另外还支持级联操作ON DELETE CASCADE表示父表删了子表一起删ON DELETE SET NULL表示父表删了子表外键列置为 NULL还有RESTRICT和NO ACTION表示父表有子表引用时不允许删除。先说结论在业务系统开发中外键该不该用业界有完全对立的两派。坚持用的人认为外键是数据库保证引用完整性的最佳手段可以在应用层少写一堆“先查父表是否存在”的代码反对的人则认为外键会带来额外的锁开销、影响写入性能而且互联网高并发场景下应用层往往比数据库层更能灵活控制数据一致性。我个人的实践原则是对一致性要求极高、写入并发不高的内部系统用外键省心对高并发互联网业务、或者数据有分库分表诉求的系统不用外键但必须在应用层做完整的关联校验和清理逻辑。需要提醒的一点是如果决定不用外键就必须手动处理“孤儿数据”问题——例如用户删了他名下的订单怎么办是在应用层先删订单再删用户还是把订单的用户ID置为 NULL这些问题如果没有规范时间长了库里面一定会出现“查不到用户的订单记录”到时候做数据清洗比当初加一个外键痛苦十倍。2.6 检查约束 CHECK数据库替你执行业务规则CHECK约束允许你指定一个布尔表达式写入时数据库会校验表达式是否为真。比如年龄不能为负age INT CHECK (age 0)状态只能是 0 或 1status TINYINT CHECK (status IN (0, 1))。这里有一个必须明确的坑MySQL 8.0.16 之前CHECK约束是“解析但不执行”的也就是说你建表时写了它不会报错但它不生效数据照样能插入非法值。8.0.16 之后才真正开始强制执行。如果你的生产库是 5.7 或更早的版本不要以为加了CHECK就万事大吉。即使是 8.0 之后也要谨慎设计CHECK表达式避免过于复杂的表达式影响写入性能。我的经验是能用ENUM或TINYINT 应用层枚举校验的场景不必依赖CHECK但它对于那些“数据库直接被人用 SQL 操作”的场合仍是很好的兜底。3. 实操从零设计一张带约束的用户订单表3.1 需求梳理与建表语句光说概念不过瘾直接上一套完整的表设计。假设我们需要做一个电商系统最小模型用户表 订单表 订单明细表。需求点很简单用户必须有手机号且唯一订单必须有用户、有金额、状态有限制订单明细属于某一笔订单且商品数量必须为正。下面是完整的建表语句我会在关键地方加注释CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户ID, phone VARCHAR(20) NOT NULL COMMENT 手机号, nickname VARCHAR(50) DEFAULT NULL COMMENT 昵称允许为空, status TINYINT NOT NULL DEFAULT 1 COMMENT 1-正常 0-禁用, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone), CONSTRAINT ck_user_status CHECK (status IN (0, 1)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表; CREATE TABLE orders ( 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-已取消, create_time 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_orders_user FOREIGN KEY (user_id) REFERENCES user (id), CONSTRAINT ck_orders_status CHECK (status IN (0, 1, 2)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表; CREATE TABLE order_item ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 明细ID, order_id BIGINT UNSIGNED NOT NULL COMMENT 所属订单ID, product_id BIGINT UNSIGNED NOT NULL COMMENT 商品ID, quantity INT UNSIGNED NOT NULL COMMENT 购买数量, price DECIMAL(10,2) NOT NULL COMMENT 成交单价, PRIMARY KEY (id), KEY idx_order_id (order_id), CONSTRAINT fk_order_item_orders FOREIGN KEY (order_id) REFERENCES orders (id), CONSTRAINT ck_order_item_quantity CHECK (quantity 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单明细表;这套设计覆盖了六种约束中的五种主键、唯一、非空、默认、检查外加外键。实际跑一遍插入测试就能发现无论应用层写多少校验MySQL 这层都能在写入时再次把关。3.2 给约束起名这件事值得多说两句建表时很多新手会忽略给约束命名让 MySQL 自动生成名字。结果就是后面报错的时候错误信息长这样CONSTRAINTorders_chk_1failed——你完全不知道这个chk_1对应的是哪个字段的什么规则。所以我的习惯是显式命名规范统一主键不用额外命名PRIMARY KEY本身就是名字唯一约束用uk_字段名外键用fk_表名_字段名检查约束用ck_表名_字段名。这样报错的时候能一眼定位问题。虽然多敲几个字符但在排查问题上的回报非常可观。另外说一个容易被忽略的点外键约束要求被引用的列必须是索引列。上面的创建语句里user.id是主键天然有索引所以没问题。但如果想引用一个有唯一约束的列比如用用户表的phone做外键那个列也已经有唯一索引了同样满足条件。换句话说外键不能随便指向某个没有索引的普通列这一点建表失败时 MySQL 会直接给出提示但提前知道能少走弯路。3.3 表建完了后面还能不能加约束很多项目是先上线跑着后面才发现需要补约束。这时就要用到ALTER TABLE。比如给订单表的user_id补外键约束ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES user (id);简单吗语法很简单但小表和大表完全是两个世界。对小表执行这条语句是毫秒级对千万级数据的表执行MySQL 的默认行为是拿元数据锁可能长时间阻塞该表的写入线上就等着报警吧。大表加约束的正确姿势是用在线变更工具比如某常用的开源 MySQL 在线 DDL 工具在低峰期操作并且先在从库上验证。另一个常见场景是“补唯一约束去重”。表里已经存在重复数据时直接加唯一约束会失败MySQL 会报出重复的键值。这时候得先写查询把重复记录找出来决定保留哪一条、删除哪一条数据清理干净之后再加约束。这一步没有捷径纯靠 SQL 写条件和业务方确认保留规则。所以最稳妥的策略永远是建表阶段就把约束想清楚而不是上线之后再亡羊补牢。4. 约束和索引剪不断理还乱的“兄弟关系”4.1 哪些约束会自动创建索引很多人没意识到约束不只是一个“规则”它在底层还会自动产生索引结构。主键约束和唯一约束创建时都会自动生成一个对应的唯一索引。这意味着给email加了唯一约束之后顺带获得了一个可用于精确查找的索引这是意外之喜。外键约束的情况略复杂。InnoDB 要求被引用的父表列必须有索引子表的外键列则是另一回事——如果子表的外键列本身没有索引MySQL 会在创建外键约束时自动为它建一个普通索引。这个自动索引的意义在于删除或更新父表记录时需要快速定位子表中有没有关联记录子表外键列没有索引InnoDB 就不得不做全表扫描锁的范围会大得多。我自己就在生产环境踩过这个坑有一张日志表的外键指向主表建表时没主动给外键列建索引MySQL 自动建了当时没注意。后来每天定时清理主表过期数据每次清理都导致日志表被大面积锁定主业务写入被拖慢。排查了一圈才意识到自动建的索引命名不规范且没走对联合索引组合手动调整了索引之后锁问题才消失。所以给外键列建索引不只是性能优化更是避免锁竞争的基础设施。4.2 约束索引不只是“校验工具”还是查询利器退一步讲即使不考虑 SQL 执行时对约束的校验约束带来的索引也天然可以被查询优化器使用。唯一约束索引字段上的等值查询、主键上的范围查询、外键列上的关联查询全都走了索引。这里分享一个经验设计索引时不要把约束和索引完全割裂开。比如你打算给“订单表”的user_id和status建联合索引用于“查某个用户某状态下的订单”列表页但同时user_id又被外键约束引用为外键列。此时你其实不需要再为user_id单独建一个单列索引让外键引用的列直接成为联合索引的最左前缀即可。建表时可以把两种诉求合并成一个索引省一份空间也省一份写入开销。不过要注意约束要求的“外键列有索引”约束条件并不要求索引类型一定是 BTreeInnoDB 下基本无差异也不要求索引一定只有该列。只要存在一个以该列作为最左前缀的索引就能满足外键约束的要求。这是我在整理冗余索引时才发现的一个实用点先满足外键约束的索引需求再考虑查询场景做联合索引两者不冲突只是要想清楚顺序。5. 高频问题排查与避坑手册5.1 插入/更新失败时的三类典型报错约束在校验失败时会直接抛错终止 SQL。常见的报错大概逃不出这三类。第一类是唯一键冲突。错误信息通常是Duplicate entry xxx for key uk_xxx。排查思路很简单先搞清楚业务上为什么会产生重复数据是并发下重复提交还是历史数据本身就有重复。如果是并发写入“先查后插”的逻辑并不能完全防重正确做法是直接靠唯一约束兜底应用层捕获到冲突后做对应提示或重试。第二类是外键关联失败。错误信息通常是Cannot add or update a child row: a foreign key constraint fails。这表示你插入或更新时传入的外键值在父表中找不到对应的记录。最常见的场景是子表先插入了数据父表才插入顺序反了或者批量导入时没有按父子顺序导入。若只是临时排数据可以先用SET foreign_key_checks 0暂时关闭外键检查导完再打开但生产库上不要随意这么做关闭期间写入的脏数据不会在重新打开时自动清理。第三类是非空或检查约束失败。非空失败报Column xxx cannot be null检查约束失败报Check constraint ck_xxx is violated。这类问题通常不是偶发而是某个新调用方没按业务规则传值排查时别只在代码里找还要看看是不是有外部系统直接写库、运营手工导数据之类的非正常写入路径。5.2 隐式转换与字符集两个容易忽视的“隐形杀手”约束报错里还有两类问题不细细排查很容易被表象误导。第一类是隐式类型转换。比如外键列是VARCHAR你插入时传的是个整数MySQL 会把整数转成字符串再比对。大多数情况下转换没问题但一旦字符集排序规则不一致比对就可能失败。第二类是字符集不一致父表的列是utf8mb4_unicode_ci子表的外键列是utf8mb4_general_ci虽然都能存中文但 MySQL 在创建外键时会直接报错提示字符集排序规则不匹配。字符集问题在建表时就该统一规范。一套系统所有表统一用utf8mb4排序规则统一省掉后面无数兼容性烦恼。老库里有历史遗留的latin1表和外键关联的字段需要先转换成一致的字符集再建约束。这类问题报错信息比较晦涩我排查过不止一次都是看着“foreign key constraint fails”的报错最后发现根本不是数据问题而是两个字段的字符集定义不同。5.3 自增主键用完了怎么办自增主键默认用BIGINT最多可以到 922亿亿正常业务根本用不完。但很多团队早期图省事用了INT UNSIGNED最大值也就 42 亿左右对高并发流水表来说并非遥不可及。一旦主键达到上限AUTO_INCREMENT不会再往上走插入直接报错而且这个报错很隐蔽看起来像“数据满了”而不是“主键满了”。一张表的主键用完了最彻底的办法是重建表换成BIGINT但这在大表上代价很高。更常见的处理是把表改成归档分区、换新表并且把AUTO_INCREMENT重置继续用。这个问题的真正教训在于建表时不要省那三个字符INT改写成BIGINT的成本几乎为零但将来能省掉的麻烦却是巨大的。6. 关于约束的更深一层思考6.1 约束不是越多越好也不是越少越好约束是把双刃剑。加得太多每一条插入更新都要额外做校验写入性能会打折扣加得太少数据质量没有兜底后期清洗成本更高。我的经验是按三层来考虑一是核心业务主键和唯一性保障这是必须的防重复没有替代方案二是优先级高的非空与业务取值范围规则比如金额、状态、时间这类人人在写但容易写错的值用约束兜底非常划算三是层级之间的引用完整性这个取决于团队对性能的容忍度和架构形态没有绝对的对错。实际项目里我见过有人连“备注”字段也加CHECK (备注字段长度 500)真出了问题报错信息五花八门运维同学查半天才知道是哪个字段的哪条规则拦下来的。约束是为了让错误尽早暴露但暴露的错误也应该能被轻松读懂——所以命名规范、注释齐全是不可省的基本功。6.2 高并发场景下的约束取舍最后聊一个偏架构的话题。很多高并发的互联网业务里整个数据库设计是不用外键的而是把数据一致性交给应用层通过事务补偿、消息队列等方式来保证。这种选择有它的道理外键约束在每次插入更新时都需要额外检查父表还会在父表、子表上增加锁的复杂度在极端高并发下确实会放大数据库的压力。但很多团队是在没必要“高并发”的阶段就盲目跟风把外键禁了。中后台系统、运营系统、内部管理系统QPS 可能都不到一百数据一致性却要求极高这种场景禁用外键纯粹是给自己挖坑——应用层做关联校验的代码容易漏漏一次就是一笔脏数据后期清洗一次的成本远超当初加外键的维护成本。所以我的建议是架构改造不是目的约束选型应该服务于真实的业务场景。你有十万级 QPS、有分库分表计划那外键确实可以不用你只是一个几万用户的内部系统老老实实把外键用起来省心得多。一点个人经验收尾做了这么久的数据库设计和维护我越来越觉得“约束”这东西本质上是把对数据质量的要求写成了“代码”让数据库替我们把关。它不像索引优化那样能直接带来性能跃升也不像 SQL 调优那样有立竿见影的成就感但它对所有下游系统、所有数据使用方的保护是长期的、沉默的。建表阶段多花十分钟仔细定义每一个约束定好命名规范、想清每个字段的业务语义后续系统演进的时候你就会发现这些当时“多写的几行”能够在无数个深夜帮你省下排查脏数据的精力。如果你现在正打算建一张新表不妨把六种约束挨个过一遍看看哪些被你随手省略了——很可能你省略的恰恰是后面最让你头疼的那一条。