MySQL 大表字段变更实践（改名 + 改类型 + 改长度）一、核心原理（必读）为什么不能直接 ALTER TABLE

目标：安全地修改字段名、数据类型或长度（如 old_type VARCHAR(50) → new_type VARCHAR(100) NOT NULL）
适用场景：生产环境大表（百万行以上）、不能停机、MySQL 5.6/5.7/8.0

一、核心原理（必读）

为什么不能直接 `ALTER TABLE`？

在 MySQL（尤其是 InnoDB 引擎）中，不是所有 ALTER TABLE 操作都能“在线”完成。很多操作会触发 “表重建”（Table Rebuild），即：

创建一张新结构的临时表
逐行拷贝原表所有数据到新表
删除原表
将临时表重命名为原表名

这个过程在早期 MySQL 中是完全锁表的（Copy Table），即使后来有了 Online DDL，仍有大量操作不支持“无锁”。

不同 MySQL 版本的行为对比

1. MySQL 5.6 及更早版本

几乎所有 DDL 都会锁表（包括改字段名、改长度、加索引等）
拷贝期间：
- 写操作完全阻塞
- 读操作可能被阻塞（取决于存储引擎）
大表 = 长时间不可用

💥 示例：
一张 5000 万行的表，执行 ALTER TABLE t MODIFY name VARCHAR(100)，可能锁表 2 小时以上！

2. MySQL 5.7

引入了 In-Place DDL，部分操作可在线进行（如扩展 VARCHAR 长度、添加索引）
但以下操作仍会重建表并锁写：
- 修改字段名（CHANGE COLUMN）
- 缩小字段长度（如 VARCHAR(100) → VARCHAR(50)）
- 改变数据类型（如 INT → BIGINT，CHAR → VARCHAR）
- 修改字符集或排序规则

⚠️ 即使你只是“改个名字”，MySQL 5.7 也会全表重建！

-- 显式要求 Online DDL，utf8mb4 字符集63是分界线要注意，不可用的时候有提示不影响数据库使用，建议使用pt-osc
ALTER TABLE users
MODIFY COLUMN email VARCHAR(63) NOT NULL,
ALGORITHM = INPLACE,
LOCK = NONE;

3. MySQL 8.0.4+（重大改进）

引入 Instant DDL，以下操作毫秒级完成，不触碰数据：
- 添加/删除列（末尾）
- 重命名列（RENAME COLUMN）
- 修改列默认值
但以下操作仍需重建表：
- 改变数据类型（如 INT → VARCHAR）
- 缩小字段长度
- 在非末尾位置加列

✅ 所以：只有 MySQL 8.0.4+ 且仅改名时，才能安全直接 ALTER

安全方案：`pt-online-schema-change` 原理

创建影子表 _原表名_new，并应用新结构（含新字段名/类型/长度）
分批拷贝历史数据（按主键分块，避免高 I/O）
通过触发器同步增量变更（INSERT/UPDATE/DELETE 自动同步到新表）
原子切换：RENAME TABLE 瞬间完成，业务无感
自动清理中间对象

💡 优点：支持任意 DDL 变更，兼容 MySQL 5.6+
⚠️ 要求：表必须有主键或唯一非空索引

二、实用操作步骤

步骤 1：确认前提

-- 检查 MySQL 版本
SELECT VERSION();

-- 检查表结构和主键
SHOW CREATE TABLE your_table;

✅ 必须满足：有主键，用户有 SUPER + TRIGGER 权限

步骤 2：备份

mysqldump -h your_host -u backup_user -p \
  --single-transaction your_db your_table > backup_$(date +%Y%m%d).sql

步骤 3：安装工具（如未安装）

www.percona.com/downloads

# Mac
brew install percona-toolkit

步骤 4：构造 ALTER 语句（关键！）

场景示例：

原字段	目标字段	ALTER 语句
`type VARCHAR(50)`	`resource_type VARCHAR(100) NOT NULL`	`CHANGE COLUMN type resource_type VARCHAR(100) NOT NULL`
`status TINYINT`	`order_status INT DEFAULT 0`	`CHANGE COLUMN status order_status INT DEFAULT 0`
`name CHAR(20)`	`full_name VARCHAR(50) NOT NULL`	`CHANGE COLUMN name full_name VARCHAR(50) NOT NULL`

🔔 注意：

CHANGE COLUMN 同时支持改名 + 改类型
必须写出完整的新定义（类型、长度、是否为空、默认值）

步骤 5：执行变更（核心命令）

pt-online-schema-change \
  --host=your_db_host \
  --user=dba_user \
  --password='your_password' \
  --alter "CHANGE COLUMN old_col new_col VARCHAR(100) NOT NULL DEFAULT ''" \
  D=your_db,t=your_table \
  --execute \
  --max-load="Threads_running=25" \
  --critical-load="Threads_running=50" \
  --chunk-size=1000 \
  --sleep=0.1 \
  --progress=time,30

🔑 参数说明：

--max-load：负载高时暂停（防雪崩）
--chunk-size：每次拷贝 1000 行（根据 I/O 调整）
--sleep：每批间隔 0.1 秒（降低压力）
--progress：每 30 秒输出进度

--alter 参数只需要写 ALTER TABLE 之后的部分，不能包含 ALTER TABLE 表名！

步骤 6：验证结果

-- 检查结构
SHOW CREATE TABLE your_table;

-- 抽样查询（验证数据和默认值）
SELECT id, new_col FROM your_table WHERE id IN (1, 100000, 1000000);

-- 检查空值（如改为 NOT NULL）
SELECT COUNT(*) FROM your_table WHERE new_col IS NULL;

MySQL 大表字段变更实践（改名 + 改类型 + 改长度）