接手过几个维护期项目之后你会慢慢发现一个规律线上数据变脏往往不是业务代码的bug而是约束没设计好。重复的手机号能注册、子订单引用了不存在的父订单、员工表里age字段存了-3……这些问题的根子大多在建模阶段就没想清楚约束该怎么加。约束这东西建表的时候顺手写上几行似乎没什么技术含量可等到数据出问题那天你才会意识到它才是数据库最便宜、最可靠的那道防线。这篇内容没什么高深理论就是把MySQL里五类约束——非空、唯一、主键、默认值、外键——掰开揉碎讲清楚。每个约束解决什么业务问题、建表时怎么写、线上怎么改、有哪些坑全部覆盖。适合正在学数据库的初学者也适合准备重构老表结构、想补上约束缺口的中级开发者。1. 约束的本质数据质量的声明式防线先说一个容易忽略的事实约束其实是数据库对业务规则的一种声明式表达。你在建表语句里写下一行约束相当于告诉数据库引擎凡是违反这条规则的数据一律不许进来。 这比在应用层写一百个if判断都可靠——因为不管数据是从哪个接口进来的、是哪个同事用脚本灌的、还是DBA手工刷的只要走到这张表就必须过约束这道闸。约束的哲学在于防而不是纠。应用层校验是软性的总有漏网之鱼而且漏掉的时候你往往不知道约束是硬性的违反就报错从源头保证你查出来的数据永远是你期望的形状。我见过太多团队对约束不上心觉得反正Java里都校验过了结果换了个临时脚本批量刷数据直接把脏数据灌进生产库。这种教训一次就足够让人长记性。具体来说MySQL的约束体系分五条主线NOT NULL非空约束声明这一列必须有值。UNIQUE唯一约束声明这一列或列组合不可重复。PRIMARY KEY主键约束非空唯一的合体同时承担定位一行数据的职责。DEFAULT默认值约束不填时给一个兜底值。FOREIGN KEY外键约束声明这一列的值必须存在于另一张表的某列中。这五类约束不是孤立存在的它们经常组合出现。举个例子用户表的手机号字段CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, phone VARCHAR(20) NOT NULL UNIQUE, nickname VARCHAR(50) NOT NULL, status TINYINT NOT NULL DEFAULT 1 );这一条建表语句里NOT NULL UNIQUE就是两个约束的组合手机号既不能为空也不能重复。DEFAULT 1保证了只要业务方不显示传status进来就是正常状态。这就是约束组合拳的日常用法。有些约束是列级约束写在字段定义后面比如NOT NULL和DEFAULT有些是表级约束写在字段列表之后比如把多列组合成一个唯一键。搞清楚这个区别等会儿写复杂约束的时候就不会晕。提示MySQL 8.0及以上版本建表时若定义了约束但没起名字系统会自动生成约束名。唯一约束默认叫字段名_2外键默认叫表名_ibfk_1这种格式。线上环境建议显式命名不然排错的时候光找约束名都够你喝一壶。2. 五种约束逐个拆解语法、适用场景与暗坑这节我们从业务视角过一遍每种约束。直接给结论再解释为什么这么用。2.1 非空约束比你想的更底层非空约束在MySQL里不是一个独立的约束对象它就是列定义的一部分。语法极简CREATE TABLE t_example ( name VARCHAR(50) NOT NULL, email VARCHAR(100) NULL );NULL和NOT NULL的区别用一句话概括NULL表示不知道或者没有它参与任何计算都会把结果染成NULL。比如SELECT score 10 FROM student如果score是NULL结果是NULL而不是10。这就是为什么很多字段必须声明NOT NULL——不是为了好看是为了避免业务代码里到处躲NULL指针。暗坑之一空字符串和NULL是两回事。是一个具体值NULL是没有值。很多初学者以为WHERE name 张三能把name为NULL的行也查出来其实查不出来。NULL不参与任何比较运算只能用IS NULL去查。所以如果你的业务里没有填写和填了空串语义不同一定要想清楚是否加非空约束。2.2 唯一约束不是你想的只是不重复唯一约束的逻辑声明是这一列或这几列的组合在整个表中不允许重复值。有意思的是MySQL实现唯一约束的方式是自动创建一个唯一索引。你建一个唯一约束等于同时建了一个索引。所以唯一约束不仅能防重复还能加速查询。CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, phone VARCHAR(20), UNIQUE KEY uk_phone (phone) );这里我给了约束一个显式名字uk_phone后面删除或修改约束时能直接找到它。需要注意两个细节。第一唯一约束允许NULL重复。在MySQL里NULL被视为未知未知和未知不相等所以你可以插很多条phone NULL的记录而不触发唯一约束。如果你的业务要求手机号要么不填填了就绝不能重复或者有手机号的行最多一条那光靠UNIQUE是不够的还得配合其他逻辑。这是个经常被忽略的边界。第二删除唯一约束的语法不是DROP CONSTRAINT而是DROP INDEX因为它的本质是索引。到了MySQL 8.0虽然支持了DROP CONSTRAINT写法但底层逻辑依然是索引操作。多列组合唯一也很常见典型场景是同一个用户对同一个商品只能有一条评价记录CREATE TABLE product_comment ( user_id INT NOT NULL, product_id INT NOT NULL, content TEXT, UNIQUE KEY uk_user_product (user_id, product_id) );注意多列唯一判断的是组合值重复而不是单列。用户1评论商品A、用户1评论商品B两条都能插入用户1再评商品A才触发冲突。2.3 主键约束非空唯一的合体但更重要主键和唯一约束最大的区别主键是行的身份标识它直接和存储引擎的物理组织方式挂钩。InnoDB的表就是一棵按主键排序的B树数据行直接挂在主键上。所以选主键是个策略性问题。日常建表常见写法CREATE TABLE t_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no) );这里有个非常容易踩的坑业务唯一键order_no和主键id是两套东西。我见过不少项目设计表的人图省事直接用订单号当主键。当时看没什么问题等到业务发展、订单表数据量上千万后订单号的随机字符串导致B树索引频繁页分裂插入性能肉眼可见地下降。主键尽量用自增整数或有序雪花ID保证插入顺序近似递增。另外主键约束的索引名永远是PRIMARY所以它和唯一约束在删除语法上不一样——主键本身不存在删除约束名这种操作而是直接DROP PRIMARY KEY。InnoDB还有一个隐藏规则如果你建表时不主动定义主键InnoDB会优先找一个非空的唯一索引当隐藏主键都找不到就生成一个6字节的隐藏列。这会导致额外的存储开销和索引效率下降。所以任何InnoDB表都应该显式定义主键这不只是规范问题。2.4 默认值约束减少业务职责的兜底机制默认值约束比较容易理解插入时不传这一列就自动填默认值。但用起来有三个细节值得展开。第一默认值是在INSERT语句没有显式指定该列时生效。如果你显式写明传NULL那就是NULL除非列定义了NOT NULL或DEFAULT有特殊处理逻辑。比如CREATE TABLE t_user ( status TINYINT NOT NULL DEFAULT 1 ); -- 不传status取1 INSERT INTO t_user (status) VALUES (DEFAULT); -- 显式传2取2 INSERT INTO t_user (status) VALUES (2);第二MySQL 8.0对DEFAULT表达式做了一次语法调整。8.0.13之前函数的默认值必须加括号比如DEFAULT (NOW())8.0.13之后建议直接写DEFAULT CURRENT_TIMESTAMP也支持表达式默认值了。如果你还停留在5.7的习惯升级到8.0后要留意这个变化。第三默认值约束不改变列的可空性。一个列同时写DEFAULT 1但没写NOT NULL那插入时传NULL照样能插进去。默认值只在你完全不提这个字段时生效。想让字段不填就默认值、填了NULL就报错请组合使用NOT NULL DEFAULT 值。这两者的语义差异业务人员讨论需求时经常混淆。2.5 外键约束跨表完整性但很多人不敢用外键约束是把数据完整性从单表提升到跨表从表的外键列值要么是NULL要么必须存在于主表被引用列中。经典场景是订单明细表引用订单主表CREATE TABLE t_order ( id BIGINT PRIMARY KEY, order_no VARCHAR(64) NOT NULL ); CREATE TABLE t_order_item ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_id BIGINT NOT NULL, product_name VARCHAR(100) NOT NULL, CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES t_order(id) ON DELETE CASCADE ON UPDATE RESTRICT );这里有几个必须搞清楚的底层限制被引用的列必须有索引。通常引用主键就没问题如果引用非主键列那张表该列必须有唯一约束或普通索引否则MySQL会报错——外键列上如果没有索引MySQL会自动创建一个但在从表上创建是要付出DDL代价的。外键只支持InnoDB。MyISAM虽然允许写外键语句但引擎会直接忽略它。这是个极其隐蔽的坑你满怀信心地写了FOREIGN KEY以为数据完整性有保障了结果插了脏数据也没报错因为MyISAM根本不理会你。类型必须严格匹配。主表BIGINT从表INT即使数值范围能放得下MySQL也不允许建立外键。字符集的排序规则也得一致。那为什么很多团队在生产环境里禁用外键主要原因是性能和高并发下的锁问题。外键约束每次写入都要去检查主表多了一次查询而且在某些场景下会扩大锁范围影响并发。这不是外键本身错而是用错了地方。我的建议是核心交易链路、并发量高的表尽量不用外键靠应用层保证后台管理系统、配置表、数据一致性要求极强但写入量低的场景外键值得用。严格分级看待而不是一刀切。3. 约束设计的实战思路从建表开始就减少未来的坑约束不是写完就完事的它的设计直接关系到你未来两年维护这个表的幸福感。这节讲讲我在实际项目中是怎么取舍的。3.1 列属性设计的顺序先定类型再定约束很多新手建表先纠结这列该不该有默认值其实顺序反了。正确顺序是先确定这列存什么类型的数据选对类型整数、字符串、日期、布尔。再问语义这列能不能为空如果业务上没有和填了空串含义不同就按需加NOT NULL。再问规则这列允不允许重复需要的话加UNIQUE。最后问兜底如果不填给什么默认值举个例子一个后台员工表的last_login_time字段。业务上从未登录是一个合法的中间状态所以这一列允许为NULL比较合理但如果你用DATETIME DEFAULT CURRENT_TIMESTAMP那就意味着创建时默认等于创建时间这不等于从未登录语义直接冲突了。所以我倾向于这样设计last_login_time DATETIME NULL DEFAULT NULL这样查询时用IS NULL来判断从未登录逻辑既不会误伤也不会产生歧义。3.2 组合约束的几个经典案例案例A逻辑删除与唯一约束的冲突业务上最常见的需求是同一用户不能有两个未删除的手机号记录但你做的是逻辑删除is_deleted物理数据还在表里。如果直接在phone上建唯一约束删掉一条再插同号会被卡住。解决方案通常有两种把删除标记并入唯一键UNIQUE KEY uk_phone_deleted (phone, is_deleted)。但问题是同一个手机号被删两次(phone, 1)又插不进去了。更通用的方案是引入删除批次号deleted_batch VARCHAR(32) NOT NULL DEFAULT 然后唯一键设为(phone, deleted_batch)。删除时给该行生成一个UUID批次号物理上没有重复的(phone, uuid)了。这种设计常见于需要大量逻辑删除场景的业务表。案例B联合唯一是分页数据的天然保证比如一个角色权限关联表(role_id, permission_id)联合唯一既防重复又能充当查询索引。一张中间表如果连联合唯一都没有迟早会插出大量重复授权记录接口返回来满屏相同数据排查起来相当痛苦。案例C默认值与业务状态机配合状态字段用TINYINT NOT NULL DEFAULT 0配合一张枚举表说明每个数字的含义。不要在状态列上直接设NULL否则业务里空状态和无状态混在一起后统计口径会崩。3.3 到底要不要所有字段都加NOT NULL网上有种说法是禁止任何字段为NULL这个观点过于绝对。有些字段确实该允许NULL比如备注扩展信息最后登录时间。这些字段的NULL值可以被当成一种携带信息的状态。真正的区别在于NULL是否有业务语义。有语义就保留NULL没语义就一律NOT NULL DEFAULT。我见过一个比较稳妥的分法所有参与运算、统计、比较的数值字段一律NOT NULL DEFAULT 0所有状态位、标志位一律NOT NULL DEFAULT 0或NOT NULL DEFAULT 1所有时间字段按需选择但创建时间通常NOT NULL DEFAULT CURRENT_TIMESTAMP自由文本、备注、扩展字段允许NULL但查询时心里要清楚NULL的语义4. 约束的后期管理改约束比建约束容易踩坑建表时想清楚约束是最好的但现实往往是老表已经跑了好几年数据里存了一堆脏值这时候想补约束麻烦就来了。这节我讲几个改约束的场景和操作细节。4.1 给已有表添加约束给已有列添加非空约束时MySQL要求该列现有数据不能有NULL。所以老表加NOT NULL前通常先做一次数据清洗-- 步骤1把NULL值替换为兜底值 UPDATE t_user SET nickname 未填写 WHERE nickname IS NULL; -- 步骤2修改列属性 ALTER TABLE t_user MODIFY COLUMN nickname VARCHAR(50) NOT NULL DEFAULT 未填写;同样的道理给已有列加唯一约束前得先确认没有重复数据-- 查询有重复的组 SELECT phone, COUNT(*) FROM t_user GROUP BY phone HAVING COUNT(*) 1; -- 去重完成后再添加唯一约束 ALTER TABLE t_user ADD UNIQUE KEY uk_phone (phone);这一步如果数据量巨大比如千万级必须先评估耗时。ALTER TABLE在MySQL 5.7及以下版本是锁表的8.0虽然引入了ALGORITHMINPLACE优化但一些场景依然会锁。大表加约束务必在低峰期执行或者借助在线变更工具。4.2 删除与修改约束删除主键ALTER TABLE t_user DROP PRIMARY KEY;删除唯一约束本质删索引ALTER TABLE t_user DROP INDEX uk_phone;删除外键约束注意要用约束名不是列名ALTER TABLE t_order_item DROP FOREIGN KEY fk_item_order;修改约束通常没有一条语句直接改的路径一般是先删再加。比如你想把某个唯一约束从单列换成多列组合就要先删旧约束再ADD新约束。这中间有个空窗期操作要在事务或低峰期处理防止期间插入重复数据。4.3 SQL_MODE对约束的隐性影响约束的行为受sql_mode影响这点容易被忽略。在宽松模式下比如sql_mode不含STRICT_TRANS_TABLES向NOT NULL列插入NULLMySQL不报错而是把列改成隐式默认值只产生一条warning。很多研发在本地开发环境没开严格模式测试怎么插都成功上线后在生产环境被报错搞得一头雾水。开发、测试、生产三个环境的sql_mode保持一致是约束正常工作的前提。MySQL 8.0默认sql_mode是带严格模式的5.7也默认开启。但某些云数据库默认配置可能被调过。建议执行下面语句确认一下SELECT sql_mode;比较理想的配置至少包含STRICT_TRANS_TABLES和NO_ENGINE_SUBSTITUTION前者保证约束不被悄悄绕过后者防止建表时引擎被静默替换。4.4 线上改约束的经典事故场景我参与过一次事故排查印象极深某后台系统的用户表mobile列历史上有大量NULL和重复值。团队给mobile加了唯一约束之后当天晚上发现有一个定时任务开始报错——因为这个任务执行时如果手机号为空会往表里插入一条mobile NULL的记录。字段本身没有NOT NULL而唯一约束又允许NULL重复所以当时插入没失败问题出在另一个地方这个任务还会把空手机号更新成同一个兜底字符串空串是具体值唯一约束拦住了第二次更新。结果就是任务永远卡在第一次更新之后形成了一个极其隐蔽的循环失败。后来怎么解的把mobile的语义彻底分清要么为空NULL要么为用户真实手机号。加了NOT NULL DEFAULT 后再配合正则校验才把这块彻底稳住。这件事让我养成了一个习惯加约束前把现有数据里有哪些非法值完整列一遍再决定兜底策略。别让约束成为定时炸弹。5. 约束与索引的协作关系一张表的设计权衡约束和索引在MySQL里是纠缠在一起的。唯一约束创建唯一索引主键创建主键索引外键创建普通索引。理解这层关系你才能在设计表时做出合理的权衡。约束类型自动创建的索引索引类型删除约束的动作PRIMARY KEYPRIMARY聚集索引DROP PRIMARY KEYUNIQUE约束名对应的索引二级索引唯一DROP INDEX 约束名FOREIGN KEY自动生成普通索引若列上已有索引则复用二级索引DROP FOREIGN KEY 约束名NOT NULL / DEFAULT不创建索引无改列属性这张表意味着你每加一个唯一约束就同时获得了一个查询加速能力。比如用户表的phone列加了唯一约束那么按手机号登录时用的索引覆盖就有了。同理外键列自动生成的索引也能加快从表按主表ID反查的速度。但反过来索引不是免费午餐。每个索引都会增加写入的开销因为插入、更新、删除时都要同步维护索引树。所以约束加得太多太滥也会让写入变慢。设计原则是该有的约束必须有不必要的索引一个也别留。比如一张只有几十行数据的配置表加不加索引无所谓一张日增百万的流水表每个多余的索引都是实打实的写入损耗。还有个关联点唯一约束和普通索引在去重场景下的选择。如果你想防重复必须用唯一约束如果你只想加速查找用普通索引就够了。后者允许重复值但写入开销更小。搞清楚业务自己要的是什么不要为了顺手就乱加。在分库分表的大数据架构下唯一约束的效果会打折——唯一性只能在单个分片内保证跨分片就无能为力了。这种场景下唯一ID通常由全局发号器生成数据库约束已经退居二线。如果是单库单表的项目约束依然是性价比最高的防线先把数据库约束吃透再谈中间件层的高级方案。6. 一张表描述全部约束设计的完整示例最后用一个相对完整的示例把上面所有内容串起来。假设我们设计一张简单的订单表业务规则是订单号必填且唯一、下单用户必填、订单金额默认0、状态默认待支付、备注可空、关联的用户必须存在。CREATE TABLE t_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, order_no VARCHAR(32) NOT NULL COMMENT 订单号, user_id BIGINT UNSIGNED NOT NULL COMMENT 下单用户ID, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2已取消, remark VARCHAR(255) NULL COMMENT 备注, 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_order_no (order_no), KEY idx_user_id (user_id), CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES t_user(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这个设计的逻辑是这样的id自增主键保证行物理顺序插入有序避免随机主键带来的页分裂。order_no是业务单号单独做唯一约束天然防重复下单。user_id建外键关联用户表保证订单不会落到不存在的用户头上。如果并发量极高这一条可以拆掉放到应用层校验但一般的后台业务外键完全扛得住。amount用DECIMAL(10,2)而不是FLOAT避免浮点误差默认0保证统计时不出现NULL陷阱。status用TINYINT NOT NULL DEFAULT 0并注释清楚状态含义。created_at和updated_at都是数据库层维护应用层不用管。这里面其实还隐含了一个设计细节KEY idx_user_id (user_id)。虽然外键会自动建索引但如果你决定不用外键这个索引得手动建否则按用户查订单的SQL会全表扫。约束可以拆掉索引不要忘记留。7. 实际维护中的几个个人体会约束这东西建表时多花十分钟琢磨能替你在未来省下几个通宵。说几个我自己的坚持第一任何表都必须有主键尤其是InnoDB表。就算业务上找不到天然的唯一键建一个自增id成本最低、收益最高。没有主键的InnoDB表在复制、备份、数据修正时都会出各种幺蛾子。第二唯一约束的语义要落到字段组合而不是直觉。好好理清哪些列组合起来才是业务上的唯一比到处乱加约束重要得多。一个约束加错后面业务方会天天来提工单。第三改约束前先备份、先查重复、先看执行计划。一套组合拳下来线上事故率会低很多。尤其是大表加唯一约束一定要先在从库或测试环境完整跑一遍算好时间窗口再动生产。第四把约束的删除脚本留在项目的数据库变更记录里。哪天约束定义有变化你能清楚地从历史记录里看到这个字段以前是什么约束、为什么改。这比靠记忆靠谱得多。最后分享一个小技巧用SHOW CREATE TABLE查看别人建的表时先扫一遍约束定义再扫字段定义。约束规划往往比字段类型更能反映设计者对业务的思考深度。你读懂了约束就读懂了半张表。 MySQL五类约束全解析：从建表到防脏数据的核心防线
接手过几个维护期项目之后你会慢慢发现一个规律线上数据变脏往往不是业务代码的bug而是约束没设计好。重复的手机号能注册、子订单引用了不存在的父订单、员工表里age字段存了-3……这些问题的根子大多在建模阶段就没想清楚约束该怎么加。约束这东西建表的时候顺手写上几行似乎没什么技术含量可等到数据出问题那天你才会意识到它才是数据库最便宜、最可靠的那道防线。这篇内容没什么高深理论就是把MySQL里五类约束——非空、唯一、主键、默认值、外键——掰开揉碎讲清楚。每个约束解决什么业务问题、建表时怎么写、线上怎么改、有哪些坑全部覆盖。适合正在学数据库的初学者也适合准备重构老表结构、想补上约束缺口的中级开发者。1. 约束的本质数据质量的声明式防线先说一个容易忽略的事实约束其实是数据库对业务规则的一种声明式表达。你在建表语句里写下一行约束相当于告诉数据库引擎凡是违反这条规则的数据一律不许进来。 这比在应用层写一百个if判断都可靠——因为不管数据是从哪个接口进来的、是哪个同事用脚本灌的、还是DBA手工刷的只要走到这张表就必须过约束这道闸。约束的哲学在于防而不是纠。应用层校验是软性的总有漏网之鱼而且漏掉的时候你往往不知道约束是硬性的违反就报错从源头保证你查出来的数据永远是你期望的形状。我见过太多团队对约束不上心觉得反正Java里都校验过了结果换了个临时脚本批量刷数据直接把脏数据灌进生产库。这种教训一次就足够让人长记性。具体来说MySQL的约束体系分五条主线NOT NULL非空约束声明这一列必须有值。UNIQUE唯一约束声明这一列或列组合不可重复。PRIMARY KEY主键约束非空唯一的合体同时承担定位一行数据的职责。DEFAULT默认值约束不填时给一个兜底值。FOREIGN KEY外键约束声明这一列的值必须存在于另一张表的某列中。这五类约束不是孤立存在的它们经常组合出现。举个例子用户表的手机号字段CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, phone VARCHAR(20) NOT NULL UNIQUE, nickname VARCHAR(50) NOT NULL, status TINYINT NOT NULL DEFAULT 1 );这一条建表语句里NOT NULL UNIQUE就是两个约束的组合手机号既不能为空也不能重复。DEFAULT 1保证了只要业务方不显示传status进来就是正常状态。这就是约束组合拳的日常用法。有些约束是列级约束写在字段定义后面比如NOT NULL和DEFAULT有些是表级约束写在字段列表之后比如把多列组合成一个唯一键。搞清楚这个区别等会儿写复杂约束的时候就不会晕。提示MySQL 8.0及以上版本建表时若定义了约束但没起名字系统会自动生成约束名。唯一约束默认叫字段名_2外键默认叫表名_ibfk_1这种格式。线上环境建议显式命名不然排错的时候光找约束名都够你喝一壶。2. 五种约束逐个拆解语法、适用场景与暗坑这节我们从业务视角过一遍每种约束。直接给结论再解释为什么这么用。2.1 非空约束比你想的更底层非空约束在MySQL里不是一个独立的约束对象它就是列定义的一部分。语法极简CREATE TABLE t_example ( name VARCHAR(50) NOT NULL, email VARCHAR(100) NULL );NULL和NOT NULL的区别用一句话概括NULL表示不知道或者没有它参与任何计算都会把结果染成NULL。比如SELECT score 10 FROM student如果score是NULL结果是NULL而不是10。这就是为什么很多字段必须声明NOT NULL——不是为了好看是为了避免业务代码里到处躲NULL指针。暗坑之一空字符串和NULL是两回事。是一个具体值NULL是没有值。很多初学者以为WHERE name 张三能把name为NULL的行也查出来其实查不出来。NULL不参与任何比较运算只能用IS NULL去查。所以如果你的业务里没有填写和填了空串语义不同一定要想清楚是否加非空约束。2.2 唯一约束不是你想的只是不重复唯一约束的逻辑声明是这一列或这几列的组合在整个表中不允许重复值。有意思的是MySQL实现唯一约束的方式是自动创建一个唯一索引。你建一个唯一约束等于同时建了一个索引。所以唯一约束不仅能防重复还能加速查询。CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, phone VARCHAR(20), UNIQUE KEY uk_phone (phone) );这里我给了约束一个显式名字uk_phone后面删除或修改约束时能直接找到它。需要注意两个细节。第一唯一约束允许NULL重复。在MySQL里NULL被视为未知未知和未知不相等所以你可以插很多条phone NULL的记录而不触发唯一约束。如果你的业务要求手机号要么不填填了就绝不能重复或者有手机号的行最多一条那光靠UNIQUE是不够的还得配合其他逻辑。这是个经常被忽略的边界。第二删除唯一约束的语法不是DROP CONSTRAINT而是DROP INDEX因为它的本质是索引。到了MySQL 8.0虽然支持了DROP CONSTRAINT写法但底层逻辑依然是索引操作。多列组合唯一也很常见典型场景是同一个用户对同一个商品只能有一条评价记录CREATE TABLE product_comment ( user_id INT NOT NULL, product_id INT NOT NULL, content TEXT, UNIQUE KEY uk_user_product (user_id, product_id) );注意多列唯一判断的是组合值重复而不是单列。用户1评论商品A、用户1评论商品B两条都能插入用户1再评商品A才触发冲突。2.3 主键约束非空唯一的合体但更重要主键和唯一约束最大的区别主键是行的身份标识它直接和存储引擎的物理组织方式挂钩。InnoDB的表就是一棵按主键排序的B树数据行直接挂在主键上。所以选主键是个策略性问题。日常建表常见写法CREATE TABLE t_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no) );这里有个非常容易踩的坑业务唯一键order_no和主键id是两套东西。我见过不少项目设计表的人图省事直接用订单号当主键。当时看没什么问题等到业务发展、订单表数据量上千万后订单号的随机字符串导致B树索引频繁页分裂插入性能肉眼可见地下降。主键尽量用自增整数或有序雪花ID保证插入顺序近似递增。另外主键约束的索引名永远是PRIMARY所以它和唯一约束在删除语法上不一样——主键本身不存在删除约束名这种操作而是直接DROP PRIMARY KEY。InnoDB还有一个隐藏规则如果你建表时不主动定义主键InnoDB会优先找一个非空的唯一索引当隐藏主键都找不到就生成一个6字节的隐藏列。这会导致额外的存储开销和索引效率下降。所以任何InnoDB表都应该显式定义主键这不只是规范问题。2.4 默认值约束减少业务职责的兜底机制默认值约束比较容易理解插入时不传这一列就自动填默认值。但用起来有三个细节值得展开。第一默认值是在INSERT语句没有显式指定该列时生效。如果你显式写明传NULL那就是NULL除非列定义了NOT NULL或DEFAULT有特殊处理逻辑。比如CREATE TABLE t_user ( status TINYINT NOT NULL DEFAULT 1 ); -- 不传status取1 INSERT INTO t_user (status) VALUES (DEFAULT); -- 显式传2取2 INSERT INTO t_user (status) VALUES (2);第二MySQL 8.0对DEFAULT表达式做了一次语法调整。8.0.13之前函数的默认值必须加括号比如DEFAULT (NOW())8.0.13之后建议直接写DEFAULT CURRENT_TIMESTAMP也支持表达式默认值了。如果你还停留在5.7的习惯升级到8.0后要留意这个变化。第三默认值约束不改变列的可空性。一个列同时写DEFAULT 1但没写NOT NULL那插入时传NULL照样能插进去。默认值只在你完全不提这个字段时生效。想让字段不填就默认值、填了NULL就报错请组合使用NOT NULL DEFAULT 值。这两者的语义差异业务人员讨论需求时经常混淆。2.5 外键约束跨表完整性但很多人不敢用外键约束是把数据完整性从单表提升到跨表从表的外键列值要么是NULL要么必须存在于主表被引用列中。经典场景是订单明细表引用订单主表CREATE TABLE t_order ( id BIGINT PRIMARY KEY, order_no VARCHAR(64) NOT NULL ); CREATE TABLE t_order_item ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_id BIGINT NOT NULL, product_name VARCHAR(100) NOT NULL, CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES t_order(id) ON DELETE CASCADE ON UPDATE RESTRICT );这里有几个必须搞清楚的底层限制被引用的列必须有索引。通常引用主键就没问题如果引用非主键列那张表该列必须有唯一约束或普通索引否则MySQL会报错——外键列上如果没有索引MySQL会自动创建一个但在从表上创建是要付出DDL代价的。外键只支持InnoDB。MyISAM虽然允许写外键语句但引擎会直接忽略它。这是个极其隐蔽的坑你满怀信心地写了FOREIGN KEY以为数据完整性有保障了结果插了脏数据也没报错因为MyISAM根本不理会你。类型必须严格匹配。主表BIGINT从表INT即使数值范围能放得下MySQL也不允许建立外键。字符集的排序规则也得一致。那为什么很多团队在生产环境里禁用外键主要原因是性能和高并发下的锁问题。外键约束每次写入都要去检查主表多了一次查询而且在某些场景下会扩大锁范围影响并发。这不是外键本身错而是用错了地方。我的建议是核心交易链路、并发量高的表尽量不用外键靠应用层保证后台管理系统、配置表、数据一致性要求极强但写入量低的场景外键值得用。严格分级看待而不是一刀切。3. 约束设计的实战思路从建表开始就减少未来的坑约束不是写完就完事的它的设计直接关系到你未来两年维护这个表的幸福感。这节讲讲我在实际项目中是怎么取舍的。3.1 列属性设计的顺序先定类型再定约束很多新手建表先纠结这列该不该有默认值其实顺序反了。正确顺序是先确定这列存什么类型的数据选对类型整数、字符串、日期、布尔。再问语义这列能不能为空如果业务上没有和填了空串含义不同就按需加NOT NULL。再问规则这列允不允许重复需要的话加UNIQUE。最后问兜底如果不填给什么默认值举个例子一个后台员工表的last_login_time字段。业务上从未登录是一个合法的中间状态所以这一列允许为NULL比较合理但如果你用DATETIME DEFAULT CURRENT_TIMESTAMP那就意味着创建时默认等于创建时间这不等于从未登录语义直接冲突了。所以我倾向于这样设计last_login_time DATETIME NULL DEFAULT NULL这样查询时用IS NULL来判断从未登录逻辑既不会误伤也不会产生歧义。3.2 组合约束的几个经典案例案例A逻辑删除与唯一约束的冲突业务上最常见的需求是同一用户不能有两个未删除的手机号记录但你做的是逻辑删除is_deleted物理数据还在表里。如果直接在phone上建唯一约束删掉一条再插同号会被卡住。解决方案通常有两种把删除标记并入唯一键UNIQUE KEY uk_phone_deleted (phone, is_deleted)。但问题是同一个手机号被删两次(phone, 1)又插不进去了。更通用的方案是引入删除批次号deleted_batch VARCHAR(32) NOT NULL DEFAULT 然后唯一键设为(phone, deleted_batch)。删除时给该行生成一个UUID批次号物理上没有重复的(phone, uuid)了。这种设计常见于需要大量逻辑删除场景的业务表。案例B联合唯一是分页数据的天然保证比如一个角色权限关联表(role_id, permission_id)联合唯一既防重复又能充当查询索引。一张中间表如果连联合唯一都没有迟早会插出大量重复授权记录接口返回来满屏相同数据排查起来相当痛苦。案例C默认值与业务状态机配合状态字段用TINYINT NOT NULL DEFAULT 0配合一张枚举表说明每个数字的含义。不要在状态列上直接设NULL否则业务里空状态和无状态混在一起后统计口径会崩。3.3 到底要不要所有字段都加NOT NULL网上有种说法是禁止任何字段为NULL这个观点过于绝对。有些字段确实该允许NULL比如备注扩展信息最后登录时间。这些字段的NULL值可以被当成一种携带信息的状态。真正的区别在于NULL是否有业务语义。有语义就保留NULL没语义就一律NOT NULL DEFAULT。我见过一个比较稳妥的分法所有参与运算、统计、比较的数值字段一律NOT NULL DEFAULT 0所有状态位、标志位一律NOT NULL DEFAULT 0或NOT NULL DEFAULT 1所有时间字段按需选择但创建时间通常NOT NULL DEFAULT CURRENT_TIMESTAMP自由文本、备注、扩展字段允许NULL但查询时心里要清楚NULL的语义4. 约束的后期管理改约束比建约束容易踩坑建表时想清楚约束是最好的但现实往往是老表已经跑了好几年数据里存了一堆脏值这时候想补约束麻烦就来了。这节我讲几个改约束的场景和操作细节。4.1 给已有表添加约束给已有列添加非空约束时MySQL要求该列现有数据不能有NULL。所以老表加NOT NULL前通常先做一次数据清洗-- 步骤1把NULL值替换为兜底值 UPDATE t_user SET nickname 未填写 WHERE nickname IS NULL; -- 步骤2修改列属性 ALTER TABLE t_user MODIFY COLUMN nickname VARCHAR(50) NOT NULL DEFAULT 未填写;同样的道理给已有列加唯一约束前得先确认没有重复数据-- 查询有重复的组 SELECT phone, COUNT(*) FROM t_user GROUP BY phone HAVING COUNT(*) 1; -- 去重完成后再添加唯一约束 ALTER TABLE t_user ADD UNIQUE KEY uk_phone (phone);这一步如果数据量巨大比如千万级必须先评估耗时。ALTER TABLE在MySQL 5.7及以下版本是锁表的8.0虽然引入了ALGORITHMINPLACE优化但一些场景依然会锁。大表加约束务必在低峰期执行或者借助在线变更工具。4.2 删除与修改约束删除主键ALTER TABLE t_user DROP PRIMARY KEY;删除唯一约束本质删索引ALTER TABLE t_user DROP INDEX uk_phone;删除外键约束注意要用约束名不是列名ALTER TABLE t_order_item DROP FOREIGN KEY fk_item_order;修改约束通常没有一条语句直接改的路径一般是先删再加。比如你想把某个唯一约束从单列换成多列组合就要先删旧约束再ADD新约束。这中间有个空窗期操作要在事务或低峰期处理防止期间插入重复数据。4.3 SQL_MODE对约束的隐性影响约束的行为受sql_mode影响这点容易被忽略。在宽松模式下比如sql_mode不含STRICT_TRANS_TABLES向NOT NULL列插入NULLMySQL不报错而是把列改成隐式默认值只产生一条warning。很多研发在本地开发环境没开严格模式测试怎么插都成功上线后在生产环境被报错搞得一头雾水。开发、测试、生产三个环境的sql_mode保持一致是约束正常工作的前提。MySQL 8.0默认sql_mode是带严格模式的5.7也默认开启。但某些云数据库默认配置可能被调过。建议执行下面语句确认一下SELECT sql_mode;比较理想的配置至少包含STRICT_TRANS_TABLES和NO_ENGINE_SUBSTITUTION前者保证约束不被悄悄绕过后者防止建表时引擎被静默替换。4.4 线上改约束的经典事故场景我参与过一次事故排查印象极深某后台系统的用户表mobile列历史上有大量NULL和重复值。团队给mobile加了唯一约束之后当天晚上发现有一个定时任务开始报错——因为这个任务执行时如果手机号为空会往表里插入一条mobile NULL的记录。字段本身没有NOT NULL而唯一约束又允许NULL重复所以当时插入没失败问题出在另一个地方这个任务还会把空手机号更新成同一个兜底字符串空串是具体值唯一约束拦住了第二次更新。结果就是任务永远卡在第一次更新之后形成了一个极其隐蔽的循环失败。后来怎么解的把mobile的语义彻底分清要么为空NULL要么为用户真实手机号。加了NOT NULL DEFAULT 后再配合正则校验才把这块彻底稳住。这件事让我养成了一个习惯加约束前把现有数据里有哪些非法值完整列一遍再决定兜底策略。别让约束成为定时炸弹。5. 约束与索引的协作关系一张表的设计权衡约束和索引在MySQL里是纠缠在一起的。唯一约束创建唯一索引主键创建主键索引外键创建普通索引。理解这层关系你才能在设计表时做出合理的权衡。约束类型自动创建的索引索引类型删除约束的动作PRIMARY KEYPRIMARY聚集索引DROP PRIMARY KEYUNIQUE约束名对应的索引二级索引唯一DROP INDEX 约束名FOREIGN KEY自动生成普通索引若列上已有索引则复用二级索引DROP FOREIGN KEY 约束名NOT NULL / DEFAULT不创建索引无改列属性这张表意味着你每加一个唯一约束就同时获得了一个查询加速能力。比如用户表的phone列加了唯一约束那么按手机号登录时用的索引覆盖就有了。同理外键列自动生成的索引也能加快从表按主表ID反查的速度。但反过来索引不是免费午餐。每个索引都会增加写入的开销因为插入、更新、删除时都要同步维护索引树。所以约束加得太多太滥也会让写入变慢。设计原则是该有的约束必须有不必要的索引一个也别留。比如一张只有几十行数据的配置表加不加索引无所谓一张日增百万的流水表每个多余的索引都是实打实的写入损耗。还有个关联点唯一约束和普通索引在去重场景下的选择。如果你想防重复必须用唯一约束如果你只想加速查找用普通索引就够了。后者允许重复值但写入开销更小。搞清楚业务自己要的是什么不要为了顺手就乱加。在分库分表的大数据架构下唯一约束的效果会打折——唯一性只能在单个分片内保证跨分片就无能为力了。这种场景下唯一ID通常由全局发号器生成数据库约束已经退居二线。如果是单库单表的项目约束依然是性价比最高的防线先把数据库约束吃透再谈中间件层的高级方案。6. 一张表描述全部约束设计的完整示例最后用一个相对完整的示例把上面所有内容串起来。假设我们设计一张简单的订单表业务规则是订单号必填且唯一、下单用户必填、订单金额默认0、状态默认待支付、备注可空、关联的用户必须存在。CREATE TABLE t_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, order_no VARCHAR(32) NOT NULL COMMENT 订单号, user_id BIGINT UNSIGNED NOT NULL COMMENT 下单用户ID, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2已取消, remark VARCHAR(255) NULL COMMENT 备注, 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_order_no (order_no), KEY idx_user_id (user_id), CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES t_user(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这个设计的逻辑是这样的id自增主键保证行物理顺序插入有序避免随机主键带来的页分裂。order_no是业务单号单独做唯一约束天然防重复下单。user_id建外键关联用户表保证订单不会落到不存在的用户头上。如果并发量极高这一条可以拆掉放到应用层校验但一般的后台业务外键完全扛得住。amount用DECIMAL(10,2)而不是FLOAT避免浮点误差默认0保证统计时不出现NULL陷阱。status用TINYINT NOT NULL DEFAULT 0并注释清楚状态含义。created_at和updated_at都是数据库层维护应用层不用管。这里面其实还隐含了一个设计细节KEY idx_user_id (user_id)。虽然外键会自动建索引但如果你决定不用外键这个索引得手动建否则按用户查订单的SQL会全表扫。约束可以拆掉索引不要忘记留。7. 实际维护中的几个个人体会约束这东西建表时多花十分钟琢磨能替你在未来省下几个通宵。说几个我自己的坚持第一任何表都必须有主键尤其是InnoDB表。就算业务上找不到天然的唯一键建一个自增id成本最低、收益最高。没有主键的InnoDB表在复制、备份、数据修正时都会出各种幺蛾子。第二唯一约束的语义要落到字段组合而不是直觉。好好理清哪些列组合起来才是业务上的唯一比到处乱加约束重要得多。一个约束加错后面业务方会天天来提工单。第三改约束前先备份、先查重复、先看执行计划。一套组合拳下来线上事故率会低很多。尤其是大表加唯一约束一定要先在从库或测试环境完整跑一遍算好时间窗口再动生产。第四把约束的删除脚本留在项目的数据库变更记录里。哪天约束定义有变化你能清楚地从历史记录里看到这个字段以前是什么约束、为什么改。这比靠记忆靠谱得多。最后分享一个小技巧用SHOW CREATE TABLE查看别人建的表时先扫一遍约束定义再扫字段定义。约束规划往往比字段类型更能反映设计者对业务的思考深度。你读懂了约束就读懂了半张表。