MySQL / 系统课时
安全新增与删除列
安全新增与删除列
本课以 MySQL 8.4 与 InnoDB 为基线,判断普通列的新增和删除能否走 ALGORITHM=INSTANT,并处理默认值、列位置、行版本上限与旧版本差异。目标是让不满足条件的变更立即失败,而不是意外退化为昂贵重建。
学习目标
- 判断普通加列、删列是否满足 INSTANT 条件,并显式要求算法
- 正确处理默认值、列位置与 MySQL 8.0 不同小版本的能力差异
- 在删列前完成应用兼容检查,并用行版本指标识别长期治理风险
用显式 INSTANT 做失败保护
ALGORITHM=INSTANT 只修改数据字典元数据,不扫描和重写整表,因此执行时间基本不随表行数增长。生产环境应显式指定它:如果当前表结构或版本不支持,语句会直接报错,避免服务器自动选择 INPLACE 或 COPY 后才发现变更成本远超预期。
INSTANT 操作只允许默认锁策略,不要同时写 LOCK=NONE。以下示例在 status 后新增一个带默认值的普通列;MySQL 8.4 支持在任意位置进行 INSTANT 加列。
CREATE DATABASE IF NOT EXISTS ddl_demo;
USE ddl_demo;
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
status VARCHAR(16) NOT NULL,
amount DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;
INSERT INTO orders VALUES
(1, 'NEW', 99.00),
(2, 'PAID', 120.00);
ALTER TABLE orders
ADD COLUMN risk_level TINYINT NOT NULL DEFAULT 0 AFTER status,
ALGORITHM=INSTANT;
SELECT id, status, risk_level, amount
FROM orders
ORDER BY id;预期结果
+----+--------+------------+--------+
| id | status | risk_level | amount |
+----+--------+------------+--------+
| 1 | NEW | 0 | 99.00 |
| 2 | PAID | 0 | 120.00 |
+----+--------+------------+--------+旧行会按新增列定义读取默认值,不需要先把整表回填一遍。后续修改默认值和回填历史业务数据是两件事,不能把默认值变更当成历史数据修复。
版本与表结构决定是否真能 INSTANT
MySQL 8.0 的能力不是从一开始就完全相同。8.0.12 起支持 INSTANT 加列,但在 8.0.29 之前,INSTANT 新列只能放在表尾;从 8.0.29 起才支持任意位置加列,并加入 INSTANT 删列。MySQL 8.4 对支持的加列、删列会默认优先使用 INSTANT,但治理脚本仍建议显式写出算法。
普通列也并非始终可用 INSTANT。常见阻断条件包括 ROW_FORMAT=COMPRESSED、存在 FULLTEXT 索引、数据字典表空间中的表以及临时表;添加 AUTO_INCREMENT 列也不是普通的瞬时加列。删列时,如果目标列参与某些索引或其他受限结构,也可能无法直接 INSTANT。

关注行版本而不是只看执行耗时
从 MySQL 8.0.29 开始,INSTANT 加列或删列会创建新的行版本。MySQL 8.4 的单表行版本上限是 64;频繁做瞬时加删列,最终会因为达到上限而拒绝新的 INSTANT 变更。可以在变更后把 TOTAL_ROW_VERSIONS 纳入结构治理检查。
SELECT NAME, TOTAL_ROW_VERSIONS
FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE NAME = 'ddl_demo/orders';预期结果
+-----------------+--------------------+
| NAME | TOTAL_ROW_VERSIONS |
+-----------------+--------------------+
| ddl_demo/orders | <VALUE> |
+-----------------+--------------------+<VALUE> 取决于这张表历史上执行过多少次 INSTANT 加列或删列。一次 ALTER TABLE 即使同时加减多个列,也对应一次新的行版本;表重建类操作会重新整理物理记录并重置该计数。行版本接近上限时,不应继续把 INSTANT 当作无限资源,而应计划一次受控重建。
删列要先解决应用依赖
INSTANT 删列虽然快,但它不是可逆的“隐藏列”。一旦删掉,应用再读取或写入该列会立即报错,而且重新添加同名列也不能自动恢复旧值。因此安全删列应先完成代码与数据依赖治理,再执行 DDL。
建议采用两阶段发布:先发布不再读写目标列的应用版本,观察一个完整业务周期并检查 SQL、报表、ETL、触发器和下游同步;确认无依赖后再删列。下面删除刚才的 risk_level。
ALTER TABLE orders
DROP COLUMN risk_level,
ALGORITHM=INSTANT;
SHOW COLUMNS FROM orders;预期结果
Field Type Null Key Default Extra
id bigint NO PRI NULL
status varchar(16) NO NULL
amount decimal(10,2) NO NULL如果显式 ALGORITHM=INSTANT 失败,不要为了“让脚本跑过去”立刻改成默认算法。先确认失败原因、预计重建时间、临时空间、复制延迟和元数据锁风险,再决定是否安排 INPLACE 或 COPY 窗口。对大表而言,失败得早往往比自动降级后长时间占用资源更安全。