ALTER TABLE 用于修改已有表的结构,包括重命名表、添加或删除字段、修改字段类型、管理索引与约束等。结构变更可能锁表、复制大量数据或影响现有程序,生产环境执行前必须备份并评估影响。
一、准备示例表
本文使用商品表演示常见操作:
CREATE TABLE products (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
stock INT NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;
执行修改前,先确认当前表结构:
SHOW CREATE TABLE products\G DESCRIBE products;
二、修改表名
ALTER TABLE products RENAME TO shop_products;
也可以使用 RENAME TABLE:
RENAME TABLE shop_products TO products;
修改表名后,应同步检查应用 SQL、视图、存储程序和运维脚本中的表名引用。

三、添加字段
1. 在表末尾添加字段
ALTER TABLE products ADD COLUMN status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态:1启用,0停用';
2. 指定字段位置
-- 添加到第一列 ALTER TABLE products ADD COLUMN product_code VARCHAR(32) NOT NULL FIRST; -- 添加到 name 字段后 ALTER TABLE products ADD COLUMN category_id BIGINT UNSIGNED NULL AFTER name;
FIRST 和 AFTER 主要影响查看顺序,业务查询仍应明确写出字段名。调整物理顺序可能增加 DDL 成本,没有必要时可省略。

四、修改字段数据类型与属性
MODIFY COLUMN 修改字段定义,但不修改字段名:
ALTER TABLE products MODIFY COLUMN name VARCHAR(200) NOT NULL COMMENT '商品名称';
使用 MODIFY 或 CHANGE 时,应重新写出需要保留的数据类型和全部属性。遗漏 NOT NULL、DEFAULT、COMMENT 等属性可能改变原字段定义。
缩短字符串长度、降低数值范围或把可空字段改为 NOT NULL 前,应先检查现有数据是否兼容:
SELECT MAX(CHAR_LENGTH(name)) AS max_name_length FROM products; SELECT COUNT(*) AS null_count FROM products WHERE name IS NULL;
五、修改字段名
1. 仅修改名称
MySQL 8.0 推荐使用 RENAME COLUMN,不需要重复字段定义:
ALTER TABLE products RENAME COLUMN stock TO inventory;
2. 同时修改名称和定义
CHANGE COLUMN 可以同时修改字段名与完整定义:
ALTER TABLE products CHANGE COLUMN inventory stock_count INT UNSIGNED NOT NULL DEFAULT 0;
重命名后还要检查视图、触发器、存储程序以及应用代码;这些对象中的引用不一定会被自动更新。
六、设置与删除默认值
-- 设置默认值 ALTER TABLE products ALTER COLUMN price SET DEFAULT 0.00; -- 删除默认值 ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
七、删除字段
ALTER TABLE products DROP COLUMN category_id;
删除字段会永久丢失该列数据,不能通过事务 ROLLBACK 恢复。执行前应确认备份有效,并检查索引、约束和程序依赖。

八、调整字段位置
MODIFY COLUMN 配合 FIRST 或 AFTER 可以调整顺序:
ALTER TABLE products MODIFY COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP FIRST; ALTER TABLE products MODIFY COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER stock_count;
必须完整写出字段定义,避免无意改变默认值或是否允许 NULL。
九、添加与删除索引
1. 普通索引
ALTER TABLE products ADD INDEX idx_products_name (name); ALTER TABLE products DROP INDEX idx_products_name;
2. 唯一索引
ALTER TABLE products ADD UNIQUE INDEX uk_products_code (product_code);
添加唯一索引前,应先检查重复值:
SELECT product_code, COUNT(*) AS duplicate_count FROM products GROUP BY product_code HAVING COUNT(*) > 1;
3. 复合索引
ALTER TABLE products ADD INDEX idx_products_status_price (status, price);
复合索引字段顺序应根据实际 WHERE、JOIN 和 ORDER BY 条件设计,而不是简单把常用字段全部加入索引。
十、添加与删除约束
1. CHECK 约束
ALTER TABLE products ADD CONSTRAINT chk_products_price CHECK (price >= 0), ADD CONSTRAINT chk_products_stock CHECK (stock_count >= 0); ALTER TABLE products DROP CHECK chk_products_stock;
2. 外键约束
ALTER TABLE products ADD CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES categories (id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE products DROP FOREIGN KEY fk_products_category;
添加外键前,要确认两端字段类型一致、被引用字段有索引,并清理不满足约束的历史数据。
十一、一次执行多项修改
MySQL 允许在一条 ALTER TABLE 中使用多个子句:
ALTER TABLE products ADD COLUMN updated_at DATETIME NULL, ADD INDEX idx_products_created_at (created_at), MODIFY COLUMN price DECIMAL(12, 2) NOT NULL DEFAULT 0.00;
合并操作可能减少重复重建表的成本,但生产环境仍需逐项确认兼容性;复杂变更应优先保证可审查和可回退。
十二、Online DDL:ALGORITHM 与 LOCK
InnoDB 的部分结构变更支持在线 DDL。可明确指定算法和锁级别,让不符合预期的操作直接失败,而不是悄悄使用代价更高的方式:
ALTER TABLE products ADD COLUMN remark VARCHAR(255) NULL, ALGORITHM = INSTANT;
ALTER TABLE products ADD INDEX idx_products_price (price), ALGORITHM = INPLACE, LOCK = NONE;
INSTANT:只修改元数据,通常最快,但仅部分操作和版本支持。INPLACE:尽量不复制整张表,但仍可能消耗较多资源。COPY:复制并重建表,耗时和临时空间需求通常最大。LOCK=NONE:请求变更期间允许并发读写;并非所有操作都支持。
即使是 Online DDL,在开始或结束阶段也可能短暂获取排他元数据锁。长事务可能阻塞 ALTER TABLE,因此大表操作必须选择维护窗口并持续监控。
十三、生产环境执行清单
- 确认 MySQL 小版本和表的存储引擎。
- 使用 SHOW CREATE TABLE 保存完整表结构。
- 检查数据是否符合新类型、唯一索引和约束。
- 确认可恢复的数据库备份,而不仅是导出建表语句。
- 评估表大小、临时磁盘空间、执行时间和锁表影响。
- 先在测试环境用相近数据量验证。
- 选择业务低峰执行,并监控元数据锁和复制延迟。
- 执行后验证表结构、行数、关键查询及应用功能。
查看最终结构:
SHOW CREATE TABLE products\G SHOW INDEX FROM products;
完整语法与各操作支持的算法,可参考 MySQL 8.0 ALTER TABLE 官方文档和 InnoDB Online DDL 官方文档。