先说说我遇到这个数据库报错时的场景。某天凌晨刚躺下手机连着震了三次群里刷出来一串红色告警“ALTER TABLE执行失败错误码1138 – Invalid use of NULL value”。这个报错对干数据库这行的人来说不陌生但真到自己值班时任何一个报错都够让人瞬间清醒。当时那条SQL其实很简单就是把一张业务表里的邮箱字段从允许NULL改成NOT NULL结果执行到一半直接被MySQL拦了下来。表结构没改成功线上业务倒是没受影响但排好的变更窗口全被打乱后面连着两个需求都得重新排期。其实1138这个错误反复出现的场景特别集中你不是第一次见到它也不会是最后一次。开发环境自测、上线前结构变更、数据迁移后的字段加固、ORM框架在代码里默认把空值写成NULL这些场合都容易撞上。只要表里已经存在NULL值而你又想把字段收严成NOT NULLMySQL就会毫不客气地甩出这句Invalid use of NULL value。这篇文章就把这个报错拆开揉碎讲清楚它是什么、怎么排查、有哪些解法、生产环境执行时要注意什么。无论是刚入门的新人还是被线上问题折腾的DBA都能直接照着操作。1. 先搞清楚这个报错到底在说什么1.1 一个真实场景的复盘先还原一下我上面提到的场景。当时的业务需求是用户注册后必须填写邮箱历史数据里却有大量用户没有邮箱所以结构上一直允许NULL。这次要做结构变更SQL长这样ALTER TABLE users MODIFY COLUMN email VARCHAR(255) NOT NULL;执行后MySQL直接报错ERROR 1138 (22004): Invalid use of NULL value我当时第一反应是看表里有多少NULL。果然问题不在SQL语法而在数据本身。执行SELECT COUNT(*) FROM users WHERE email IS NULL;返回了三千多条。也就是说历史数据里存着几千个“没填邮箱”的用户而这条ALTER语句要求“每一行都必须有邮箱”数据库当然不答应。这个报错的逻辑其实特别简单NULL在SQL里代表“这个值不存在/未知”NOT NULL代表“这个字段必须有值”两句话是直接矛盾的。你用一条DDL强制要求“所有行都必须有值”但表里明明躺着一堆“没有值”的行MySQL就会在执行阶段发现数据违反约束然后停止操作并回滚。整个链路用一句话概括表里有NULL你却想加NOT NULL数据库拒绝执行。1.2 1138背后的约束检查机制很多人以为这个错误是语法问题其实不是。SQL语句在MySQL里要经过解析、优化、执行三个阶段语法错误在解析阶段就会被抓出来但1138不是在解析阶段抛的而是在执行阶段才被发现。具体来说MySQL在修改表结构时会逐行检查已有数据是否满足新的约束。尤其是MODIFY COLUMN这类操作它会读取目标列的所有数据逐一比对“是不是存在NULL”。一旦发现任何一个NULL立刻中断执行扔出错误码1138。这也是为什么很多时候ALTER TABLE执行到一半才报错因为检查过程已经开始了只是碰到第一个违规数据就停下来。这里还有个容易忽略的点sql_mode会影响报错的行为。在MySQL的严格模式下STRICT_TRANS_TABLES或STRICT_ALL_TABLES只要出现非法NULL值语句会直接失败并完整回滚。但如果关闭了严格模式某些DML操作对NULL的容忍度会变高比如INSERT时往NOT NULL字段塞NULL会被静默转成该类型的默认值数字转0字符串转空串并产生一条warning而不是直接报错。可ALTER TABLE不太一样它属于结构变更即便非严格模式下也不会“自动把NULL转默认值”依然会甩出1138。这个差异我后面会专门讲很多人就是在这里踩的坑。1.3 最容易触发1138的三种操作我统计了一下自己处理过的工单触发这个报错的场景基本集中在以下三种ALTER TABLE把可空字段改成NOT NULL这是头号场景比如给字段加非空约束、调整字段属性时顺手加个NOT NULL。常见语句是MODIFY COLUMN或CHANGE COLUMN。UPDATE语句显式把NULL赋给NOT NULL字段这种情况多见于代码里没有做空值判断ORM框架传参时把空字符串转成了NULL。例如UPDATE users SET email NULL WHERE id 1;INSERT语句插入NULL或漏写字段显式写NULL是最直白的违规漏写字段的隐蔽性更高因为插入操作会为没写的字段填入“隐式默认值”在MySQL里如果没有显式DEFAULT这个隐式值很可能就是NULL。同样报1138。此外还有一种间接场景从外部系统导入数据CSV里空单元格被读成了NULL正好又要同步改表结构两件事撞在一起就成了1138。理解了触发链条接下来的排查思路才清晰先找到哪个字段、哪些数据是NULL再决定怎么补。2. 接到报错之后别急着补数据2.1 把报错现场完整记录下来很多人一看到1138第一反应就是“那把NULL改成空字符串不就行了”。这话方向没错但直接上手改之前有几件事必须做。别嫌啰嗦线上出过太多因为“手快”搞出来的事故。先记录报错现场具体的SQL语句、执行时间、执行客户端、是否在事务里、事务里还有没有其他操作。这些信息看起来不重要但决定你的恢复路径。比如如果报错发生在事务里并且事务已经半途中断回滚后的状态和事务前的状态可能不一样如果是在存储过程里报错整段过程是否回滚还要看异常处理逻辑。把这些信息留在手边后面分析才能有据可依。还要看一眼当时的锁状态和会话情况SELECT * FROM information_schema.innodb_trx\G如果线上刚好有长事务在跑你的ALTER TABLE可能在排队等锁。这时候不要反复重试先看看锁等待情况避免把CPU和IO白白烧在无谓的尝试上。2.2 一步步定位到具体字段和数据如果报错语句已经告诉你涉及哪张表哪个字段定位很快。但有些场景下报错信息不够具体尤其是通过中间件或ORM转发过来的SQL可能被包装成了一长串调用链。这时可以从两个角度入手。第一个角度查所有可空字段。用information_schema可以快速得到一个表里所有允许NULL的字段清单SELECT COLUMN_NAME, IS_NULLABLE, COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_database AND TABLE_NAME your_table AND IS_NULLABLE YES;第二个角度直接核对NULL数据的分布。定位到目标字段后统计NULL数量和占比SELECT COUNT(*) AS total_rows, SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS null_rows FROM users;注意这两条SQL在大表上执行可能很慢。尤其第二条如果表有上千万行直接全表扫会带来不小的IO开销。可以先用EXPLAIN看一下执行计划或者利用统计信息估算结果集大小挑选业务低峰期执行。2.3 动手前先回答三个问题排查完现场和数据分布急着上修复方案之前先回答三个问题。这三个问题的答案直接决定选哪个方案。第一个问题业务上这些NULL到底代表什么意义是“用户没有填过邮箱”还是“历史数据由于bug没写入”还是“导入时丢了数据”不同语义对应的补值方式完全不同。如果是前者补成空字符串是合理的如果是后者可能需要找回源数据不能用空字符串糊弄过去。第二个问题涉及的数据量有多大几千条可以直接UPDATE几百万条就要考虑分批甚至用在线DDL工具否则一条大事务下去主从延迟和锁等待会把你折腾到怀疑人生。第三个问题当前是不是业务高峰能不能容忍锁表ALTER TABLE在InnoDB下虽然支持在线DDL但MODIFY COLUMN能否使用瞬时算法取决于字段类型和长度是否变化以及MySQL版本。如果不能接受执行期间对表的写入影响就必须另选方案比如先加新列再迁移或者用工具平滑变更。这三个问题想清楚了再进入修复环节。盲目补数据是最忌讳的因为NULL在很多业务里是有含义的一律把它“洗”成空字符串后续统计、查询、报表全都可能对不上账。3. 四种解决方案按数据量选型3.1 小表直接UPDATE加ALTER数据量不大几千到几万行的情况下最直接的做法就是“先补值再改结构”。整个过程可以拆成四步每一步都加一条验证SQL确保不会带病执行。第一步备份。不管表大小结构变更前一定要有可回退的备份。可以用mysqldump单独导这张表mysqldump -u root -p --single-transaction your_database users users_backup.sql第二步把NULL补成业务认可的默认值。比如空字符串UPDATE users SET email WHERE email IS NULL;第三步确认没有漏网之鱼SELECT COUNT(*) FROM users WHERE email IS NULL;如果返回0才可以继续。第四步执行结构变更ALTER TABLE users MODIFY COLUMN email VARCHAR(255) NOT NULL DEFAULT ;这里有一个非常关键的认知MySQL不会自动用默认值去填充表里已经存在的NULL。你在MODIFY COLUMN语句里写了DEFAULT 只影响以后插入数据时的行为ALTER执行过程中遇到已有NULL照样报1138。所以必须先UPDATE再ALTER顺序不能反。3.2 大表分批清洗避免长事务如果表里有几十万甚至几百万个NULL一把梭UPDATE users SET email WHERE email IS NULL会引发一个大事务。大事务的问题不仅是锁时间长更麻烦的是会产生大量undo日志导致从库要回放很久主从延迟瞬间飙高。我曾经见过一次全表更新几十万行主从延迟直接拉到了十分钟以上报警系统被刷爆。正确的做法是按主键范围分批更新。假设users表主键是idUPDATE users SET email WHERE email IS NULL AND id BETWEEN 1 AND 5000; UPDATE users SET email WHERE email IS NULL AND id BETWEEN 5001 AND 10000;每批更新完停顿一小会让从库跟上再跑下一批。批大小按实际IO能力调整经验值一般在5000到20000行之间。如果主键不是连续分布的可以改成按主键排序后分页更新或者借助SELECT id ... LIMIT n先取出一批主键再用WHERE id IN (...)更新。分批的好处不只是控制锁范围还能让你随时中断。假如跑到一半发现业务数据有问题停下来分析情况再决定是否继续而不是把所有操作绑在一个不可分割的大事务里。分批更新完成后同样执行ALTER TABLE。要注意的是即便前面数据已经清洗干净ALTER本身在大表上也可能耗时较长。可以执行前先看会话级别的超时设置避免客户端说断就断SET SESSION innodb_lock_wait_timeout 50;3.3 不想改业务数据先加列再迁移有些场景下原字段的NULL值不能被覆盖比如历史数据需要保留原始状态。这时候可以直接换一种思路不修改原字段而是新增一个非空字段迁移完数据后换列名。操作步骤大致如下-- 第一步新增可空字段带默认值 ALTER TABLE users ADD COLUMN email_new VARCHAR(255) NOT NULL DEFAULT ; -- 第二步用COALESCE把原字段的NULL统一转成默认值 UPDATE users SET email_new COALESCE(email, ); -- 第三步校验新字段没有NULL SELECT COUNT(*) FROM users WHERE email_new IS NULL; -- 第四步删掉旧字段把新字段改名 ALTER TABLE users DROP COLUMN email, RENAME COLUMN email_new TO email;这个方案的好处是原字段数据从头到尾没有被修改每一步都有回退余地。比如第二步执行完发现数据不对可以直接ALTER TABLE users DROP COLUMN email_new;原字段还是原样。代价是表里会有个短暂的过渡字段写入时两个字段都要维护建议在业务低峰期操作。3.4 上千万元素的表直接用平滑变更工具数据量大到单条ALTER会在线上卡很久时就得借助专门的在线DDL工具了。我常用的是Percona Toolkit里的pt-online-schema-change原理是先创建一个结构一样的新表在旧表上加触发器记录增量变更然后分批把数据拷贝到新表最后通过原子性的RENAME TABLE切换新旧表。整个过程对线上写入的影响被控制在很小范围内。基本用法是这样pt-online-schema-change Dyour_database,tusers \ --alter MODIFY COLUMN email VARCHAR(255) NOT NULL DEFAULT \ --no-check-replication-filters \ --execute执行前要确保三个前提表有主键用于分批拷贝、没有触发器和外键冲突或者明确处理、磁盘空间足够存放新表副本。这个工具生成的触发器会占用少量性能所以同样建议低峰期跑。另一个选择是gh-ost它不依赖触发器而是通过解析binlog来同步增量数据对主库的压力更小但部署和配置成本略高。选择工具前先确认你对复制环境有足够了解否则工具本身可能成为新的故障点。4. 生产环境执行时最容易翻车的几个细节4.1 备份永远放在最前面这句话听起来像废话但每次线上变更总有人跳过去。我处理过的最惨烈一次案例是同事执行ALTER之前没有备份结果默认值选错了导致全表几百万条记录被改写。虽然最后靠binlog捞回来但整整花了三个小时期间业务一直被影响。备份不是“导出一份SQL文件”就行了。如果表在1GB以上mysqldump导出再导入回滚成本很高。物理备份工具XtraBackup更合适它会直接拷贝数据文件恢复速度比逻辑备份快一个量级。不管你用哪种都要在测试环境先演练一遍恢复流程。备份文件能正常导回库才是真安全。另外如果线上有主从架构结构变更前可以先在从库上执行一遍观察有无报错、耗时多长、从库延迟多少。确认没问题再轮到主库。灰度思想不能只用在业务代码里数据库变更同样适用。4.2 sql_mode带来的“静默成功”陷阱前面提过严格模式关闭时DML语句对NULL的处理会变宽容。举例来说INSERT INTO users(email) VALUES (NULL);如果字段是NOT NULL非严格模式下MySQL不会报错而是插入空字符串并给出一条warning。但ALTER TABLE是另一个逻辑。你很可能因此在测试环境得出结论“表里有NULL也没关系可以改成NOT NULL”因为你的测试连接关闭了严格模式上了生产生产连接开着严格模式1138就冒出来了。这种“测试好了、生产报错”的现象非常坑。所以动结构之前先统一确认两端环境的sql_modeSELECT sql_mode;尽量保证测试、预发、生产的sql_mode完全一致否则你踩到的只是环境差异不是真正的业务问题。4.3 唯一索引和默认值之间的“隐藏雷”这是一个特别容易被忽略的细节。NULL在MySQL的索引里有个特殊待遇多个NULL可以同时存在不受唯一索引约束。也就是说email字段即使有唯一索引两条email为NULL的记录也可以共存。但如果你把所有NULL都统一补成空字符串情况就变了。空字符串之间是相等的唯一索引会直接拦截第二次写入ERROR 1062 (23000): Duplicate entry for key uk_email更常见的是业务查询里拿IS NULL做判断补值之后条件全要改。所以补默认值前先确认这个字段有没有唯一索引如果有提前查一下补成默认值后会撞出多少个重复项SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) 1;如果确实有重复冲突可以考虑用带业务语义的唯一值来补比如邮箱不存在时写成空字符串加用户ID保证唯一。不过这种补偿值比较难看最好还是回到业务层面讨论NULL到底该怎么定义。4.4 从库延迟和锁等待的双重压力大表ALTER的过程中DDL在从库一样要执行而且从库通常是单线程回放延迟比主库更明显。如果在业务高峰执行主库的写入压力加DDL拷贝压力很可能把从库拖到“Seconds_Behind_Master”持续上涨。另外一个隐性风险是ALTER TABLE在执行过程中会持有元数据锁MDL锁如果此时有另一个查询长时间占用相关表DDL可能在“Waiting for table metadata lock”状态等上很久而它自己也会反过来阻塞后面的所有查询。这种情况在业务高峰期尤其常见。建议执行前用SHOW PROCESSLIST;看一眼有没有长时间运行的查询确认没有大事务占着表。如果实在躲不开高峰就用pt-osc这类工具把锁影响降到最低。5. 常见问题速查和我的经验清单5.1 问题速查表我把这些年遇到的典型情况整理成一个速查表方便各位在值班时快速对照。注意每一条的处理结果都要在执行后用查询验证别以“我感觉应该好了”收尾。报错/现象可能原因快速处理ALTER TABLE报1138目标表存在NULL值查NULL清洗后重试UPDATE赋NULL报1138字段有NOT NULL约束检查ORM代码改用COALESCEINSERT空值报1138显式NULL或漏写字段修改插入语句或表约束修改后唯一索引冲突补充的默认值重复用唯一值或临时策略去重大表ALTER引发从库延迟单条大事务分批或使用pt-osc5.2 一个最小复现实验为了方便你本地验证我留一个最小化复现脚本。MySQL 5.7和8.0都适用。-- 建表允许email为空 CREATE TABLE test_1138 ( id INT PRIMARY KEY, email VARCHAR(100) NULL ); -- 插入一条NULL数据 INSERT INTO test_1138 VALUES (1, NULL), (2, ab.com); -- 这里会报ERROR 1138 ALTER TABLE test_1138 MODIFY email VARCHAR(100) NOT NULL; -- 修复先补数据再改结构 UPDATE test_1138 SET email WHERE email IS NULL; ALTER TABLE test_1138 MODIFY email VARCHAR(100) NOT NULL DEFAULT ;这个实验能让你快速理解报错的行为特征不是语法错了是已有数据不满足新约束。剩下的就是你自己的业务数据该怎么处理的问题。5.3 治理NULL值的长期建议一次1138报错处理完不算完。后面更值得做的是从源头减少这类问题发生。我个人的做法是建立一套结构变更前的自检清单内容不复杂但真的管用新表字段能加NOT NULL就加不能加的必须写明理由。所有结构变更SQL在测试环境跑通并核对测试环境sql_mode和生产一致。上线变更前用脚本检查目标表NULL分布和唯一索引冲突风险。ORM实体类的字段校验和数据库约束要对齐别让代码里的“空字符串”传到数据库变成NULL。数据导入管线里加一道清洗任务把空单元格明确映射为NULL或默认值不让脏数据流入核心表。这套清单执行下来1138出现的频率会大幅下降。就算偶尔出现也能在几分钟内定位问题而不是让告警声在凌晨三点把你从睡梦里拽起来。最后分享一个自己踩坑后养成的习惯凡是把可空字段改成NOT NULL我必然会先跑三句SQL——查NULL总量、查空字符串总量、查默认值与唯一索引的冲突概率。这三句SQL花不了两分钟但能让我躲开绝大多数1138相关的坑。NULL不是不能处理关键是动手之前先想清楚业务上“没有值”到底应该表达成NULL还是空值。把约束变严不是目的数据语义清晰才是目的。
MySQL报错1138：Invalid use of NULL的排查与解决
先说说我遇到这个数据库报错时的场景。某天凌晨刚躺下手机连着震了三次群里刷出来一串红色告警“ALTER TABLE执行失败错误码1138 – Invalid use of NULL value”。这个报错对干数据库这行的人来说不陌生但真到自己值班时任何一个报错都够让人瞬间清醒。当时那条SQL其实很简单就是把一张业务表里的邮箱字段从允许NULL改成NOT NULL结果执行到一半直接被MySQL拦了下来。表结构没改成功线上业务倒是没受影响但排好的变更窗口全被打乱后面连着两个需求都得重新排期。其实1138这个错误反复出现的场景特别集中你不是第一次见到它也不会是最后一次。开发环境自测、上线前结构变更、数据迁移后的字段加固、ORM框架在代码里默认把空值写成NULL这些场合都容易撞上。只要表里已经存在NULL值而你又想把字段收严成NOT NULLMySQL就会毫不客气地甩出这句Invalid use of NULL value。这篇文章就把这个报错拆开揉碎讲清楚它是什么、怎么排查、有哪些解法、生产环境执行时要注意什么。无论是刚入门的新人还是被线上问题折腾的DBA都能直接照着操作。1. 先搞清楚这个报错到底在说什么1.1 一个真实场景的复盘先还原一下我上面提到的场景。当时的业务需求是用户注册后必须填写邮箱历史数据里却有大量用户没有邮箱所以结构上一直允许NULL。这次要做结构变更SQL长这样ALTER TABLE users MODIFY COLUMN email VARCHAR(255) NOT NULL;执行后MySQL直接报错ERROR 1138 (22004): Invalid use of NULL value我当时第一反应是看表里有多少NULL。果然问题不在SQL语法而在数据本身。执行SELECT COUNT(*) FROM users WHERE email IS NULL;返回了三千多条。也就是说历史数据里存着几千个“没填邮箱”的用户而这条ALTER语句要求“每一行都必须有邮箱”数据库当然不答应。这个报错的逻辑其实特别简单NULL在SQL里代表“这个值不存在/未知”NOT NULL代表“这个字段必须有值”两句话是直接矛盾的。你用一条DDL强制要求“所有行都必须有值”但表里明明躺着一堆“没有值”的行MySQL就会在执行阶段发现数据违反约束然后停止操作并回滚。整个链路用一句话概括表里有NULL你却想加NOT NULL数据库拒绝执行。1.2 1138背后的约束检查机制很多人以为这个错误是语法问题其实不是。SQL语句在MySQL里要经过解析、优化、执行三个阶段语法错误在解析阶段就会被抓出来但1138不是在解析阶段抛的而是在执行阶段才被发现。具体来说MySQL在修改表结构时会逐行检查已有数据是否满足新的约束。尤其是MODIFY COLUMN这类操作它会读取目标列的所有数据逐一比对“是不是存在NULL”。一旦发现任何一个NULL立刻中断执行扔出错误码1138。这也是为什么很多时候ALTER TABLE执行到一半才报错因为检查过程已经开始了只是碰到第一个违规数据就停下来。这里还有个容易忽略的点sql_mode会影响报错的行为。在MySQL的严格模式下STRICT_TRANS_TABLES或STRICT_ALL_TABLES只要出现非法NULL值语句会直接失败并完整回滚。但如果关闭了严格模式某些DML操作对NULL的容忍度会变高比如INSERT时往NOT NULL字段塞NULL会被静默转成该类型的默认值数字转0字符串转空串并产生一条warning而不是直接报错。可ALTER TABLE不太一样它属于结构变更即便非严格模式下也不会“自动把NULL转默认值”依然会甩出1138。这个差异我后面会专门讲很多人就是在这里踩的坑。1.3 最容易触发1138的三种操作我统计了一下自己处理过的工单触发这个报错的场景基本集中在以下三种ALTER TABLE把可空字段改成NOT NULL这是头号场景比如给字段加非空约束、调整字段属性时顺手加个NOT NULL。常见语句是MODIFY COLUMN或CHANGE COLUMN。UPDATE语句显式把NULL赋给NOT NULL字段这种情况多见于代码里没有做空值判断ORM框架传参时把空字符串转成了NULL。例如UPDATE users SET email NULL WHERE id 1;INSERT语句插入NULL或漏写字段显式写NULL是最直白的违规漏写字段的隐蔽性更高因为插入操作会为没写的字段填入“隐式默认值”在MySQL里如果没有显式DEFAULT这个隐式值很可能就是NULL。同样报1138。此外还有一种间接场景从外部系统导入数据CSV里空单元格被读成了NULL正好又要同步改表结构两件事撞在一起就成了1138。理解了触发链条接下来的排查思路才清晰先找到哪个字段、哪些数据是NULL再决定怎么补。2. 接到报错之后别急着补数据2.1 把报错现场完整记录下来很多人一看到1138第一反应就是“那把NULL改成空字符串不就行了”。这话方向没错但直接上手改之前有几件事必须做。别嫌啰嗦线上出过太多因为“手快”搞出来的事故。先记录报错现场具体的SQL语句、执行时间、执行客户端、是否在事务里、事务里还有没有其他操作。这些信息看起来不重要但决定你的恢复路径。比如如果报错发生在事务里并且事务已经半途中断回滚后的状态和事务前的状态可能不一样如果是在存储过程里报错整段过程是否回滚还要看异常处理逻辑。把这些信息留在手边后面分析才能有据可依。还要看一眼当时的锁状态和会话情况SELECT * FROM information_schema.innodb_trx\G如果线上刚好有长事务在跑你的ALTER TABLE可能在排队等锁。这时候不要反复重试先看看锁等待情况避免把CPU和IO白白烧在无谓的尝试上。2.2 一步步定位到具体字段和数据如果报错语句已经告诉你涉及哪张表哪个字段定位很快。但有些场景下报错信息不够具体尤其是通过中间件或ORM转发过来的SQL可能被包装成了一长串调用链。这时可以从两个角度入手。第一个角度查所有可空字段。用information_schema可以快速得到一个表里所有允许NULL的字段清单SELECT COLUMN_NAME, IS_NULLABLE, COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_database AND TABLE_NAME your_table AND IS_NULLABLE YES;第二个角度直接核对NULL数据的分布。定位到目标字段后统计NULL数量和占比SELECT COUNT(*) AS total_rows, SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS null_rows FROM users;注意这两条SQL在大表上执行可能很慢。尤其第二条如果表有上千万行直接全表扫会带来不小的IO开销。可以先用EXPLAIN看一下执行计划或者利用统计信息估算结果集大小挑选业务低峰期执行。2.3 动手前先回答三个问题排查完现场和数据分布急着上修复方案之前先回答三个问题。这三个问题的答案直接决定选哪个方案。第一个问题业务上这些NULL到底代表什么意义是“用户没有填过邮箱”还是“历史数据由于bug没写入”还是“导入时丢了数据”不同语义对应的补值方式完全不同。如果是前者补成空字符串是合理的如果是后者可能需要找回源数据不能用空字符串糊弄过去。第二个问题涉及的数据量有多大几千条可以直接UPDATE几百万条就要考虑分批甚至用在线DDL工具否则一条大事务下去主从延迟和锁等待会把你折腾到怀疑人生。第三个问题当前是不是业务高峰能不能容忍锁表ALTER TABLE在InnoDB下虽然支持在线DDL但MODIFY COLUMN能否使用瞬时算法取决于字段类型和长度是否变化以及MySQL版本。如果不能接受执行期间对表的写入影响就必须另选方案比如先加新列再迁移或者用工具平滑变更。这三个问题想清楚了再进入修复环节。盲目补数据是最忌讳的因为NULL在很多业务里是有含义的一律把它“洗”成空字符串后续统计、查询、报表全都可能对不上账。3. 四种解决方案按数据量选型3.1 小表直接UPDATE加ALTER数据量不大几千到几万行的情况下最直接的做法就是“先补值再改结构”。整个过程可以拆成四步每一步都加一条验证SQL确保不会带病执行。第一步备份。不管表大小结构变更前一定要有可回退的备份。可以用mysqldump单独导这张表mysqldump -u root -p --single-transaction your_database users users_backup.sql第二步把NULL补成业务认可的默认值。比如空字符串UPDATE users SET email WHERE email IS NULL;第三步确认没有漏网之鱼SELECT COUNT(*) FROM users WHERE email IS NULL;如果返回0才可以继续。第四步执行结构变更ALTER TABLE users MODIFY COLUMN email VARCHAR(255) NOT NULL DEFAULT ;这里有一个非常关键的认知MySQL不会自动用默认值去填充表里已经存在的NULL。你在MODIFY COLUMN语句里写了DEFAULT 只影响以后插入数据时的行为ALTER执行过程中遇到已有NULL照样报1138。所以必须先UPDATE再ALTER顺序不能反。3.2 大表分批清洗避免长事务如果表里有几十万甚至几百万个NULL一把梭UPDATE users SET email WHERE email IS NULL会引发一个大事务。大事务的问题不仅是锁时间长更麻烦的是会产生大量undo日志导致从库要回放很久主从延迟瞬间飙高。我曾经见过一次全表更新几十万行主从延迟直接拉到了十分钟以上报警系统被刷爆。正确的做法是按主键范围分批更新。假设users表主键是idUPDATE users SET email WHERE email IS NULL AND id BETWEEN 1 AND 5000; UPDATE users SET email WHERE email IS NULL AND id BETWEEN 5001 AND 10000;每批更新完停顿一小会让从库跟上再跑下一批。批大小按实际IO能力调整经验值一般在5000到20000行之间。如果主键不是连续分布的可以改成按主键排序后分页更新或者借助SELECT id ... LIMIT n先取出一批主键再用WHERE id IN (...)更新。分批的好处不只是控制锁范围还能让你随时中断。假如跑到一半发现业务数据有问题停下来分析情况再决定是否继续而不是把所有操作绑在一个不可分割的大事务里。分批更新完成后同样执行ALTER TABLE。要注意的是即便前面数据已经清洗干净ALTER本身在大表上也可能耗时较长。可以执行前先看会话级别的超时设置避免客户端说断就断SET SESSION innodb_lock_wait_timeout 50;3.3 不想改业务数据先加列再迁移有些场景下原字段的NULL值不能被覆盖比如历史数据需要保留原始状态。这时候可以直接换一种思路不修改原字段而是新增一个非空字段迁移完数据后换列名。操作步骤大致如下-- 第一步新增可空字段带默认值 ALTER TABLE users ADD COLUMN email_new VARCHAR(255) NOT NULL DEFAULT ; -- 第二步用COALESCE把原字段的NULL统一转成默认值 UPDATE users SET email_new COALESCE(email, ); -- 第三步校验新字段没有NULL SELECT COUNT(*) FROM users WHERE email_new IS NULL; -- 第四步删掉旧字段把新字段改名 ALTER TABLE users DROP COLUMN email, RENAME COLUMN email_new TO email;这个方案的好处是原字段数据从头到尾没有被修改每一步都有回退余地。比如第二步执行完发现数据不对可以直接ALTER TABLE users DROP COLUMN email_new;原字段还是原样。代价是表里会有个短暂的过渡字段写入时两个字段都要维护建议在业务低峰期操作。3.4 上千万元素的表直接用平滑变更工具数据量大到单条ALTER会在线上卡很久时就得借助专门的在线DDL工具了。我常用的是Percona Toolkit里的pt-online-schema-change原理是先创建一个结构一样的新表在旧表上加触发器记录增量变更然后分批把数据拷贝到新表最后通过原子性的RENAME TABLE切换新旧表。整个过程对线上写入的影响被控制在很小范围内。基本用法是这样pt-online-schema-change Dyour_database,tusers \ --alter MODIFY COLUMN email VARCHAR(255) NOT NULL DEFAULT \ --no-check-replication-filters \ --execute执行前要确保三个前提表有主键用于分批拷贝、没有触发器和外键冲突或者明确处理、磁盘空间足够存放新表副本。这个工具生成的触发器会占用少量性能所以同样建议低峰期跑。另一个选择是gh-ost它不依赖触发器而是通过解析binlog来同步增量数据对主库的压力更小但部署和配置成本略高。选择工具前先确认你对复制环境有足够了解否则工具本身可能成为新的故障点。4. 生产环境执行时最容易翻车的几个细节4.1 备份永远放在最前面这句话听起来像废话但每次线上变更总有人跳过去。我处理过的最惨烈一次案例是同事执行ALTER之前没有备份结果默认值选错了导致全表几百万条记录被改写。虽然最后靠binlog捞回来但整整花了三个小时期间业务一直被影响。备份不是“导出一份SQL文件”就行了。如果表在1GB以上mysqldump导出再导入回滚成本很高。物理备份工具XtraBackup更合适它会直接拷贝数据文件恢复速度比逻辑备份快一个量级。不管你用哪种都要在测试环境先演练一遍恢复流程。备份文件能正常导回库才是真安全。另外如果线上有主从架构结构变更前可以先在从库上执行一遍观察有无报错、耗时多长、从库延迟多少。确认没问题再轮到主库。灰度思想不能只用在业务代码里数据库变更同样适用。4.2 sql_mode带来的“静默成功”陷阱前面提过严格模式关闭时DML语句对NULL的处理会变宽容。举例来说INSERT INTO users(email) VALUES (NULL);如果字段是NOT NULL非严格模式下MySQL不会报错而是插入空字符串并给出一条warning。但ALTER TABLE是另一个逻辑。你很可能因此在测试环境得出结论“表里有NULL也没关系可以改成NOT NULL”因为你的测试连接关闭了严格模式上了生产生产连接开着严格模式1138就冒出来了。这种“测试好了、生产报错”的现象非常坑。所以动结构之前先统一确认两端环境的sql_modeSELECT sql_mode;尽量保证测试、预发、生产的sql_mode完全一致否则你踩到的只是环境差异不是真正的业务问题。4.3 唯一索引和默认值之间的“隐藏雷”这是一个特别容易被忽略的细节。NULL在MySQL的索引里有个特殊待遇多个NULL可以同时存在不受唯一索引约束。也就是说email字段即使有唯一索引两条email为NULL的记录也可以共存。但如果你把所有NULL都统一补成空字符串情况就变了。空字符串之间是相等的唯一索引会直接拦截第二次写入ERROR 1062 (23000): Duplicate entry for key uk_email更常见的是业务查询里拿IS NULL做判断补值之后条件全要改。所以补默认值前先确认这个字段有没有唯一索引如果有提前查一下补成默认值后会撞出多少个重复项SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) 1;如果确实有重复冲突可以考虑用带业务语义的唯一值来补比如邮箱不存在时写成空字符串加用户ID保证唯一。不过这种补偿值比较难看最好还是回到业务层面讨论NULL到底该怎么定义。4.4 从库延迟和锁等待的双重压力大表ALTER的过程中DDL在从库一样要执行而且从库通常是单线程回放延迟比主库更明显。如果在业务高峰执行主库的写入压力加DDL拷贝压力很可能把从库拖到“Seconds_Behind_Master”持续上涨。另外一个隐性风险是ALTER TABLE在执行过程中会持有元数据锁MDL锁如果此时有另一个查询长时间占用相关表DDL可能在“Waiting for table metadata lock”状态等上很久而它自己也会反过来阻塞后面的所有查询。这种情况在业务高峰期尤其常见。建议执行前用SHOW PROCESSLIST;看一眼有没有长时间运行的查询确认没有大事务占着表。如果实在躲不开高峰就用pt-osc这类工具把锁影响降到最低。5. 常见问题速查和我的经验清单5.1 问题速查表我把这些年遇到的典型情况整理成一个速查表方便各位在值班时快速对照。注意每一条的处理结果都要在执行后用查询验证别以“我感觉应该好了”收尾。报错/现象可能原因快速处理ALTER TABLE报1138目标表存在NULL值查NULL清洗后重试UPDATE赋NULL报1138字段有NOT NULL约束检查ORM代码改用COALESCEINSERT空值报1138显式NULL或漏写字段修改插入语句或表约束修改后唯一索引冲突补充的默认值重复用唯一值或临时策略去重大表ALTER引发从库延迟单条大事务分批或使用pt-osc5.2 一个最小复现实验为了方便你本地验证我留一个最小化复现脚本。MySQL 5.7和8.0都适用。-- 建表允许email为空 CREATE TABLE test_1138 ( id INT PRIMARY KEY, email VARCHAR(100) NULL ); -- 插入一条NULL数据 INSERT INTO test_1138 VALUES (1, NULL), (2, ab.com); -- 这里会报ERROR 1138 ALTER TABLE test_1138 MODIFY email VARCHAR(100) NOT NULL; -- 修复先补数据再改结构 UPDATE test_1138 SET email WHERE email IS NULL; ALTER TABLE test_1138 MODIFY email VARCHAR(100) NOT NULL DEFAULT ;这个实验能让你快速理解报错的行为特征不是语法错了是已有数据不满足新约束。剩下的就是你自己的业务数据该怎么处理的问题。5.3 治理NULL值的长期建议一次1138报错处理完不算完。后面更值得做的是从源头减少这类问题发生。我个人的做法是建立一套结构变更前的自检清单内容不复杂但真的管用新表字段能加NOT NULL就加不能加的必须写明理由。所有结构变更SQL在测试环境跑通并核对测试环境sql_mode和生产一致。上线变更前用脚本检查目标表NULL分布和唯一索引冲突风险。ORM实体类的字段校验和数据库约束要对齐别让代码里的“空字符串”传到数据库变成NULL。数据导入管线里加一道清洗任务把空单元格明确映射为NULL或默认值不让脏数据流入核心表。这套清单执行下来1138出现的频率会大幅下降。就算偶尔出现也能在几分钟内定位问题而不是让告警声在凌晨三点把你从睡梦里拽起来。最后分享一个自己踩坑后养成的习惯凡是把可空字段改成NOT NULL我必然会先跑三句SQL——查NULL总量、查空字符串总量、查默认值与唯一索引的冲突概率。这三句SQL花不了两分钟但能让我躲开绝大多数1138相关的坑。NULL不是不能处理关键是动手之前先想清楚业务上“没有值”到底应该表达成NULL还是空值。把约束变严不是目的数据语义清晰才是目的。
