修改表结构

导学

业务在演进，表结构也需要随之调整。但修改生产环境的大表是一场"高风险手术"——锁表、复制延迟、空间膨胀都可能发生。本节学习 ALTER TABLE 的各种操作及其风险。

定义

ALTER TABLE：DDL 语句，用于修改已有表的结构，包括增删列、修改列类型、增删索引、修改表属性等。

常见 ALTER 操作

添加列

当前数据状态：先创建一张演示表

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

SELECT * FROM employees;

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

操作语句：

-- 在 score 列之后添加 bonus 列
ALTER TABLE employees ADD bonus DECIMAL(10, 2) AFTER score;

DESC employees;

操作后数据状态：

Field	Type	Null	Key	Default	Extra
emp_id	int(11)	NO	PRI	NULL	auto_increment
emp_name	varchar(20)	YES		NULL
dept	varchar(20)	YES		NULL
score	decimal(5,2)	YES		NULL
bonus	decimal(10,2)	YES		NULL

SELECT * FROM employees;

emp_id	emp_name	dept	score	bonus
1	大翔	技术部	100	NULL
2	白歌	技术部	NULL	NULL

结果解读：新添加的列默认值为 NULL，已有数据自动填充默认值。MySQL 5.7 中，添加列通常是Online DDL操作（不阻塞 DML），但如果指定 AFTER/FIRST 或添加 VARCHAR 列且长度超过 255，可能需要重建表。

修改列

当前数据状态：bonus 列现在是 DECIMAL(10, 2)。

操作语句：

-- 修改 bonus 列，添加 NOT NULL 约束和默认值
ALTER TABLE employees MODIFY bonus DECIMAL(10, 2) NOT NULL DEFAULT 0.00;

SELECT * FROM employees;

操作后数据状态：

emp_id	emp_name	dept	score	bonus
1	大翔	技术部	100	0.00
2	白歌	技术部	NULL	0.00

结果解读：MODIFY 修改列的类型和属性。由于原 bonus 为 NULL，MySQL 在设为 NOT NULL 时自动将 NULL 转为默认值 0.00。如果缩小类型范围（如 DECIMAL(10,2) 改 DECIMAL(5,2)），可能导致数据截断或报错。

修改列名

当前数据状态：bonus 列已存在且有默认值。

操作语句：

-- 将 bonus 改名为 annual_bonus（MySQL 特有 CHANGE 语法）
ALTER TABLE employees CHANGE bonus annual_bonus DECIMAL(10, 2) NOT NULL DEFAULT 0.00;

DESC employees;

操作后数据状态：

Field	Type	Null	Key	Default	Extra
annual_bonus	decimal(10,2)	NO		0.00

结果解读：CHANGE 可以修改列名、类型和属性，需要写两次列名（旧名和新名）。MODIFY 只能修改类型和属性，不能改名。

删除列

当前数据状态：employees 表有 annual_bonus 列。

操作语句：

-- 删除 annual_bonus 列
ALTER TABLE employees DROP COLUMN annual_bonus;

DESC employees;

操作后数据状态：annual_bonus 列已从表结构中移除。

SELECT * FROM employees;

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

结果解读：删除列会立即释放该列占用的磁盘空间，InnoDB 会重建表。此操作在大表上可能耗时较长，且数据不可恢复。

添加索引

当前数据状态：employees 表只有主键索引。

操作语句：

-- 方式一：ALTER TABLE 添加索引
ALTER TABLE employees ADD INDEX idx_dept (dept);

-- 方式二：CREATE INDEX（效果相同）
CREATE INDEX idx_emp_name ON employees(emp_name);

SHOW INDEX FROM employees;

操作后数据状态（节选）：

Table	Non_unique	Key_name	Column_name
employees	0	PRIMARY	emp_id
employees	1	idx_dept	dept
employees	1	idx_emp_name	emp_name

结果解读：添加索引后，按 dept 或 emp_name 查询会使用索引加速。MySQL 5.7 中，ALTER TABLE ADD INDEX 通常是 Online DDL，可以通过 ALGORITHM=INPLACE, LOCK=NONE 实现不锁表。

删除索引

