先分享一个我印象特别深的场景。前几年做电商项目接口幂等逻辑测试全过结果大促当天同一笔订单被两个并发请求同时创建成功。查到最后根子不在代码而在表设计上订单号字段忘了加唯一约束。那种半夜爬起来导出数据、拿Excel逐行标红的滋味经历过的人都懂。MySQL 单表约束——主键、非空、唯一、默认——看着就是建表时多写几行代码的事实际上是你给数据入口设置的最后一道防线。这篇内容适合刚学 MySQL 的人系统过一遍约束概念也适合平时 SQL 没少写、但从来没有把约束整理成体系的开发者。我会把底层原理、建表写法、修改手法和实际踩坑一起讲透。1. 约束的根本使命把数据完整性从应用层下沉到数据层1.1 没有单表约束时我在生产环境看到的脏数据现场先说几个我亲眼见过的案例。第一件用户表没有唯一约束两个运营后台同时导入Excel同一个手机号注册了两条账号记录最后 CRM 系统里同一个客户关联了两个 ID后续所有统计全部对不上。第二件订单明细表没有非空约束前端漏传商品ID程序没做校验一条product_id NULL的记录就这么进了库下游报表计算金额时这一行被静默忽略月底对账差出几十万。第三件状态字段没有默认值开发偷懒不传status结果库里一半是 NULL一半是 0查询条件WHERE status 0永远查不全数据。这些问题的共同点在于出问题的时间点不在数据写入的那一刻而在几个月后某个统计需求找上来的时候。脏数据一旦进入生产库清洗成本极高——你要么写一堆临时脚本去猜当时业务上应该是什么要么接受报表数据永远有偏差。更麻烦的是你根本不知道到底有多少行数据受影响。随着数据量增长这种不确定性只会越来越大。所以单表约束首要价值不是让建表好看而是阻止脏数据进入存储层。1.2 为什么约束比应用层校验更可靠很多人会说我在应用接口里做校验不就行了字段必填我在后端判断一下唯一性我先查一次库再插入。道理没错但应用层校验有个天然缺陷它只覆盖你写的那条业务链路。现实情况是一张表通常有多个写入入口主站接口、管理后台、定时任务、数据同步脚本、运营临时跑的一条 UPDATE、DBA 手工修复数据的 SQL甚至同事用 Navicat 打开表直接改了几行。你不可能在每个入口都保证校验逻辑一致。等哪天某个脚本绕过业务逻辑直连数据库灌数据应用层校验就是一张废纸。数据库约束则不同它由存储引擎强制执行。只要数据要落进这张表不管是哪个客户端、哪条链路、哪个人手工敲 SQL都必须遵守规则。这就好比小区门口的交规不依赖每个司机自觉而是有摄像头和交警在管。把完整性规则下沉到数据库层才是真正对所有写入者一视同仁。1.3 单表约束全景四种约束各管一段在深入细节之前先用一张表把 MySQL 单表约束的全貌立起来约束类型关键字核心作用底层实现主键约束PRIMARY KEY唯一标识每一行不允许重复和 NULL聚簇索引非空约束NOT NULL字段不允许为 NULL存储引擎强制检查唯一约束UNIQUE字段值不允许重复但允许多个 NULL唯一二级索引默认值约束DEFAULT未显式赋值时自动填充指定值插入时自动补全补充一句MySQL 8.0.16 及以后才真正支持 CHECK 约束在此之前你写的 CHECK 约束语法能通过但引擎会直接忽略并没有实际约束力。这个点很多人不知道我会在后面的实战经验部分专门展开。现阶段先把主键、非空、唯一、默认这四类核心约束吃透已经能解决 90% 的表设计问题。2. 主键约束每张表都该有一张身份证2.1 三种主键定义写法以及各自适合的场景主键的定义有两种位置列级定义也就是直接跟在字段后面表级定义写在字段列表最后。先看列级的写法CREATE TABLE student ( id INT PRIMARY KEY, name VARCHAR(32) NOT NULL, class_id INT NOT NULL );这种写法适合单个字段做主键、且主键逻辑简单清晰的情况。如果你需要两个字段联合起来才能唯一标识一行就得用表级定义CREATE TABLE order_item ( order_id BIGINT NOT NULL, item_no INT NOT NULL, product_id BIGINT NOT NULL, quantity INT NOT NULL, PRIMARY KEY (order_id, item_no) );这是典型的复合主键场景。同一个订单下可能有多个商品条目单独拿order_id当主键会撞车单独拿item_no也不行两个字段合起来才唯一。复合主键在建表时只能用表级定义因为单个字段根本没有组合的概念。还有第三种写法把主键约束和索引一起显式命名方便后续对约束的管理CREATE TABLE student ( id INT NOT NULL, name VARCHAR(32) NOT NULL, CONSTRAINT pk_student_id PRIMARY KEY (id) );用CONSTRAINT pk_student_id给主键起名这样你将来查看约束、删除约束时就有明确的标识。但要记住MySQL 里主键约束名通常固定为 PRIMARY直接写PRIMARY KEY也不会影响使用显式命名更多是为了格式统一。2.2 自增主键为什么是默认选择一个关于索引结构的理由主键字段最常用的搭配是AUTO_INCREMENT也就是自增整数。这样写有存储层面的深层原因InnoDB 表的数据本身是按照主键聚簇存放的自增主键在插入时永远追加在索引树的最后不需要频繁移动已有数据写入性能最稳定。而很多新手喜欢用业务单号或者 UUID 做主键这个出发点可以理解——业务字段将来要按照它查询。但随机字符串做主键会带来两个实际问题一是插入位置随机导致页分裂和索引碎片化写入性能随表变大明显下降二是 UUID 是 16 字节比 INT 的 4 字节和 BIGINT 的 8 字节大得多聚簇索引的所有二级索引里都会冗余这份主键值内存和磁盘占用都吃亏。我的习惯是每个业务表都配一个与业务无关的自增或雪花 ID 做物理主键至于订单号、手机号这些业务上要唯一的字段单独用唯一约束去管。主键管怎么存储唯一约束管业务上不能重复两者职责分开表设计会清爽很多。2.3 删除或失效主键后InnoDB 会怎么处理MySQL 本身没有主键失效这个操作更常见的场景是你要删除主键或者因为重建表结构导致主键定义被改动。很多人不知道删除主键之后 InnoDB 内部的连锁反应。如果你在建表时既没有主键也没有任何非空唯一索引InnoDB 会自动生成一个不可见的 6 字节 ROWID 作为聚簇索引依据。如果一张表原本有主键你执行删除主键之后InnoDB 会去找表里第一个非空唯一索引充当新的聚簇索引如果找不到就会退回隐藏 ROWID 模式。这里有个容易忽略的代价删除主键意味着聚簇索引重建InnoDB 需要把整张表的数据重新组织一遍。表越大这个操作耗时越长期间对表的读写都会受影响。所以别把删除主键当成一条简单 SQL这些操作我都会在第五部分讲 ALTER TABLE 时一起展开。另外还要提醒一点不要指望用业务字段同时当主键又当业务唯一键来省事。主键设计要服务于数据存储的稳定性业务唯一性用 UNIQUE 约束表达两者的语义完全不同混在一起只会让后续改动投鼠忌器。2.4 主键与唯一索引的一个关键区别主键和唯一索引在值不能重复这一点上很像所以时不时有人混淆。它们的区别集中在三点第一一张表只能有一个主键但可以有多个唯一索引第二主键字段不允许为 NULL唯一索引在 MySQL 里允许有多个 NULL第三主键是聚簇索引直接决定数据物理存储顺序唯一索引只是普通二级索引。判断标准很简单如果这个字段既不能为空、也不能重复、而且每张表只能有一份那就做主键如果字段只是业务上不能重复但允许还没填的空状态优先用唯一约束。后面讲唯一约束的 NULL 行为时这一点会更清晰。3. 非空与默认值两个互补的守门员3.1 NULL 和空字符串不是一回事这是统计口径错乱的根源很多初学者以为 NULL 和差不多其实这是两类完全不同的值。NULL 表示这个字段没有值它不参与任何常规的比较运算空字符串是一个长度为 0 的字符串类型的真实值。你可以用WHERE phone 查询空字符串但查 NULL 必须用WHERE phone IS NULL。这个差异在生产环境会直接导致统计结果错乱。比如你统计缺失手机号的用户数用SELECT COUNT(*) FROM user WHERE phone IS NULL查出来是 500 人用SELECT COUNT(*) FROM user WHERE phone 查出来又是 300 人两边相加才等于真正缺失的数量。更麻烦的是COUNT(phone)这类聚合函数会自动忽略 NULL如果业务上没手机号存的是 NULL计算的时候这一行就会人间蒸发。还有一个容易被忽略的坑两个字段做字符串拼接时只要其中一个为 NULL整个拼接结果就是 NULL。比如CONCAT(first_name, last_name)只要 last_name 是 NULL哪怕 first_name 有值输出也是 NULL。在实际业务中姓和名这种字段一旦允许 NULL就会出现大量显示为空的记录。所以我的建议是业务上明确必须要有值的字段直接加NOT NULL确实允许没有值的字段也要想清楚到底是允许 NULL 还是允许空字符串二选一制定统一标准。大多数面向报表的业务表里把字符串字段设计成NOT NULL DEFAULT 比允许 NULL 更好用因为查询、聚合、拼接行为都可预期。3.2 DEFAULT 的常规姿势时间戳、状态值和开关字段默认值约束和主键不一样主键解决这行是谁默认值解决没传时我该填什么。最常见的场景有三个。第一个是时间字段。创建时间和更新时间几乎是每张业务表的标配create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间ON UPDATE CURRENT_TIMESTAMP的意思是只要这行数据发生 UPDATE该字段自动刷新为当前时间不用应用层手动维护。这个写法在 MySQL 5.6.5 以后对 DATETIME 有效之前版本只有 TIMESTAMP 能用如果你还在维护老库要注意。第二个是状态字段。状态码、删除标记、审核标识这些字段应当给出明确的默认值status TINYINT NOT NULL DEFAULT 0 COMMENT 0正常 1禁用, is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT 0未删除 1已删除这样做的好处是应用层漏传这个字段时数据不会变成 NULL而是落一个确定的初始值。业务上可以放心用WHERE status 0查询不用担心 NULL 漏数据。第三个是展示类字段比如昵称。用户没设置昵称你可以给DEFAULT 匿名用户而不是让字段为 NULL 或者空字符串前端展示时就不用写一堆判空逻辑。本质上默认值是在应用层逻辑缺失时由数据库帮你兜一个合理的底。3.3 一条建表 SQL把主键、非空、默认值串起来光讲概念容易飘直接看一条我认为典型且完整的用户表建表语句CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, email VARCHAR(64) NOT NULL COMMENT 登录邮箱, phone VARCHAR(20) NOT NULL DEFAULT COMMENT 手机号, nickname VARCHAR(32) NOT NULL DEFAULT 匿名用户 COMMENT 昵称, status TINYINT NOT NULL DEFAULT 0 COMMENT 0正常 1禁用, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;这里每个字段都刻意加了NOT NULL并且能设默认值的都设了默认值。email是登录账号必须非空同时加了唯一约束防止重复注册phone允许还没绑定但存空字符串而不是 NULL查询统一nickname没设置时给一个可见的默认昵称状态和时间字段全部由数据库填充默认值。这样一张表应用层即使有字段漏传数据进库后也一定是完整、可预期的而不是一堆 NULL。3.4 sql_mode 对非空约束的影响为什么有时候没传也不报错这里必须聊一个让很多人困惑的点同样是插入数据时字段缺失有些环境报错有些环境却能成功。关键在sql_mode。MySQL 的严格模式由STRICT_TRANS_TABLES控制。开启严格模式后向一个NOT NULL且没有默认值的字段插入 NULL 或缺失值会直接报错关闭严格模式时MySQL 会静默地把该字段替换成隐式默认值——数值类型填 0字符串类型填空字符串然后只给一个警告。很多开发在本地环境没开严格模式测试时数据照样能插进去上线后突然发现插入报错就是这个原因。你可以在会话里用SELECT sql_mode;查看当前设置。我强烈建议生产环境开启严格模式否则非空约束形同虚设数据完整性依然无从谈起。这也是很多人以为我加了 NOT NULL 但好像没生效的真正幕后黑手。4. 唯一约束防重复的最后防线4.1 业务唯一键的典型场景与三种创建方式主键解决每行必须不同但业务上有些字段也有不能重复的需求它们不是主键却必须唯一典型如用户邮箱、手机号、订单号、身份证号、优惠券码等。这些场景都要用唯一约束。创建方式有三种。第一种列级定义适合单字段唯一CREATE TABLE user ( email VARCHAR(64) NOT NULL UNIQUE );第二种表级定义并起名适合单字段唯一且需要显式管理约束名CREATE TABLE user ( email VARCHAR(64) NOT NULL, UNIQUE KEY uk_email (email) );第三种复合唯一约束适合多字段组合才唯一的情况。比如同一个用户不能对同一件商品重复评价CREATE TABLE review ( user_id BIGINT NOT NULL, product_id BIGINT NOT NULL, content VARCHAR(500) NOT NULL, UNIQUE KEY uk_user_product (user_id, product_id) );复合唯一约束里字段顺序有讲究(user_id, product_id)和(product_id, user_id)创建的索引结构不同后续查询如果常用WHERE user_id ?前者更合适因为能直接命中索引左前缀。4.2 MySQL 唯一约束的 NULL 特性可以留白但不能撞脸唯一约束最反直觉的地方在于它对 NULL 的处理。在 MySQL 中唯一约束允许多个 NULL 同时存在。也就是说字段email VARCHAR(64) UNIQUE表中可以插入很多行email NULL因为这些 NULL 互不冲突。这个行为在别的数据库里并不统一有的只允许一个 NULL所以在不同数据库间迁移时容易踩雷。但在 MySQL 里你只要记住唯一约束保证非 NULL 值不重复NULL 之间随意。如果你希望连空值也只能出现一次那就把字段加上NOT NULL从根源上禁止 NULL 出现。还有一个更隐蔽的坑空字符串是真实值不是 NULL。所以一个唯一字段你用DEFAULT 来代表未填写那表中就只能有一条第二条插入时就会报重复。设计可空但需可重复的字段时要么允许 NULL要么干脆不建唯一约束。4.3 给已有重复数据的表加唯一约束完整处理流程这是热搜词里mysql设置唯一已经有重复数据库对应的典型场景。直接执行ALTER TABLE user ADD UNIQUE KEY uk_email(email);大概率会报错Duplicate entry。原因是表里已经存在重复值。完整处理流程分三步。第一步找出重复数据。用分组聚合定位SELECT email, COUNT(*) AS cnt FROM user GROUP BY email HAVING cnt 1;第二步处理重复数据。具体怎么处理取决于业务可以给重复记录里保留一行更新其他行的 email 为新的唯一值也可以把重复记录合并甚至可以删除无效记录。这一步涉及数据修复操作前一定先备份原表。我自己习惯先建一张备份表比如CREATE TABLE user_bak_20250101 AS SELECT * FROM user;再把重复数据处理干净。第三步确认没有重复后再添加唯一约束ALTER TABLE user ADD UNIQUE KEY uk_email (email);添加成功后再把备份表删掉。整套流程最怕中间跳过第一步直接加约束错误信息只能告诉你有重复不会告诉你是哪几行等数据量大了之后再回头定位重复项代价会非常大。4.4 唯一约束在并发写入中的兜底作用并发条件下唯一约束的价值会被放大。典型的场景是发券两个请求同时过来应用层都先查了这张券是否已领取都发现没有记录然后同时执行 INSERT。如果券码字段没有唯一约束这两条都会成功等于同一张券被发了两次。应用层的先查后插存在天然的竞态窗口任何并发控制都做不到百分百可靠但数据库层面唯一约束的校验是原子性的两个并发 INSERT 中必然有一个失败。所以我在设计接口时会这样看待唯一约束它不是让你不写业务校验的借口而是业务校验之外的兜底防线。业务代码的正常流程该查就查、该报友好错就报但数据库这层必须保证无论如何都重复不了。这也是很多高并发系统里幂等控制最终落到一张带唯一约束的幂等表上的原因——约束不认网络延迟不认进程调度不认分布式时钟。5. 建表之后的反悔药用 ALTER TABLE 修改与删除约束5.1 增删主键先解除自增再动手给一张表加主键很简单ALTER TABLE student ADD PRIMARY KEY (id);但要保证id列既没有 NULL 也没有重复值否则执行会失败。删除主键时有一个关键前置条件如果主键列是自增的必须先把自增属性去掉否则会报错。正确步骤是先改列定义再删主键ALTER TABLE student MODIFY id INT NOT NULL; ALTER TABLE student DROP PRIMARY KEY;第一步去掉AUTO_INCREMENT第二步才能真正删掉主键。我还想提醒一句删除主键意味着聚簇索引重建InnoDB 内部会对整张表重新组织。生产环境的大表千万不要直接执行最好在维护窗口操作并且在测试环境先跑一遍预估耗时。5.2 增删非空与默认值最容易把列定义写丢的操作增加和删除非空约束标准的姿势是用 MODIFY 重定义整列ALTER TABLE user MODIFY email VARCHAR(64) NOT NULL; ALTER TABLE user MODIFY email VARCHAR(64) NULL;这里最坑的地方在于MODIFY 是整列覆盖定义你写的字段类型长度必须和原来完全一致不然会被顺手改掉。我见过不止一次有人想给 email 加非空写成了ALTER TABLE user MODIFY email VARCHAR(32) NOT NULL;结果原先的 VARCHAR(64) 被缩成了 VARCHAR(32)存量数据超过 32 字符的长邮箱直接截断或报错。正确的做法是先把完整列定义查出来在原有基础上加约束ALTER TABLE user MODIFY email VARCHAR(64) NOT NULL COMMENT 登录邮箱;默认值的增删语法略有不同不需要重写整列ALTER TABLE user ALTER COLUMN status SET DEFAULT 0; ALTER TABLE user ALTER COLUMN status DROP DEFAULT;给已有数据的表添加非空约束之前记得先检查列里有没有 NULL。有 NULL 的时候直接加会失败需要先补全或清洗数据UPDATE user SET phone WHERE phone IS NULL;5.3 增删唯一约束删除时记住它本质是索引添加唯一约束很容易ALTER TABLE user ADD UNIQUE KEY uk_email (email);删除时有个需要记住的点唯一约束在 InnoDB 里会创建一个唯一二级索引所以你删除它时用的不是DROP CONSTRAINT而是DROP INDEXALTER TABLE user DROP INDEX uk_email;如果当初建约束时没起名MySQL 默认会用字段名作为索引名比如删email上的唯一约束时可能用ALTER TABLE user DROP INDEX email;。不确定名字时先执行SHOW INDEX FROM user;看清楚索引名再动手。这里顺便提一句唯一索引和普通索引都靠 DROP INDEX 删除千万不要因为它是约束就去搜 DROP CONSTRAINTMySQL 会报语法错误。5.4 查看表结构与约束信息的三个命令遇到任何约束到底加没加索引名是什么的疑问最有效的排查方式是看当前的建表语句SHOW CREATE TABLE user;一条命令能看到所有约束主键、非空、默认值、唯一索引全部列出来比翻当初的建表脚本可靠得多因为所有 ALTER 修改都已经反映在里面。想看索引详细信息SHOW INDEX FROM user;输出里Non_unique列等于 0 的就是唯一索引等于 1 的是普通索引Key_name是索引名。如果想通过系统表做自动化检查可以查信息模式SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME user;这三个命令配合使用基本能应对日常所有约束排查需求。我每次做表结构评审时第一件事就是跑一遍SHOW CREATE TABLE把全表约束状态摸清楚再下结论。6. 生产环境使用约束的几条实战经验6.1 约束命名规范可读性决定了维护效率约束名不是为了好看而是为了三个月后你自己还能一眼看懂。我推荐一套简单的命名规范主键直接用 PRIMARY KEY唯一约束统一叫uk_表名_字段名普通索引叫idx_表名_字段名。比如用户表的 email 唯一约束就是uk_user_email订单明细表的复合唯一约束就是uk_order_item_order_id_item_no。这套规范最大的好处是任何人看到uk_前缀就知道这是一个唯一约束看到字段名就知道它在约束什么。尤其在执行DROP INDEX时名字含义清晰能避免误删。如果每个人建约束时都随手起一个毫无规律的名字等你需要维护几十张表时查索引名都会查到崩溃。6.2 大表加约束之前想清楚窗口期和工具方案在千万行级别的大表上执行 ALTER TABLE不管加主键、加唯一约束还是加非空约束都可能引发长时间的表重建或索引重建期间会产生锁竞争、IO 飙升、主从延迟。我的经验是三个原则第一操作前在测试环境用相同数据量级跑一遍拿到真实耗时第二选择业务低峰期执行并且提前准备好回滚方案第三如果表特别大且不能接受长时间锁表考虑使用在线变更工具来做而不是直接拼一条裸的 ALTER 语句。另外加唯一约束时如果目标字段在历史数据上已经存在大量重复前面第四部分说过直接跑 ALTER 会报错。处理重复数据本身可能涉及 UPDATE 大量行这个操作同样会影响线上。所以更好的顺序是先写好数据清理脚本并验证再执行加约束的 DDL把数据修复和 DDL 变更拆成两个独立窗口避免互相影响。6.3 小心 MySQL 8.0.16 之前看起来有 CHECK 约束的假象我在前面提过一句这里展开讲。MySQL 8.0.16 之前的版本CHECK 约束会被 MySQL 解析器接受语法检查通过但存储引擎完全忽略它。也就是说你写age INT CHECK (age 0)它能建表成功但插入age -1照样成功。无数人栽在这个假约束上以为加了约束实际没任何效果。MySQL 8.0.16 之后CHECK 约束才开始被真正强制执行。所以如果你还在用 8.0.16 之前的版本不要依赖 CHECK 做任何完整性校验业务规则请放到应用层升级到 8.0.16 以后CHECK 可以用来做简单的取值范围约束比如状态字段的枚举值校验。但也要注意不要滥用过于复杂的 CHECK 表达式会影响写入性能。6.4 我每次做表结构评审时最后必查的三件事这些年经手了不少表结构评审我发现只要抓住三个核心问题单表约束这一层基本不会出大乱子。第一每张业务表是不是都有主键。没有主键的表在 InnoDB 里只能用隐藏 ROWID 组织数据不仅无法高效定位行而且后续做数据变更、日志解析、分库分表都会遇到麻烦。日志表、临时表可以例外但业务表一定要有主键。第二业务上需要唯一的字段是不是真的加了唯一约束。电商的订单号、用户的手机号、支付的流水号这些不能重复的字段光靠代码保证不够必须落到数据库层。这也是我今天强调最多的一点。第三空值的语义是不是清晰。每个字段到底允许 NULL、还是用空字符串、还是给了默认值必须有一个统一设计。最怕的是同一个含义的字段有些表用 NULL有些表用有些表用 0将来做统计时口径混乱查出来的数据自己都不敢信。把这三件事想清楚你建出来的表至少不会因为约束缺失而在深夜被人叫起来修数据。最后再分享一个我实际操作中的小习惯每次新建一张业务表之前我都会把SHOW CREATE TABLE的输出逻辑在脑子里过一遍假装自己是一个三个月后接手这张表的同事看这个表结构能不能一眼读懂。约束名称清晰、每个字段非空和默认值有明确意图、业务唯一键有兜底——这个表设计才算真正过关。
MySQL单表约束全解析：主键、非空、唯一与默认值实战指南
先分享一个我印象特别深的场景。前几年做电商项目接口幂等逻辑测试全过结果大促当天同一笔订单被两个并发请求同时创建成功。查到最后根子不在代码而在表设计上订单号字段忘了加唯一约束。那种半夜爬起来导出数据、拿Excel逐行标红的滋味经历过的人都懂。MySQL 单表约束——主键、非空、唯一、默认——看着就是建表时多写几行代码的事实际上是你给数据入口设置的最后一道防线。这篇内容适合刚学 MySQL 的人系统过一遍约束概念也适合平时 SQL 没少写、但从来没有把约束整理成体系的开发者。我会把底层原理、建表写法、修改手法和实际踩坑一起讲透。1. 约束的根本使命把数据完整性从应用层下沉到数据层1.1 没有单表约束时我在生产环境看到的脏数据现场先说几个我亲眼见过的案例。第一件用户表没有唯一约束两个运营后台同时导入Excel同一个手机号注册了两条账号记录最后 CRM 系统里同一个客户关联了两个 ID后续所有统计全部对不上。第二件订单明细表没有非空约束前端漏传商品ID程序没做校验一条product_id NULL的记录就这么进了库下游报表计算金额时这一行被静默忽略月底对账差出几十万。第三件状态字段没有默认值开发偷懒不传status结果库里一半是 NULL一半是 0查询条件WHERE status 0永远查不全数据。这些问题的共同点在于出问题的时间点不在数据写入的那一刻而在几个月后某个统计需求找上来的时候。脏数据一旦进入生产库清洗成本极高——你要么写一堆临时脚本去猜当时业务上应该是什么要么接受报表数据永远有偏差。更麻烦的是你根本不知道到底有多少行数据受影响。随着数据量增长这种不确定性只会越来越大。所以单表约束首要价值不是让建表好看而是阻止脏数据进入存储层。1.2 为什么约束比应用层校验更可靠很多人会说我在应用接口里做校验不就行了字段必填我在后端判断一下唯一性我先查一次库再插入。道理没错但应用层校验有个天然缺陷它只覆盖你写的那条业务链路。现实情况是一张表通常有多个写入入口主站接口、管理后台、定时任务、数据同步脚本、运营临时跑的一条 UPDATE、DBA 手工修复数据的 SQL甚至同事用 Navicat 打开表直接改了几行。你不可能在每个入口都保证校验逻辑一致。等哪天某个脚本绕过业务逻辑直连数据库灌数据应用层校验就是一张废纸。数据库约束则不同它由存储引擎强制执行。只要数据要落进这张表不管是哪个客户端、哪条链路、哪个人手工敲 SQL都必须遵守规则。这就好比小区门口的交规不依赖每个司机自觉而是有摄像头和交警在管。把完整性规则下沉到数据库层才是真正对所有写入者一视同仁。1.3 单表约束全景四种约束各管一段在深入细节之前先用一张表把 MySQL 单表约束的全貌立起来约束类型关键字核心作用底层实现主键约束PRIMARY KEY唯一标识每一行不允许重复和 NULL聚簇索引非空约束NOT NULL字段不允许为 NULL存储引擎强制检查唯一约束UNIQUE字段值不允许重复但允许多个 NULL唯一二级索引默认值约束DEFAULT未显式赋值时自动填充指定值插入时自动补全补充一句MySQL 8.0.16 及以后才真正支持 CHECK 约束在此之前你写的 CHECK 约束语法能通过但引擎会直接忽略并没有实际约束力。这个点很多人不知道我会在后面的实战经验部分专门展开。现阶段先把主键、非空、唯一、默认这四类核心约束吃透已经能解决 90% 的表设计问题。2. 主键约束每张表都该有一张身份证2.1 三种主键定义写法以及各自适合的场景主键的定义有两种位置列级定义也就是直接跟在字段后面表级定义写在字段列表最后。先看列级的写法CREATE TABLE student ( id INT PRIMARY KEY, name VARCHAR(32) NOT NULL, class_id INT NOT NULL );这种写法适合单个字段做主键、且主键逻辑简单清晰的情况。如果你需要两个字段联合起来才能唯一标识一行就得用表级定义CREATE TABLE order_item ( order_id BIGINT NOT NULL, item_no INT NOT NULL, product_id BIGINT NOT NULL, quantity INT NOT NULL, PRIMARY KEY (order_id, item_no) );这是典型的复合主键场景。同一个订单下可能有多个商品条目单独拿order_id当主键会撞车单独拿item_no也不行两个字段合起来才唯一。复合主键在建表时只能用表级定义因为单个字段根本没有组合的概念。还有第三种写法把主键约束和索引一起显式命名方便后续对约束的管理CREATE TABLE student ( id INT NOT NULL, name VARCHAR(32) NOT NULL, CONSTRAINT pk_student_id PRIMARY KEY (id) );用CONSTRAINT pk_student_id给主键起名这样你将来查看约束、删除约束时就有明确的标识。但要记住MySQL 里主键约束名通常固定为 PRIMARY直接写PRIMARY KEY也不会影响使用显式命名更多是为了格式统一。2.2 自增主键为什么是默认选择一个关于索引结构的理由主键字段最常用的搭配是AUTO_INCREMENT也就是自增整数。这样写有存储层面的深层原因InnoDB 表的数据本身是按照主键聚簇存放的自增主键在插入时永远追加在索引树的最后不需要频繁移动已有数据写入性能最稳定。而很多新手喜欢用业务单号或者 UUID 做主键这个出发点可以理解——业务字段将来要按照它查询。但随机字符串做主键会带来两个实际问题一是插入位置随机导致页分裂和索引碎片化写入性能随表变大明显下降二是 UUID 是 16 字节比 INT 的 4 字节和 BIGINT 的 8 字节大得多聚簇索引的所有二级索引里都会冗余这份主键值内存和磁盘占用都吃亏。我的习惯是每个业务表都配一个与业务无关的自增或雪花 ID 做物理主键至于订单号、手机号这些业务上要唯一的字段单独用唯一约束去管。主键管怎么存储唯一约束管业务上不能重复两者职责分开表设计会清爽很多。2.3 删除或失效主键后InnoDB 会怎么处理MySQL 本身没有主键失效这个操作更常见的场景是你要删除主键或者因为重建表结构导致主键定义被改动。很多人不知道删除主键之后 InnoDB 内部的连锁反应。如果你在建表时既没有主键也没有任何非空唯一索引InnoDB 会自动生成一个不可见的 6 字节 ROWID 作为聚簇索引依据。如果一张表原本有主键你执行删除主键之后InnoDB 会去找表里第一个非空唯一索引充当新的聚簇索引如果找不到就会退回隐藏 ROWID 模式。这里有个容易忽略的代价删除主键意味着聚簇索引重建InnoDB 需要把整张表的数据重新组织一遍。表越大这个操作耗时越长期间对表的读写都会受影响。所以别把删除主键当成一条简单 SQL这些操作我都会在第五部分讲 ALTER TABLE 时一起展开。另外还要提醒一点不要指望用业务字段同时当主键又当业务唯一键来省事。主键设计要服务于数据存储的稳定性业务唯一性用 UNIQUE 约束表达两者的语义完全不同混在一起只会让后续改动投鼠忌器。2.4 主键与唯一索引的一个关键区别主键和唯一索引在值不能重复这一点上很像所以时不时有人混淆。它们的区别集中在三点第一一张表只能有一个主键但可以有多个唯一索引第二主键字段不允许为 NULL唯一索引在 MySQL 里允许有多个 NULL第三主键是聚簇索引直接决定数据物理存储顺序唯一索引只是普通二级索引。判断标准很简单如果这个字段既不能为空、也不能重复、而且每张表只能有一份那就做主键如果字段只是业务上不能重复但允许还没填的空状态优先用唯一约束。后面讲唯一约束的 NULL 行为时这一点会更清晰。3. 非空与默认值两个互补的守门员3.1 NULL 和空字符串不是一回事这是统计口径错乱的根源很多初学者以为 NULL 和差不多其实这是两类完全不同的值。NULL 表示这个字段没有值它不参与任何常规的比较运算空字符串是一个长度为 0 的字符串类型的真实值。你可以用WHERE phone 查询空字符串但查 NULL 必须用WHERE phone IS NULL。这个差异在生产环境会直接导致统计结果错乱。比如你统计缺失手机号的用户数用SELECT COUNT(*) FROM user WHERE phone IS NULL查出来是 500 人用SELECT COUNT(*) FROM user WHERE phone 查出来又是 300 人两边相加才等于真正缺失的数量。更麻烦的是COUNT(phone)这类聚合函数会自动忽略 NULL如果业务上没手机号存的是 NULL计算的时候这一行就会人间蒸发。还有一个容易被忽略的坑两个字段做字符串拼接时只要其中一个为 NULL整个拼接结果就是 NULL。比如CONCAT(first_name, last_name)只要 last_name 是 NULL哪怕 first_name 有值输出也是 NULL。在实际业务中姓和名这种字段一旦允许 NULL就会出现大量显示为空的记录。所以我的建议是业务上明确必须要有值的字段直接加NOT NULL确实允许没有值的字段也要想清楚到底是允许 NULL 还是允许空字符串二选一制定统一标准。大多数面向报表的业务表里把字符串字段设计成NOT NULL DEFAULT 比允许 NULL 更好用因为查询、聚合、拼接行为都可预期。3.2 DEFAULT 的常规姿势时间戳、状态值和开关字段默认值约束和主键不一样主键解决这行是谁默认值解决没传时我该填什么。最常见的场景有三个。第一个是时间字段。创建时间和更新时间几乎是每张业务表的标配create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间ON UPDATE CURRENT_TIMESTAMP的意思是只要这行数据发生 UPDATE该字段自动刷新为当前时间不用应用层手动维护。这个写法在 MySQL 5.6.5 以后对 DATETIME 有效之前版本只有 TIMESTAMP 能用如果你还在维护老库要注意。第二个是状态字段。状态码、删除标记、审核标识这些字段应当给出明确的默认值status TINYINT NOT NULL DEFAULT 0 COMMENT 0正常 1禁用, is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT 0未删除 1已删除这样做的好处是应用层漏传这个字段时数据不会变成 NULL而是落一个确定的初始值。业务上可以放心用WHERE status 0查询不用担心 NULL 漏数据。第三个是展示类字段比如昵称。用户没设置昵称你可以给DEFAULT 匿名用户而不是让字段为 NULL 或者空字符串前端展示时就不用写一堆判空逻辑。本质上默认值是在应用层逻辑缺失时由数据库帮你兜一个合理的底。3.3 一条建表 SQL把主键、非空、默认值串起来光讲概念容易飘直接看一条我认为典型且完整的用户表建表语句CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, email VARCHAR(64) NOT NULL COMMENT 登录邮箱, phone VARCHAR(20) NOT NULL DEFAULT COMMENT 手机号, nickname VARCHAR(32) NOT NULL DEFAULT 匿名用户 COMMENT 昵称, status TINYINT NOT NULL DEFAULT 0 COMMENT 0正常 1禁用, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;这里每个字段都刻意加了NOT NULL并且能设默认值的都设了默认值。email是登录账号必须非空同时加了唯一约束防止重复注册phone允许还没绑定但存空字符串而不是 NULL查询统一nickname没设置时给一个可见的默认昵称状态和时间字段全部由数据库填充默认值。这样一张表应用层即使有字段漏传数据进库后也一定是完整、可预期的而不是一堆 NULL。3.4 sql_mode 对非空约束的影响为什么有时候没传也不报错这里必须聊一个让很多人困惑的点同样是插入数据时字段缺失有些环境报错有些环境却能成功。关键在sql_mode。MySQL 的严格模式由STRICT_TRANS_TABLES控制。开启严格模式后向一个NOT NULL且没有默认值的字段插入 NULL 或缺失值会直接报错关闭严格模式时MySQL 会静默地把该字段替换成隐式默认值——数值类型填 0字符串类型填空字符串然后只给一个警告。很多开发在本地环境没开严格模式测试时数据照样能插进去上线后突然发现插入报错就是这个原因。你可以在会话里用SELECT sql_mode;查看当前设置。我强烈建议生产环境开启严格模式否则非空约束形同虚设数据完整性依然无从谈起。这也是很多人以为我加了 NOT NULL 但好像没生效的真正幕后黑手。4. 唯一约束防重复的最后防线4.1 业务唯一键的典型场景与三种创建方式主键解决每行必须不同但业务上有些字段也有不能重复的需求它们不是主键却必须唯一典型如用户邮箱、手机号、订单号、身份证号、优惠券码等。这些场景都要用唯一约束。创建方式有三种。第一种列级定义适合单字段唯一CREATE TABLE user ( email VARCHAR(64) NOT NULL UNIQUE );第二种表级定义并起名适合单字段唯一且需要显式管理约束名CREATE TABLE user ( email VARCHAR(64) NOT NULL, UNIQUE KEY uk_email (email) );第三种复合唯一约束适合多字段组合才唯一的情况。比如同一个用户不能对同一件商品重复评价CREATE TABLE review ( user_id BIGINT NOT NULL, product_id BIGINT NOT NULL, content VARCHAR(500) NOT NULL, UNIQUE KEY uk_user_product (user_id, product_id) );复合唯一约束里字段顺序有讲究(user_id, product_id)和(product_id, user_id)创建的索引结构不同后续查询如果常用WHERE user_id ?前者更合适因为能直接命中索引左前缀。4.2 MySQL 唯一约束的 NULL 特性可以留白但不能撞脸唯一约束最反直觉的地方在于它对 NULL 的处理。在 MySQL 中唯一约束允许多个 NULL 同时存在。也就是说字段email VARCHAR(64) UNIQUE表中可以插入很多行email NULL因为这些 NULL 互不冲突。这个行为在别的数据库里并不统一有的只允许一个 NULL所以在不同数据库间迁移时容易踩雷。但在 MySQL 里你只要记住唯一约束保证非 NULL 值不重复NULL 之间随意。如果你希望连空值也只能出现一次那就把字段加上NOT NULL从根源上禁止 NULL 出现。还有一个更隐蔽的坑空字符串是真实值不是 NULL。所以一个唯一字段你用DEFAULT 来代表未填写那表中就只能有一条第二条插入时就会报重复。设计可空但需可重复的字段时要么允许 NULL要么干脆不建唯一约束。4.3 给已有重复数据的表加唯一约束完整处理流程这是热搜词里mysql设置唯一已经有重复数据库对应的典型场景。直接执行ALTER TABLE user ADD UNIQUE KEY uk_email(email);大概率会报错Duplicate entry。原因是表里已经存在重复值。完整处理流程分三步。第一步找出重复数据。用分组聚合定位SELECT email, COUNT(*) AS cnt FROM user GROUP BY email HAVING cnt 1;第二步处理重复数据。具体怎么处理取决于业务可以给重复记录里保留一行更新其他行的 email 为新的唯一值也可以把重复记录合并甚至可以删除无效记录。这一步涉及数据修复操作前一定先备份原表。我自己习惯先建一张备份表比如CREATE TABLE user_bak_20250101 AS SELECT * FROM user;再把重复数据处理干净。第三步确认没有重复后再添加唯一约束ALTER TABLE user ADD UNIQUE KEY uk_email (email);添加成功后再把备份表删掉。整套流程最怕中间跳过第一步直接加约束错误信息只能告诉你有重复不会告诉你是哪几行等数据量大了之后再回头定位重复项代价会非常大。4.4 唯一约束在并发写入中的兜底作用并发条件下唯一约束的价值会被放大。典型的场景是发券两个请求同时过来应用层都先查了这张券是否已领取都发现没有记录然后同时执行 INSERT。如果券码字段没有唯一约束这两条都会成功等于同一张券被发了两次。应用层的先查后插存在天然的竞态窗口任何并发控制都做不到百分百可靠但数据库层面唯一约束的校验是原子性的两个并发 INSERT 中必然有一个失败。所以我在设计接口时会这样看待唯一约束它不是让你不写业务校验的借口而是业务校验之外的兜底防线。业务代码的正常流程该查就查、该报友好错就报但数据库这层必须保证无论如何都重复不了。这也是很多高并发系统里幂等控制最终落到一张带唯一约束的幂等表上的原因——约束不认网络延迟不认进程调度不认分布式时钟。5. 建表之后的反悔药用 ALTER TABLE 修改与删除约束5.1 增删主键先解除自增再动手给一张表加主键很简单ALTER TABLE student ADD PRIMARY KEY (id);但要保证id列既没有 NULL 也没有重复值否则执行会失败。删除主键时有一个关键前置条件如果主键列是自增的必须先把自增属性去掉否则会报错。正确步骤是先改列定义再删主键ALTER TABLE student MODIFY id INT NOT NULL; ALTER TABLE student DROP PRIMARY KEY;第一步去掉AUTO_INCREMENT第二步才能真正删掉主键。我还想提醒一句删除主键意味着聚簇索引重建InnoDB 内部会对整张表重新组织。生产环境的大表千万不要直接执行最好在维护窗口操作并且在测试环境先跑一遍预估耗时。5.2 增删非空与默认值最容易把列定义写丢的操作增加和删除非空约束标准的姿势是用 MODIFY 重定义整列ALTER TABLE user MODIFY email VARCHAR(64) NOT NULL; ALTER TABLE user MODIFY email VARCHAR(64) NULL;这里最坑的地方在于MODIFY 是整列覆盖定义你写的字段类型长度必须和原来完全一致不然会被顺手改掉。我见过不止一次有人想给 email 加非空写成了ALTER TABLE user MODIFY email VARCHAR(32) NOT NULL;结果原先的 VARCHAR(64) 被缩成了 VARCHAR(32)存量数据超过 32 字符的长邮箱直接截断或报错。正确的做法是先把完整列定义查出来在原有基础上加约束ALTER TABLE user MODIFY email VARCHAR(64) NOT NULL COMMENT 登录邮箱;默认值的增删语法略有不同不需要重写整列ALTER TABLE user ALTER COLUMN status SET DEFAULT 0; ALTER TABLE user ALTER COLUMN status DROP DEFAULT;给已有数据的表添加非空约束之前记得先检查列里有没有 NULL。有 NULL 的时候直接加会失败需要先补全或清洗数据UPDATE user SET phone WHERE phone IS NULL;5.3 增删唯一约束删除时记住它本质是索引添加唯一约束很容易ALTER TABLE user ADD UNIQUE KEY uk_email (email);删除时有个需要记住的点唯一约束在 InnoDB 里会创建一个唯一二级索引所以你删除它时用的不是DROP CONSTRAINT而是DROP INDEXALTER TABLE user DROP INDEX uk_email;如果当初建约束时没起名MySQL 默认会用字段名作为索引名比如删email上的唯一约束时可能用ALTER TABLE user DROP INDEX email;。不确定名字时先执行SHOW INDEX FROM user;看清楚索引名再动手。这里顺便提一句唯一索引和普通索引都靠 DROP INDEX 删除千万不要因为它是约束就去搜 DROP CONSTRAINTMySQL 会报语法错误。5.4 查看表结构与约束信息的三个命令遇到任何约束到底加没加索引名是什么的疑问最有效的排查方式是看当前的建表语句SHOW CREATE TABLE user;一条命令能看到所有约束主键、非空、默认值、唯一索引全部列出来比翻当初的建表脚本可靠得多因为所有 ALTER 修改都已经反映在里面。想看索引详细信息SHOW INDEX FROM user;输出里Non_unique列等于 0 的就是唯一索引等于 1 的是普通索引Key_name是索引名。如果想通过系统表做自动化检查可以查信息模式SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME user;这三个命令配合使用基本能应对日常所有约束排查需求。我每次做表结构评审时第一件事就是跑一遍SHOW CREATE TABLE把全表约束状态摸清楚再下结论。6. 生产环境使用约束的几条实战经验6.1 约束命名规范可读性决定了维护效率约束名不是为了好看而是为了三个月后你自己还能一眼看懂。我推荐一套简单的命名规范主键直接用 PRIMARY KEY唯一约束统一叫uk_表名_字段名普通索引叫idx_表名_字段名。比如用户表的 email 唯一约束就是uk_user_email订单明细表的复合唯一约束就是uk_order_item_order_id_item_no。这套规范最大的好处是任何人看到uk_前缀就知道这是一个唯一约束看到字段名就知道它在约束什么。尤其在执行DROP INDEX时名字含义清晰能避免误删。如果每个人建约束时都随手起一个毫无规律的名字等你需要维护几十张表时查索引名都会查到崩溃。6.2 大表加约束之前想清楚窗口期和工具方案在千万行级别的大表上执行 ALTER TABLE不管加主键、加唯一约束还是加非空约束都可能引发长时间的表重建或索引重建期间会产生锁竞争、IO 飙升、主从延迟。我的经验是三个原则第一操作前在测试环境用相同数据量级跑一遍拿到真实耗时第二选择业务低峰期执行并且提前准备好回滚方案第三如果表特别大且不能接受长时间锁表考虑使用在线变更工具来做而不是直接拼一条裸的 ALTER 语句。另外加唯一约束时如果目标字段在历史数据上已经存在大量重复前面第四部分说过直接跑 ALTER 会报错。处理重复数据本身可能涉及 UPDATE 大量行这个操作同样会影响线上。所以更好的顺序是先写好数据清理脚本并验证再执行加约束的 DDL把数据修复和 DDL 变更拆成两个独立窗口避免互相影响。6.3 小心 MySQL 8.0.16 之前看起来有 CHECK 约束的假象我在前面提过一句这里展开讲。MySQL 8.0.16 之前的版本CHECK 约束会被 MySQL 解析器接受语法检查通过但存储引擎完全忽略它。也就是说你写age INT CHECK (age 0)它能建表成功但插入age -1照样成功。无数人栽在这个假约束上以为加了约束实际没任何效果。MySQL 8.0.16 之后CHECK 约束才开始被真正强制执行。所以如果你还在用 8.0.16 之前的版本不要依赖 CHECK 做任何完整性校验业务规则请放到应用层升级到 8.0.16 以后CHECK 可以用来做简单的取值范围约束比如状态字段的枚举值校验。但也要注意不要滥用过于复杂的 CHECK 表达式会影响写入性能。6.4 我每次做表结构评审时最后必查的三件事这些年经手了不少表结构评审我发现只要抓住三个核心问题单表约束这一层基本不会出大乱子。第一每张业务表是不是都有主键。没有主键的表在 InnoDB 里只能用隐藏 ROWID 组织数据不仅无法高效定位行而且后续做数据变更、日志解析、分库分表都会遇到麻烦。日志表、临时表可以例外但业务表一定要有主键。第二业务上需要唯一的字段是不是真的加了唯一约束。电商的订单号、用户的手机号、支付的流水号这些不能重复的字段光靠代码保证不够必须落到数据库层。这也是我今天强调最多的一点。第三空值的语义是不是清晰。每个字段到底允许 NULL、还是用空字符串、还是给了默认值必须有一个统一设计。最怕的是同一个含义的字段有些表用 NULL有些表用有些表用 0将来做统计时口径混乱查出来的数据自己都不敢信。把这三件事想清楚你建出来的表至少不会因为约束缺失而在深夜被人叫起来修数据。最后再分享一个我实际操作中的小习惯每次新建一张业务表之前我都会把SHOW CREATE TABLE的输出逻辑在脑子里过一遍假装自己是一个三个月后接手这张表的同事看这个表结构能不能一眼读懂。约束名称清晰、每个字段非空和默认值有明确意图、业务唯一键有兜底——这个表设计才算真正过关。
