MySQL 8.0 DDL 数据表结构变更踩坑

因为业务需要，进行数据库表结构变更是在所难免的。日常有些数据量较大的表，几百万到几千万行，也没有特别大，仅供参考。常见操作增加列，增加索引等。

一、增加字段，允许为NULL

这种一般是“轻量级”的 ALTER，“只添加 NULL 列”的操作在某些存储引擎（如 InnoDB）上是快速的、在线的。

满足以下条件，效率是很高的（瞬间完成）

表使用的是 InnoDB
字段是 NULL（不是 NOT NULL 且没有默认值）
没有触发器、分区、全文索引等复杂结构
使用的是 MySQL 8.0+

在这种情况下，MySQL 使用“即时 DDL”技术，不需要复制整张表，也不会锁表。

ALTER TABLE `tb_111` AND COLUMN `col` VARCHAR(20) NULL;

注意：

不要设置默认值，先设置为NULL，再按需更新

ALTER TABLE `tb_111` AND COLUMN `col` VARCHAR(20) DEFAULT "" NULL;

为什么这种 ALTER 会触发表重写，这类 ALTER 被称为 “不即时（non-instant）”操作：

添加了一个有默认值（DEFAULT ""）的字段
MySQL 必须在物理磁盘上为每一行写入这列值
即使你写了 NULL DEFAULT ""，也会视为要初始化这个字段

Python Django 生成 migration时，会产生两条SQL语句，注意合并去掉默认值后再执行

-- 第一步：添加字段时设置 DEFAULT 0
ALTER TABLE `tb_111` ADD COLUMN `col` VARCHAR(20) DEFAULT "" NULL;

-- 第二步：又取消默认值，改成 DEFAULT NULL
ALTER TABLE `tb_111` ADD COLUMN `col` VARCHAR(20) SET DEFAULT NULL;

二、原字段不能为NULL，改为允许为NULL

理论上讲，在 MySQL 8.0 (InnoDB) 里，对字段做 NOT NULL → NULL 这样的修改，一般是元数据变更（instant alter），不需要重建整张表。执行几乎是秒级完成（只更新表结构元信息）。

但是，有个表600万行数据，试了一下，没有立即执行完成，就取消了。

可以尝试指定算法

ALTER TABLE tb_2222 
MODIFY COLUMN your_column_name VARCHAR(10) NULL,
ALGORITHM=INSTANT;

如果报错 ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported for this operation. 就说明这个操作不是立即完成的，需要谨慎操作

原因分析：

MySQL 8.0 的 instant DDL 只支持部分元数据级别的变更，例如：

增加一个 NULL 的新列（带默认值或无默认值）
修改 DEFAULT 值
重命名列
…

但是 NULL/NOT NULL 属性的修改，因为涉及到存储格式和数据一致性校验，必须重写表。

三、给某个字段增加索引

ALTER TABLE your_table_name ADD INDEX idx_created_time (created_time);

InnoDB执行过程

扫描 created_time 字段
写入一棵新的 B+Tree 索引
更新数据字典

支持的算法方式 INPLACE 和 COPY

ALGORITHM=INPLACE（默认常见）

不需要复制整张表数据
大部分情况下可以在线添加索引（但会阻塞写入和更新）
读操作通常还能并发执行

ALGORITHM=COPY

会复制整个表，创建一张临时表，写入新索引，最后替换原表
耗时长，磁盘占用大，期间 DML（INSERT/UPDATE/DELETE）会被锁住

操作风险点

表很大（千万级、上亿行） → 建索引时间长，锁表风险高
磁盘空间不足 → 临时表、索引构建时需要额外空间（可能接近表大小的 1 倍）
业务高峰期 → 可能阻塞写入，导致业务卡顿

四、主键为字符串，增加一个 int_id，并且自增

场景：字符串 uuid作为主键，想单独增加一个用于排序或筛选的自增ID

ALTER TABLE your_table_name
ADD COLUMN id_auto BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD UNIQUE KEY (id_auto);

会填加字段 id_auto，表里的数据，会自动并从1开始计数，自动填充好。

注意事项	说明
AUTO_INCREMENT 只能有一个	你不能有两个 AUTO_INCREMENT 字段
必须有索引	否则会报错：Incorrect table definition; there can be only one auto column and it must be defined as a key
不建议与已有主键冲突	不要用 AUTO_INCREMENT 替换 UUID 主键，除非你准备彻底迁移主键
插入历史数据不会自动补全 AUTO_INCREMENT	它从“当前最大 + 1”开始递增，但老数据该字段会是 0 或报错（需要填充）

在SQL后面增加 ALGORITHM=INSTANT 会报错：

SQL Error [1846] [0A000]: ALGORITHM=INSTANT is not supported. Reason: Adding an auto-increment column requires a lock. Try ALGORITHM=COPY/INPLACE.

这个操作需要锁表，而且复杂，表里的数据不多还好。这个操作需要锁表，如果数据量大，肯定锁表时间过长。

总结

ALGORITHM 这个选项在 MySQL 5.6 就引入了，但能力有限。到 MySQL 8.0 才逐渐增强，特别是 INSTANT 算法是 8.0.12 之后才支持的。

最快的：INSTANT → 元数据级操作（新增列 DEFAULT NULL、改列名、改默认值等），秒完成，适合大表。
中等：INPLACE → 不复制表数据，但会扫描全表（加索引、改列 NULL/NOT NULL），通常还能在线执行，但可能拖很久。
最慢：COPY → 复制整张表（加主键、改字符集等），大表非常危险。

拿不准时可以指定 ALGORITHM=INSTANT，如果报错说明需要锁表或复制表，参考数据量谨慎操作，尤其不要在业务高峰期操作。

操作	COPY	INPLACE	INSTANT	说明
新增列（带默认值）	✅	✅	❌	如果有默认值需要写回现有数据，不能用 INSTANT
新增列（无默认值 / DEFAULT NULL）	✅	✅	✅	8.0.12+ 支持，几乎秒完成
删除列	✅	✅	✅	8.0.29+ 开始支持 INSTANT
修改列类型（如 VARCHAR(100) → VARCHAR(200))	✅	✅	❌	大部分情况需要重新检查/重建数据
修改列允许 NULL / NOT NULL	✅	✅	❌	需要扫描数据，验证是否符合约束
修改列默认值	✅	✅	✅	只改元数据
重命名列	✅	✅	✅	只改元数据
增加主键	✅	❌	❌	必须重建整张表
删除主键	✅	❌	❌	必须重建整张表
增加二级索引 (ADD INDEX)	✅	✅	❌	会扫描全表并构建索引树
删除二级索引 (DROP INDEX)	✅	✅	❌	需要重建索引树
重命名表	✅	✅	✅	元数据操作，秒完成
更改表字符集	✅	❌	❌	需要重写数据

一、增加字段，允许为NULL​

二、原字段不能为NULL，改为允许为NULL​

三、给某个字段增加索引​

四、主键为字符串，增加一个 int_id，并且自增​

总结​

一、增加字段，允许为NULL

二、原字段不能为NULL，改为允许为NULL

三、给某个字段增加索引

四、主键为字符串，增加一个 int_id，并且自增

总结