MySQL修改含NULL值的INT列为NOT NULL默认10报错原因及解决

Hey there, let's break this down and fix it step by step.

为什么会出现这个报错？ #

当你尝试执行ALTER TABLE contact MODIFY i INT NOT NULL DEFAULT 10;时，MySQL抛出"invalid use of null value"错误的核心原因很直接：你的contact表的i列目前已经存在若干NULL值。

你想要给这列加上NOT NULL约束，但MySQL不会自动帮你把已有的NULL值替换成你设置的默认值10——它要求你先处理掉这些现存的NULL，才能成功应用NOT NULL约束。

正确的修改流程 #

你需要分两步操作，先清理现有NULL值，再修改列属性：

1. 先将所有NULL值更新为默认值10
   执行这条语句把列里的NULL都替换成10：

   UPDATE contact SET i = 10 WHERE i IS NULL;
   

   如果你的表数据量很大，可以考虑分批更新或者用事务来保证数据安全，避免中途出错导致部分数据修改。

2. 再修改列的约束和默认值
   现在列里已经没有NULL值了，执行你原来的ALTER语句就不会报错了：

   ALTER TABLE contact MODIFY i INT NOT NULL DEFAULT 10;
   

可选：用事务保证操作原子性 #

如果担心中途操作出错，可以用事务包裹这两步，这样一旦有问题就能回滚到修改前的状态：

START TRANSACTION;
UPDATE contact SET i = 10 WHERE i IS NULL;
ALTER TABLE contact MODIFY i INT NOT NULL DEFAULT 10;
COMMIT;

如果执行过程中出现错误，执行ROLLBACK;就能撤销所有修改。

内容的提问来源于stack exchange，提问作者Gaurav