数据库约束2从非空到检查约束把表结构这张“安全网”织密写数据库的都知道约束这东西平时不太起眼但一到线上出脏数据、业务侧报错或者统计结果对不上的时候回头看十有八九都是约束设计没到位。上一篇我们重点讲了主键约束和外键约束这两个算是约束里的“骨架”一个管唯一标识、一个管表间关系。但骨架立起来之后血肉里的细节还得靠剩下几个约束来兜底——非空约束、默认值约束、唯一约束、检查约束。这一篇我把它们一次性讲透包括适用场景、使用方式、坑点以及平时维护表结构时最容易被忽视的细节。这篇文章适合刚把MySQL基础语法过完一遍、开始真正设计业务表的人看也适合已经写了一阵子SQL、想回头把表结构整理得更规范的同学。你要是手头正好在建表直接照着文里的写法抄也行。1. 约束的整体设计思路约束不是“限制”是表结构的一部分很多人对约束的理解停留在“不让填错数据”这个层面这个说法没错但格局小了。约束在数据库里的作用更像是一道防线——它把数据从“可能出错”变成“结构上不允许出错”。换句话说约束不是事后补救的工具而是事前拦截的机制。1.1 一套表里到底该用哪些约束MySQL里的约束可以分为六类主键约束PRIMARY KEY、外键约束FOREIGN KEY、唯一约束UNIQUE、非空约束NOT NULL、默认值约束DEFAULT、检查约束CHECK。严格来说默认值约束并不算传统意义上的“约束”它更多是兜底补给但放在一起理解更方便。主键和外键是“骨架”上一篇已经讲过这篇重点就是后面这四个。它们在实战中有一个共同特点单独看都不难难的是组合在一起的设计判断。举个例子手机号字段要不要加非空加了非空之后默认值怎么处理既加了非空又加了唯一和直接拿它当主键有什么区别这些问题在真实项目里特别容易纠结我会在后面一个个拆开讲。设计表结构时我的习惯是先想清楚这个字段的语义再去套约束。字段代表什么、允许不允许为空、重复有没有意义、缺省时该是什么值——这四个问题过一遍对应的约束自然就出来了。反过来如果不上约束就建表创建表一时爽后面业务逻辑就要到处救火。1.2 为什么这四类约束要“组合”使用约束之间不是互斥的很多字段同时会命中多个约束。比如创建时间字段一般是“非空 默认值当前时间”用户手机号字段是“非空 唯一”状态字段是“非空 默认值 检查范围”。组合使用才能表达出完整的字段语义。聊一个小例子某项目的订单表里有个status字段初期只加了默认值0没加CHECK约束也没加非空。结果后面接入的数据同步工具传进来一批status NULL的记录程序里if status 1的判断全部落空整整查了两天才定位到根因——不是程序逻辑错了是表结构允许了“非法状态”入库。如果当初在建表时就写上status TINYINT NOT NULL DEFAULT 0后面再配合检查约束锁死范围这批脏数据根本进不来。这就是约束组合的意义单独看每个约束都不复杂组合起来才是完整的数据规则表达。2. 非空约束与默认值约束最基础也最容易被忽视的两个“守门员”非空约束和默认值约束在开发里最常见也是很多人建表时不假思索就写上、却很少细想语义合理性的两个约束。因为它们太基础了翻车的概率反而很高。2.1 NOT NULL为什么“可空”要付出额外代价非空约束的本质很简单这一列的值不允许为NULL。但真正需要理解的是NULL在数据库里的独特地位。NULL不是0不是空字符串它表示“未知、缺失、未定义”。所有和NULL的计算结果几乎都是NULL。一个字段上做的SUM、AVG、COUNT如果里面有NULL结果都会受到干扰。COUNT(column)会主动跳过NULL行这往往导致统计结果和直觉不符。所以在设计时一个简单判断标准就是这个字段的值在业务上是否永远存在如果永远存在就应该加NOT NULL。比如用户的注册时间、订单的创建时间、商品的价格、库存数量——这些都是业务上必然存在的值。反观“备注”“扩展信息”“昵称头像”这类字段允许为空是合理的甚至空字符串也比NULL更适合表达“用户没填”这个语义。有人会用NULL去表达“暂时还没值”这个思路要分场景。如果是“还没到填写时机”用默认值代替NULL通常更稳妥如果用NULL表达“用户确实没填”那就要确保所有SQL查询都考虑到了NULL的干扰程序代码里也得专门处理空值判断。宁可让程序员多写几行判断也不能让数据库里塞满语义不明的NULL——前者只是成本后者是隐患。2.2 DEFAULT给字段一个“不劳而获”的兜底值默认值约束像是一个“后补选手”插入数据时如果没给这个字段传值数据库会自动用默认值顶上。它能减少业务侧重复传参更重要的是保证数据完整性。最典型的场景是创建时间。建表时我见过太多这样的写法CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, created_at DATETIME NOT NULL );后面每次插入都要手动写NOW()漏一次就报非空错误或者在INSERT里不写这个字段直接报错。正常做法是让数据库自己管理CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );这样插入时连created_at都不用管数据库自动写入当前时间更新记录时updated_at还会自动刷新。这个“自动刷新”的能力是MySQL在时间类字段上提供的特色功能其他类型字段享受不到。默认值还有一些“隐性规则”。字符串类型的默认值不要用NULL直接给空字符串更合适数字类型的默认值给0还是给-1要看业务语义日期类型默认值是CURRENT_TIMESTAMP还是具体日期也要根据场景判断。还有个容易踩的坑DEFAULT的值类型必须与字段类型兼容比如VARCHAR字段默认值不能是数字MySQL 8.0 对这类错误会直接报错。2.3 实操在现有表上补加非空与默认值如果表已经建好了想补加非空约束MySQL不能用简单的ALTER TABLE ... MODIFY之外绕弯子。直接改写法如下ALTER TABLE users MODIFY COLUMN username VARCHAR(50) NOT NULL;执行之前必须先确认字段里没有NULL值。否则一个报错告诉你Data truncated或者直接提示有NULL那也是“预期内的惊吓”。稳妥的做法是先查一遍SELECT COUNT(*) FROM users WHERE username IS NULL;查出结果为0再执行修改。这是我在实际维护中养成的习惯——任何表结构变更之前先查脏数据再动手。3. 唯一约束比你想的更有用也比你想的更容易踩坑唯一约束UNIQUE保证一列或一组列的值在表中不重复。它和主键有点像但一个表只能有一个主键却可以有多个唯一约束。更关键的区别是主键隐含了非空属性而唯一约束允许NULL。3.1 单列唯一与联合唯一的应用场景单列唯一很好理解业务上“不允许重复”的字段都要加。比如用户名、手机号、邮箱、订单号。加了唯一约束之后重复插入直接报错不用在代码里先查一遍再插既省了一次查询又避免了并发场景下两人同时插入同一个值的问题。联合唯一则更有意思。它表达的不是“单列不重复”而是“列的组合不重复”。比如一张表里记录用户和角色的一对多关系一个用户可以拥有多个角色但同一个用户不能重复拥有同一个角色。这个约束用联合唯一表达最合适CREATE TABLE user_roles ( user_id INT NOT NULL, role_id INT NOT NULL, PRIMARY KEY (user_id, role_id), UNIQUE KEY uk_user_role (user_id, role_id) );有同学会问主键已经是联合的了再加联合唯一不是多余吗这种情况是挺多余。但如果两张表的主键设计不需要联合主键只是业务逻辑上要求组合不重复那联合唯一就派上用场了。比如收藏表CREATE TABLE user_favorites ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, article_id INT NOT NULL, UNIQUE KEY uk_user_article (user_id, article_id) );主键用自增 id但业务上真正要防重的是(user_id, article_id)这个组合。这是联合唯一的经典场景。3.2 唯一约束中NULL值行为所有数据库都不一样这一节必须单独讲因为“唯一约束允许NULL”这件事在不同数据库里的表现完全不同。MySQL里唯一约束的列如果有多行NULL是允许的——因为NULL不等于NULL。换句话说UNIQUE约束不会阻止你插入两行都是NULL的记录。这在某些业务场景下会带来隐患。比如有个deleted_email字段逻辑删除时记录邮箱正常情况下是NULL删除后写入邮箱地址加了唯一约束防止同一个邮箱被重复删除。结果MySQL允许N条NULL记录共存唯一约束形同虚设。要解决这个场景常见思路是用空字符串替代NULL或者单独用“状态 邮箱”的联合唯一。另一个坑如果你用INSERT ... ON DUPLICATE KEY UPDATE或REPLACE INTO写入数据碰到唯一约束冲突时触发的是更新或删除重插而不是报错。一不小心就会把现有数据改掉。所以唯一约束不是无条件安全的业务写入方式也会影响它的行为。3.3 唯一约束的命名与索引属性唯一约束在MySQL中实现为唯一索引这一点体现在SHOW INDEX FROM 表名里会看到相关记录。所以“加唯一约束”和“建唯一索引”在MySQL里基本是一回事但不同点在于约束是逻辑层面的规则索引是实现层面的结构。理解这一点后面排查慢查询时如果想“顺手把这个字段的查询加速”就可以直接利用唯一索引不用额外建普通索引。命名上我建议统一前缀uk_例如uk_username、uk_user_role。这是个很小的习惯但后期维护ALTER TABLE时如果没有名字MySQL会自动生成一个名字想删都不好删——得先去information_schema里查出来再动手凭空多一步操作。4. 检查约束历史遗留的大坑以及8.0之后的正确打开方式检查约束CHECK是数据库里“显式规则”的代表。它允许你写一个条件表达式MySQL在建表或修改表时校验条件是否为真不满足的条件直接拦截数据写入。4.1 MySQL版本差异你的CHECK到底生没生效这一节是所有人都该知道的“历史遗留坑”。MySQL 5.7及更早版本CHECK约束在语法上是支持的但解析后直接忽略不产生任何实际效果。也就是说你在5.7里写了CHECK (age 18)数据照样能插入17岁的记录MySQL连眉头都不皱一下。从MySQL 8.0.16开始CHECK约束才真正生效。如果你用的还是5.7或者早期8.0版本千万别以为写上了CHECK就万事大吉。验证方法很简单SHOW CREATE TABLE 表名;如果建表语句里保留着CONSTRAINT ... CHECK (...)说明生效了如果被吞掉或者压根不显示说明你的版本没启用。另外一个验证办法是直接插入一条违反规则的数据能插进去就说明CHECK是摆设。4.2 检查约束实战年龄、性别、状态的写法8.0.16之后CHECK的实用性上来了价值主要体现在“把业务侧的逻辑判断下沉到数据库”。比如用户表CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, age INT CHECK (age 18 AND age 120), gender CHAR(1) CHECK (gender IN (M, F, O)), status TINYINT NOT NULL DEFAULT 0, CONSTRAINT chk_status CHECK (status IN (0, 1, 2)) );年龄的检查约束用区间表达式性别用枚举式 IN 表达式状态字段建议给约束起名字这样后期想去掉时方便精准定位。检查约束里还支持复合表达式比如“VIP等级大于3的用户积分必须大于1000”这种跨字段的逻辑可以写成CONSTRAINT chk_vip_points CHECK (vip_level 3 OR points 1000)这种写法在业务上相当于在数据入口处又装了一道闸门。但它也有边界如果业务规则频繁变化每次都要ALTER TABLE改约束这比在代码里改逻辑要笨重得多。所以检查约束适合那些“业务上不可能变”的规则比如年龄范围、性别枚举、状态码范围不适合经常调整的规则。4.3 多个检查约束的命名与维护给CHECK约束命名值得专门说一遍。一个表里可能有多个检查约束如果不命名MySQL自动生成的名字根本没有可读性后面想单独删除其中一个只能瞎子摸象。命名用chk_前缀加字段名如chk_age_range、chk_status_value。删除单个约束的标准语法ALTER TABLE users DROP CHECK chk_status_value;注意MySQL 8.0检查约束的名字在information_schema.CHECK_CONSTRAINTS表里能看到。修改一个字段的检查范围不能直接改表达式要“先删再加”ALTER TABLE users DROP CHECK chk_status_value; ALTER TABLE users ADD CONSTRAINT chk_status_value CHECK (status IN (0, 1, 2, 3));这两条语句最好放在一个事务里执行避免中途异常导致约束丢失。虽然ALTER TABLE在MySQL里会触发隐式提交但分开执行仍然存在窗口期生产环境操作前先评估好。5. 约束的管理与维护不是建完表就结束后面全是细活很多人建表时约束设计得挺好的但后面业务迭代需求变来变去表结构也跟着改。这时约束的维护就变成了一个“细活”每次变更都要谨慎。5.1 查看表上现有约束的方法日常排障和变更之前第一步一定是“看清现状”。我常用的方法有三个一是直接看建表语句最快速SHOW CREATE TABLE users\G二是查information_schema里的约束信息适合脚本化处理SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME users;三是看索引信息里的唯一约束SHOW INDEX FROM users;三个方法结合起来表上的主键、外键、唯一、检查就一目了然。特别是变更前我会把这三个全跑一遍确认没有遗漏的约束指向目标字段。5.2 在已有表上添加/删除约束的完整操作加非空约束用MODIFY COLUMN加默认值也可以用同样的方式ALTER TABLE users MODIFY COLUMN status TINYINT NOT NULL DEFAULT 0;加唯一约束ALTER TABLE users ADD UNIQUE KEY uk_username (username);加检查约束ALTER TABLE users ADD CONSTRAINT chk_age_range CHECK (age 18 AND age 120);删除唯一约束时要小心命名。如果你建表时没给唯一约束命名MySQL会自动生成字段名作为索引名删除时直接写字段名即可ALTER TABLE users DROP INDEX uk_username;删除非空约束就是把NOT NULL去掉重新MODIFYALTER TABLE users MODIFY COLUMN username VARCHAR(50) NULL;这一系列操作的共同点执行前备份数据、先小表验证、在低峰期操作。变更表结构会触发表重建或元数据锁大表在线变更可能会出现锁等待严重时影响线上读写。这些操作在开发环境和测试环境随便跑生产环境建议谨慎操作。5.3 表结构变更与约束冲突的处理流程实操中最常见的场景是业务侧要求放宽某个字段的约束比如原来username是非空现在要允许为空。这个需求看似简单但你要先做三件事第一确认现有数据没有违反新规则的。如果原来非空现在要放开那没有冲突反过来原来允许为空现在要加非空必须先处理掉已有的NULL行。第二检查程序代码里是否有依赖旧约束的逻辑。如果代码里写了“username一定存在”的判断数据库约束一放开代码不会报错但逻辑就失效了这属于隐蔽问题。第三考虑约束变更影响的关联对象。比如视图、存储过程里如果依赖了旧的表结构ALTER TABLE之后这些对象可能直接失效。我上一份项目就遇到过某核心表要把一个字段从非空改成可空结果忘了有个视图定义里用了这个字段做INNER JOIN视图直接失效接口报错查了半天。这个教训说明表结构约束变更的影响面永远比看到的要大。5.4 约束变更时的SQL执行顺序建议多个人同时维护一个库的时候顺序乱是常态。我的建议是先加后减先新后旧。要新加一个约束先加新的、验证没问题再考虑删旧的要替换约束先加新约束再删旧约束。这样可以保证任何一个时间点表都不会处于“无约束裸奔”的状态。另外执行顺序上尽量把DDL合并。例如同时要加唯一约束和检查约束一条ALTER TABLE里一次写完比拆成两次执行要安全因为执行期间表上的锁次数减少出问题的概率也低。6. 约束相关的高频问题与排查实录这一节整理一下我在实战中和答疑时经常碰到的问题。每个问题背后都是一段真实的“踩坑”记录看看你有没有中过招。6.1 为什么插入了重复数据没有报错唯一约束失效了大概率原因是唯一约束的列里有NULLMySQL不认为两个NULL相等。还有一个可能是你用的表引擎是MyISAM部分约束行为与InnoDB有差异。另外如果你是通过INSERT IGNORE或者REPLACE INTO写入的MySQL会悄悄跳过冲突记录或者先删后插不会向你报错。检查思路是先确认约束存在再看数据里有没有NULL最后看一下写入方式——三步排查法几乎能覆盖所有场景。6.2 CHECK约束明明写了违反规则的数据还是进来了先查版本。MySQL 8.0.16之前CHECK就是摆设。8.0.16之后写CHECK注意有没有语法错误如果建表语句执行成功但CHECK没生效大概率是版本不够。第三个可能性表已存在时加了CHECK但约束加在哪个字段上写错了条件。用SHOW CREATE TABLE看一眼是最稳妥的。6.3 加了默认值约束插入时却还要求必须传值默认值约束只在“插入语句中没有指定该列”时生效。如果你用了INSERT INTO users (username, created_at) VALUES (x, NULL)显式传了NULL默认值不会接管这个NULL。这一点经常被混淆——默认值不等于“自动修正NULL”它只作用于“缺失值”的场景。如果希望NULL自动转默认值需要结合DEFAULT和触发器或者程序逻辑来处理。6.4 修改表结构时提示“Duplicate entry”错误怎么办这个提示说明你给字段加唯一约束但表中已经有重复数据了。处理方案先把重复数据查出来去重后再加约束。查询重复数据的SQL很简单SELECT username, COUNT(*) FROM users GROUP BY username HAVING COUNT(*) 1;处理重复数据时保留哪一条要根据业务判断。我的习惯是保留id最小的那条把其他的更新或归档后再执行加约束操作。6.5 大表加约束特别慢有没有更快的方式大表加非空或唯一约束本质上是重建表的过程耗时与数据量成正比。MySQL 8.0支持在线DDL部分ALTER操作不需要锁表但底层仍然要扫描校验数据。如果表实在太大建议分批次处理先加普通索引再通过业务逻辑逐步清理脏数据最后在低峰期一次性加上约束。有些团队会借助工具做在线变更但操作复杂度会高不少非必要情况下还是尽量在设计阶段就把约束定好避免后期大规模返工。7. 一套小型示例从零设计带完整约束的业务表最后用一套完整的示例把刚才所有约束串起来。假设我们在做一个社区应用核心表是用户表、文章表、评论表。设计时我会按下面这种思路来做。用户表CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, mobile CHAR(11) NOT NULL, email VARCHAR(100) NULL, age INT CHECK (age 0 AND age 120), status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_username (username), UNIQUE KEY uk_mobile (mobile), UNIQUE KEY uk_email (email), CONSTRAINT chk_status CHECK (status IN (0, 1, 2)) );文章表CREATE TABLE articles ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, title VARCHAR(200) NOT NULL, content TEXT NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_articles_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT chk_article_status CHECK (status IN (0, 1, 2)) );评论表CREATE TABLE comments ( id INT PRIMARY KEY AUTO_INCREMENT, article_id INT NOT NULL, user_id INT NOT NULL, content VARCHAR(500) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_comments_article FOREIGN KEY (article_id) REFERENCES articles(id), CONSTRAINT fk_comments_user FOREIGN KEY (user_id) REFERENCES users(id) );这套设计的要点是字段级约束管字段的合法性表级约束管字段之间的关系和组合规则主键、外键、唯一、非空、默认值、检查一个不少。看起来约束加得很多但每一条都有明确的业务语义没有无意义的冗余。后续代码里写插入逻辑时只需要专注业务本身数据库会自动拦截违反规则的数据。我在实际项目中的体会是约束设计最考验的不是“会不会用”而是“该不该用”。每次写建表语句前把字段的业务语义过一遍想清楚这个字段允许什么、不允许什么、缺省应该是什么建表的质量就能提升一大截。约束永远是设计阶段考虑得越充分上线后越省心的东西。你要是手里正好有线上表还没加约束建议从今天开始逐个字段排查一遍把该补的补上别等到脏数据出了事再回来补那就不是改一条SQL的事而是要陪着业务方一起清理数据、核对账目、解释事故了。