MySQL列属性从NULL改为NOT NULL DEFAULT的深坑及解决方案

引言

在 MySQL 数据库运维和表结构变更场景中，调整字段属性几乎是高频操作。其中一种非常常见的需求，就是把原本允许 NULL 的列修改为 NOT NULL，并同时设置默认值。

ALTER TABLE slowtech.t1 MODIFY name VARCHAR(10) NOT NULL DEFAULT 'slowtech';

这条 DDL 语句看起来很简单，但在生产环境中却很容易埋下隐患，甚至直接引发线上故障。本文将深入分析 MySQL 字段从 NULL 改为 NOT NULL DEFAULT 时的典型陷阱，并给出更安全、可落地的操作方案。

1. 问题现象

1.1 业务突然报错

某一天，业务系统突然出现如下报错：

ERROR 1048 (23000): Column 'name' cannot be null

但奇怪的是，业务执行的 SQL 明明是：

INSERT INTO slowtech.t1(id) VALUES(1);  -- 没有插入name字段！

按正常理解，这条 SQL 本不应该失败，因为：

• 原表t1中的name列本来是允许 NULL 的
• 语句里没有指定name字段，理论上应该自动写入 NULL

1.2 现场排查

进一步检查表结构：

mysql> DESC slowtech.t1;+-------+-------------+------+-----+----------+-------+| Field | Type        | Null | Key | Default  | Extra |+-------+-------------+------+-----+----------+-------+| id    | int(11)     | NO   | PRI | NULL     |       || name  | varchar(10) | NO   |     | slowtech |       |  -- 已改为NOT NULL+-------+-------------+------+-----+----------+-------+

问题很快就找到了：不久前，DBA 刚执行过一次 DDL，将name列从允许 NULL 修改成了 NOT NULL DEFAULT。

2. 深入分析：为什么会出现这个错误？

2.1 PT-OSC的实现原理

根因往往不在 SQL 本身，而在 DDL 工具的执行机制上。以pt-online-schema-change为例，它的大致实现流程如下：

2.2 问题重现