当前数据状态：employees 表有 idx_emp_name 索引。

操作语句：

-- 方式一：ALTER TABLE 删除索引
ALTER TABLE employees DROP INDEX idx_emp_name;

-- 方式二：DROP INDEX（效果相同）
DROP INDEX idx_dept ON employees;

SHOW INDEX FROM employees;

操作后数据状态：只剩 PRIMARY 索引。

结果解读：删除索引后，按 dept 或 emp_name 查询将退化为全表扫描，但写入操作（INSERT/UPDATE/DELETE）会变快。

修改表属性

当前数据状态：employees 表使用默认字符集和存储引擎。

操作语句：

-- 修改存储引擎
ALTER TABLE employees ENGINE=InnoDB;

-- 修改字符集
ALTER TABLE employees CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改表注释
ALTER TABLE employees COMMENT='员工信息表 V2';

SHOW CREATE TABLE employees;

操作后数据状态（节选）：

CREATE TABLE `employees` (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(20) DEFAULT NULL,
  `dept` varchar(20) DEFAULT NULL,
  `score` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工信息表 V2'

结果解读：修改存储引擎或字符集会重建整张表，大表上执行需谨慎。

Online DDL 机制

MySQL 5.7 大幅增强了 Online DDL 能力，允许部分 ALTER 操作在不阻塞 DML 的情况下执行。

显式指定 Online DDL

当前数据状态：重新创建演示表

CREATE TABLE online_test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    val VARCHAR(50)
);

INSERT INTO online_test (val) VALUES ('A'), ('B'), ('C');

操作语句：

-- 显式指定 Online DDL 参数
ALTER TABLE online_test ADD COLUMN new_col INT,
ALGORITHM=INPLACE, LOCK=NONE;

DESC online_test;

操作后数据状态：

Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	NULL	auto_increment
val	varchar(50)	YES		NULL
new_col	int(11)	YES		NULL

LOCK 级别	含义
`NONE`	不锁表，允许读写
`SHARED`	允许读，阻塞写
`EXCLUSIVE`	阻塞读写

⚠️ 注意：如果指定了 LOCK=NONE 但不支持，MySQL 会报错而不是降级。应根据实际情况选择。

大表 ALTER 的最佳实践

方案一：使用 pt-online-schema-change（推荐）

Percona Toolkit 提供的工具，通过创建新表、同步增量数据、rename 切换的方式实现"无锁"改表：

pt-online-schema-change \
    --alter "ADD COLUMN age TINYINT" \
    D=library,t=employees \
    --execute

方案二：低峰期执行 + 监控

-- 先估算时间
ALTER TABLE employees ADD COLUMN test_col INT;

配合 SHOW PROCESSLIST 和复制延迟监控，确保不影响业务。

常见误区

误区	正解
`ALTER` 都不锁表	MySQL 5.7 部分支持 Online DDL，但重建表的操作仍会锁表或产生大量 I/O。
`ALTER` 可以回滚	DDL 隐式提交，不可回滚。
修改列类型总是安全的	缩窄类型可能导致数据截断（如 `VARCHAR(100)` 改 `VARCHAR(10)`），大改小可能溢出。

面试考点

Q：MySQL 5.7 的 Online DDL 是什么？

Online DDL 允许部分 ALTER TABLE 操作在不阻塞 DML（或仅短暂阻塞）的情况下执行。通过 ALGORITHM=INPLACE 和 LOCK=NONE 控制。

Q：如何安全地给大表添加列？

使用 pt-online-schema-change 工具；2. 在低峰期执行并监控；3. 先在测试环境验证执行时间和影响。

Q：MODIFY 和 CHANGE 的区别？

MODIFY 修改列的类型/属性，不能改名；CHANGE 可以修改列名、类型和属性。CHANGE 语法更通用但需要写两次列名。

小结

ALTER TABLE 用于修改表结构，包括列和索引的增删改
MySQL 5.7 支持 Online DDL，但并非所有操作都不锁表
大表结构变更应使用 pt-online-schema-change 等工具
DDL 隐式提交，执行前务必确认备份

下一章引子：表结构定义好了，但查询速度取决于另一个关键因素——索引。