MySQL ALTER TABLE 完全指南：字段增刪改、索引維護、重命名與類型變更實戰語法

引言

ALTER TABLE 是 MySQL 結構變更的核心命令，涵蓋字段、索引、約束、表名、存儲引擎等全方位操作。本文彙總高頻實戰語法模式，說明關鍵參數、鎖行為、性能影響及避坑要點，助力線上 DDL 安全高效執行。

一、字段操作

1.1 新增字段

-- 末尾追加
ALTER TABLE t2 ADD COLUMN d TIMESTAMP;

-- 指定位置（FIRST / AFTER col_name）
ALTER TABLE infos ADD COLUMN ex TINYINT NOT NULL DEFAULT 0 AFTER id;

-- 同時新增主鍵自增列（建表遺漏補救）
ALTER TABLE tabelname 
  ADD COLUMN new_field_id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
  ADD PRIMARY KEY (new_field_id);

1.2 刪除字段

ALTER TABLE t2 DROP COLUMN c;
-- 等同
ALTER TABLE t2 DROP c;

> ⚠️ 刪除列會重建表（拷貝算法），大表導致長時間鎖表。5.6+ 支持 ALGORITHM=INPLACE 但仍需重建聚簇索引。

1.3 修改字段名稱

-- CHANGE 可同時改名、改類型、改屬性（需完整聲明新定義）
ALTER TABLE t1 CHANGE COLUMN a b INT;

-- 僅改名（類型屬性不變）仍需重複聲明
ALTER TABLE t1 CHANGE COLUMN a b INT NOT NULL DEFAULT 0;

1.4 修改字段類型/屬性

-- 修改類型、增加 NOT NULL、調整 DEFAULT
ALTER TABLE t1 CHANGE COLUMN b b BIGINT NOT NULL;
ALTER TABLE infos CHANGE COLUMN list list TINYINT NOT NULL DEFAULT 0;

-- MODIFY 僅改類型/屬性，不改名（語法更簡潔）
ALTER TABLE t1 MODIFY COLUMN b BIGINT NOT NULL;

> ⚠️ 類型收窄（如 BIGINT→INT、VARCHAR(255)→VARCHAR(50)）可能截斷數據，**務必先驗證數據範圍**。

二、索引操作

2.1 新增索引

-- 普通索引
ALTER TABLE tablename ADD INDEX idx_name (col1, col2);

-- 唯一索引
ALTER TABLE tablename ADD UNIQUE INDEX uk_card (cardnumber);

-- 主鍵（隱含 NOT NULL + UNIQUE）
ALTER TABLE tablename ADD PRIMARY KEY (id);

2.2 刪除索引

ALTER TABLE tablename DROP INDEX idx_name;
-- 主鍵刪除
ALTER TABLE tablename DROP PRIMARY KEY;

三、表級操作

3.1 重命名表

ALTER TABLE t1 RENAME TO t2;
-- 等同
RENAME TABLE t1 TO t2;

3.2 修改表選項

-- 變更存儲引擎
ALTER TABLE t1 ENGINE = InnoDB;

-- 變更字符集
ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 僅修改默認字符集（不轉換現有列）
ALTER TABLE t1 DEFAULT CHARACTER SET utf8mb4;

四、MySQL 5.6+ Online DDL 關鍵參數

操作類型	ALGORITHM	LOCK	說明
新增/刪除二級索引	INPLACE	NONE	無鎖、不重建表，僅掃描構建索引
修改列類型/屬性	INPLACE	SHARED/EXCLUSIVE	需重建表，短暫共享/排他鎖
新增主鍵/自增列	INPLACE	EXCLUSIVE	重建聚簇索引，全程排他鎖
變更字符集	COPY	EXCLUSIVE	全表重建，長時間鎖表
刪除列	COPY	EXCLUSIVE	全表重建

最佳實踐：顯式指定 ALGORITHM=INPLACE, LOCK=NONE，若不支持會報錯回滾，避免意外長鎖：

ALTER TABLE big_table ADD INDEX idx_new (col), ALGORITHM=INPLACE, LOCK=NONE;

五、pt-online-schema-change：大表零停機變更利器

Percona Toolkit pt-online-schema-change 通過「創建影子表 → 異步同步數據 → 切換表名」實現**無鎖 DDL**：

pt-online-schema-change \
  --alter "ADD INDEX idx_name (col)" \
  D=db,t=big_table \
  --execute --max-load "Threads_running=50" --critical-load "Threads_running=100"

• 自動控制同步速率，避免主庫過載
• 支持觸發器同步增量變更
• 切換瞬間僅需極短元數據鎖

六、常見坑點速查

1. 忘記 DEFAULT 導致隱式 NULL：ADD COLUMN c INT 默認允許 NULL，建議顯式 NOT NULL DEFAULT 0
2. 外鍵約束阻礙 DDL：需先 DROP FOREIGN KEY 再操作，完成後重建
3. 字符集轉換數據截斷：latin1 → utf8mb4 可能導致長度超限，預先 MODIFY COLUMN c VARCHAR(191)
4. 自增值重置風險：TRUNCATE 或 DROP + CREATE 會重置 AUTO_INCREMENT，ALTER TABLE 保留
5. 元數據鎖 (MDL) 爭用**：長事務未提交時執行 DDL 會阻塞所有後續訪問，排查 performance_schema.metadata_locks

七、總結

ALTER TABLE 雖語法簡單，但線上執行涉及鎖模式、算法選擇、數據兼容、工具輔助等多維考量。掌握 CHANGE/MODIFY 差異、索引 Online DDL 參數、pt-osc 大表零停機模式，配合**變更前驗證、變更中監控、變更後校驗**全流程管控，方能安全高效演進數據庫結構。