-- 1. 原始表结构（允许NULL）CREATE TABLE slowtech.t1(    id INT PRIMARY KEY,    name VARCHAR(10));-- 2. PT-OSC创建新表并修改结构CREATE TABLE slowtech._t1_new(    id INT PRIMARY KEY,    name VARCHAR(10) NOT NULL DEFAULT 'slowtech'  -- 已修改);-- 3. 创建触发器CREATE TRIGGER slowtech.`pt_osc_slowtech_t1_ins` AFTER INSERT ON `slowtech`.`t1` FOR EACH ROW REPLACE INTO `slowtech`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`);-- 4. 业务插入数据（只插入id）INSERT INTO slowtech.t1(id) VALUES(1);-- 报错：ERROR 1048 (23000): Column 'name' cannot be null

2.3 为什么会报错？

关键点：触发器会把业务 SQL 和同步到新表的操作放在同一个事务里执行。虽然业务 SQL 对原表来说并不违规，但触发器写入新表时，NEW.name仍然是 NULL，从而违反了新表上的 NOT NULL 约束，最终导致整个语句失败。

3. 数据拷贝阶段的另一个坑

3.1 拷贝过程中的报错

除了增量写入阶段，如果原表里本身已经存在 NULL 值，那么 PT-OSC 在全量拷贝数据时同样会报错：

-- 原表存在NULL值mysql> INSERT INTO slowtech.t1(id) VALUES(1);mysql> SELECT * FROM t1;+----+------+| id | name |+----+------+|  1 | NULL |+----+------+-- 执行PT-OSCpt-online-schema-change h=localhost,D=slowtech,t=t1   --alter "MODIFY name VARCHAR(10) NOT NULL DEFAULT 'slowtech'"   --execute-- 报错：-- Error copying rows from `slowtech`.`t1` to `slowtech`.`_t1_new`: -- Column 'name' cannot be null

3.2 使用–null-to-not-null参数的隐患

-- 添加参数可以忽略1048错误pt-online-schema-change ... --null-to-not-null --execute-- 实际执行的是：INSERT LOW_PRIORITY IGNORE INTO `slowtech`.`_t1_new` (`id`, `name`) SELECT `id`, `name` FROM `slowtech`.`t1` LOCK IN SHARE MODE;-- 查看结果mysql> SELECT * FROM _t1_new;+----+------+| id | name |+----+------+|  1 |      |  -- NULL被转换为空字符串！+----+------+

⚠️ 重要警告：--null-to-not-null参数并不是真正按业务语义把 NULL 安全替换为你想要的默认值，它可能会把 NULL 转成：

• 字符类型：空字符串 ''
• 数字类型：0
• 日期类型：'0000-00-00'

这些值是否符合业务逻辑，必须提前验证清楚，否则很容易造成数据语义错误。

4. 正确的实施步骤

4.1 总体流程

4.2 详细实施步骤

步骤1：先改为NULL DEFAULT

-- 先允许NULL，但设置默认值ALTER TABLE slowtech.t1 MODIFY name VARCHAR(10) NULL DEFAULT 'slowtech';

作用：先保留字段可为 NULL 的能力，但给它补上默认值。这样后续新增数据在未显式指定该列时，就不会再产生新的 NULL。

步骤2：处理现有NULL值

-- 查看现有NULL值数量SELECT COUNT(*) FROM slowtech.t1 WHERE name IS NULL;-- 分批更新NULL值为默认值（避免锁大表）-- 每次更新1000条UPDATE slowtech.t1 SET name = DEFAULT WHERE name IS NULL LIMIT 1000;-- 确认没有NULL值了SELECT COUNT(*) FROM slowtech.t1 WHERE name IS NULL;

注意事项：

• 如果是大表，一定要采用分批更新，避免长事务和大锁
• 尽量选择业务低峰期执行，降低对线上请求的影响
• 持续监控主从延迟、数据库负载以及慢 SQL 情况

步骤3：最终改为NOT NULL DEFAULT

-- 确认没有NULL值后，执行最终修改ALTER TABLE slowtech.t1 MODIFY name VARCHAR(10) NOT NULL DEFAULT 'slowtech';

4.3 使用PT-OSC的安全方案

如果线上环境必须依赖 PT-OSC 来执行表结构变更，推荐按照下面的安全流程分步处理：

# 第一步：先改为NULL DEFAULTpt-online-schema-change h=localhost,D=slowtech,t=t1   --alter "MODIFY name VARCHAR(10) NULL DEFAULT 'slowtech'"   --execute# 第二步：手动更新NULL值（同上）mysql -e "UPDATE slowtech.t1 SET name = DEFAULT WHERE name IS NULL LIMIT 1000"# 第三步：改为NOT NULL DEFAULTpt-online-schema-change h=localhost,D=slowtech,t=t1   --alter "MODIFY name VARCHAR(10) NOT NULL DEFAULT 'slowtech'"   --execute

5. 理解NOT NULL DEFAULT的真正含义

很多 MySQL 开发者或 DBA 对NOT NULL DEFAULT的语义理解并不准确，这也是线上踩坑的重要原因之一：

-- 创建表CREATE TABLE t1 (    id INT PRIMARY KEY,    name VARCHAR(10) NOT NULL DEFAULT 'slowtech');

5.1 误区一：认为DEFAULT会覆盖NULL

-- ❌ 错误理解：认为会自动将NULL转为默认值INSERT INTO t1 VALUES (1, NULL);  -- 实际结果：ERROR 1048 (23000): Column 'name' cannot be null

也就是说，DEFAULT只会在“未指定该列”时生效，而不是在“显式插入 NULL”时替你兜底。

5.2 误区二：认为NOT NULL和DEFAULT是一体的

-- ✅ 正确理解：这是两个独立的部分CREATE TABLE t2 (    name1 VARCHAR(10) NOT NULL DEFAULT 'a',  -- NOT NULL + DEFAULT    name2 VARCHAR(10) NULL DEFAULT 'b',      -- NULL + DEFAULT    name3 VARCHAR(10) NOT NULL,               -- NOT NULL + 无DEFAULT    name4 VARCHAR(10) NULL                    -- NULL + 无DEFAULT);

NOT NULL和DEFAULT本质上是两个独立约束：一个控制是否允许存储 NULL，另一个控制未传值时的默认填充值。

5.3 各组合的实际效果

6. PT-OSC使用注意事项

6.1 异常退出后的清理顺序

如果 PT-OSC 执行过程中异常退出，可能会残留触发器和中间表。此时清理顺序一定不能弄错：

-- ❌ 错误顺序：先删中间表DROP TABLE slowtech._t1_new;-- 此时业务执行DML会报错INSERT INTO slowtech.t1 VALUES (1, 'victor');-- ERROR 1146 (42S02): Table 'slowtech._t1_new' doesn't exist-- ✅ 正确顺序：先删触发器DROP TRIGGER slowtech.`pt_osc_slowtech_t1_ins`;-- 再删中间表DROP TABLE slowtech._t1_new;

原因很简单：只要触发器还在，业务对原表的 DML 就会继续尝试同步到中间表；如果中间表先被删掉，就会直接导致线上写入报错。

6.2 关键参数说明

7. 最佳实践总结

7.1 安全三步曲

7.2 操作检查清单

• 确认业务是否能够接受短时间内字段仍然存在 NULL
• 提前评估历史 NULL 数据量，并制定分批修复方案
• 尽量安排在业务低峰期执行表结构变更
• 实时监控数据库负载、主从复制延迟和异常告警
• 提前准备完整的回滚预案和应急处理方案

7.3 一句话总结

把字段从 NULL 调整为 NOT NULL DEFAULT，真正困难的从来不是那条 ALTER TABLE 语句，而是如何先安全消除历史数据中的 NULL，再从源头上避免后续增量数据继续写入 NULL。

只有真正理解这一点，才能避免 MySQL 表结构变更中的常见坑位，更稳妥地完成这类看似简单、实则风险不小的字段修改操作。