MySQL ALTER TABLE 全方位操作指南 (5.6+ Online DDL)
所有操作在 MySQL 5.6+ 支援 Online DDL，大表僅在準備/提交階段短暫元數據鎖，不阻塞 DML。操作前務必 DESC table 確認現狀、評估鎖影響、低峰執行。
1. 表重命名
ALTER TABLE old_name RENAME TO new_name;
-- 或
RENAME TABLE old_name TO new_name; -- 支援多表原子重命名
2. 欄位操作
| 操作 | 語法 | 關鍵點 |
|---|
| 新增欄位 | ALTER TABLE t ADD col INT NOT NULL DEFAULT 0; | 指定位置 FIRST / AFTER col2；8.0.12+ Instant ADD COLUMN 無鎖無拷貝 |
| 刪除欄位 | ALTER TABLE t DROP COLUMN col; | 不可刪除被索引/外鍵引用的欄位，需先刪索引/外鍵 |
| 修改欄位名+型別 | ALTER TABLE t CHANGE old_col new_col VARCHAR(100) NOT NULL; | 型別不變也需完整寫出 |
| 僅修改型別/屬性 | ALTER TABLE t MODIFY col VARCHAR(100) NOT NULL DEFAULT ''; | 不支援重命名 |
| 調整欄位順序 | ALTER TABLE t MODIFY col INT FIRST; / AFTER other_col | 純元數據操作，極快 |
3. 索引與約束管理
| 操作 | 語法 | 備註 |
|---|
| 新增普通索引 | ALTER TABLE t ADD INDEX idx_name (col1, col2); | Online DDL，無鎖掃描建立 |
| 新增唯一索引 | ALTER TABLE t ADD UNIQUE uk_email (email); | 資料有重複會報錯，需先清理 |
| 新增主鍵 | ALTER TABLE t ADD PRIMARY KEY (id); | 涉及聚簇索引重建，大表耗時長 |
| 刪除索引 | ALTER TABLE t DROP INDEX idx_name; | 主鍵用 DROP PRIMARY KEY |
| 新增外鍵 | ALTER TABLE child ADD CONSTRAINT fk_pid FOREIGN KEY (pid) REFERENCES parent(id); | 高併發慎用，建議應用層維護 |
| 刪除外鍵 | ALTER TABLE child DROP FOREIGN KEY fk_pid; | 需知約束名，SHOW CREATE TABLE 查看 |
4. 表級屬性變更
- 變更儲存引擎：
ALTER TABLE t ENGINE=InnoDB; (大表重建，謹慎)。
- 修改字元集：
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; (轉換資料，重建表)。
- 修改自增值：
ALTER TABLE t AUTO_INCREMENT = 1000;。
- 表壓縮：
ALTER TABLE t COMPRESSION='zlib'; (InnoDB 透明頁壓縮)。
5. Online DDL 鎖影響對照 (InnoDB)
| 操作類型 | 演算法 | 鎖模式 | 備註 |
|---|
| 新增/刪除二級索引 | INPLACE | NONE | 極快，僅掃描索引頁 |
| 新增/刪除欄位 (Instant) | INSTANT | NONE | 8.0.12+，純元數據 |
| 修改欄位型別/長度 | INPLACE | 共享鎖 (準備/提交) | 大表需重建數據頁 |
| 新增/刪除主鍵 | COPY | 排他鎖 (準備/提交) | 聚簇索引重建，最慢 |
| CONVERT TO CHARSET | COPY | 排他鎖 | 全表重建轉換資料 |
| RENAME TABLE | INPLACE | 元數據鎖 (極短) | 首選重命名方式 |
6. 實戰建議
- 大表變更**先在測試環境驗證耗時/鎖影響**。
- 使用
pt-online-schema-change (Percona Toolkit) 實現真正無鎖變更 (觸發器同步)。
- 變更前
SHOW CREATE TABLE 保存結構，變更後對比驗證。
- 監控
INNODB_METRICS / performance_schema.events_statements_current 觀察進度。
- 避免業務高峰期，預留足夠磁碟空間 (重建需 1.5~2 倍表大小)。
