MySQL ALTER TABLE 修改表結構完整指南：添加/刪除字段、索引、主鍵與重命名實戰

核心語法

ALTER TABLE table_name action [, action ...];

單條語句可組合多個操作，用逗號分隔，效率優於多條語句。

常用操作類型

實戰演示

初始建表

CREATE TABLE myTable (ID SMALLINT);

DESC myTable;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | smallint(6) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

組合操作：添加字段 + 修改類型 + 設置主鍵

ALTER TABLE myTable
    ADD COLUMN Quantity SMALLINT UNSIGNED NOT NULL,
    MODIFY ID SMALLINT UNSIGNED NOT NULL,
    ADD PRIMARY KEY (ID);

結果驗證

DESC myTable;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| ID       | smallint(5) unsigned | NO   | PRI |         |       |
| Quantity | smallint(5) unsigned | NO   |     |         |       |
+----------+----------------------+------+-----+---------+-------+

刪除操作：移除字段與主鍵

ALTER TABLE myTable
    DROP COLUMN Quantity,
    DROP PRIMARY KEY;

最終結構

DESC myTable;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| ID    | smallint(5) unsigned | NO   |     |         |       |
+-------+----------------------+------+-----+---------+-------+

性能與鎖表關鍵知識

MySQL 5.6+ Online DDL

• INPLACE 算法：不鎖表或僅短暫鎖表，支持：添加/刪除二級索引、修改字段默認值、擴展 VARCHAR 長度等
• COPY 算法：全表複製重建，獨佔鎖，操作：修改字段類型、添加主鍵、修改字符集等

指定算法

ALTER TABLE t ADD INDEX idx_c (c), ALGORITHM=INPLACE, LOCK=NONE;

大表修改最佳實踐

1. 業務低峰期執行
2. 使用 pt-online-schema-change (Percona Toolkit) 實現真正無鎖變更
3. 分步驟：創建新表 → 同步數據 → 重命名切換
4. 備份先行，驗證回滾方案

常見陷阱

• 外鍵約束：修改被引用字段需先刪除外鍵
• 主鍵類型變更：需先 DROP PRIMARY KEY，再 MODIFY，再 ADD PRIMARY KEY
• ENUM/SET 修改：新值必須包含舊值，否則數據截斷
• 字符集轉換：CONVERT TO 會重寫數據，DEFAULT CHARSET 僅影響新字段

總結

ALTER TABLE 是 DDL 核心命令，掌握組合操作、算法選擇、鎖表影響與大表變更策略，是 DBA 與後端開發必備技能。