MySQL DDL 實戰：ALTER TABLE 修改表結構八大操作全解析

ALTER TABLE 修改表結構完整指南：不鎖表、不遷數據的線上變更技巧

修改表定義比重建表簡單，不影響現有服務。MySQL 通過 ALTER TABLE 支援 8 類核心操作。

1. 修改表名

ALTER TABLE 舊表名 RENAME [TO] 新表名;
-- TO 可選，不影響執行

演示：

CREATE TABLE `user` (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(5), card_id VARCHAR(20), age INT DEFAULT 25);
ALTER TABLE `user` RENAME `usr`;  -- 成功，結構數據無損

2. 修改欄位資料類型

ALTER TABLE 表名 MODIFY 欄位名 新資料類型;

演示：ALTER TABLE `user` MODIFY name VARCHAR(10) NOT NULL; 將長度 5 → 10。

3. 修改欄位名

ALTER TABLE 表名 CHANGE 舊欄位名 新欄位名 新資料類型;
-- 僅改名時類型保持原樣

兩種情況：

4. 新增欄位

ALTER TABLE 表名 ADD 欄位名 資料類型 [約束] [FIRST | AFTER 既有欄位名];

四種定位方式：

5. 刪除欄位

ALTER TABLE 表名 DROP 欄位名;

演示：ALTER TABLE `user` DROP name; 成功移除。

6. 調整欄位順序

ALTER TABLE 表名 MODIFY 欄位名 資料類型 FIRST | AFTER 既有欄位名;

演示：

• ALTER TABLE `user` MODIFY name VARCHAR(5) FIRST; → 移到首位
• ALTER TABLE `user` MODIFY name VARCHAR(5) AFTER age; → 移到 age 後

工具生成 SQL 對比：客戶端工具常用 CHANGE COLUMN ... AFTER ... 實現同樣效果。

7. 更改儲存引擎

ALTER TABLE 表名 ENGINE = 新引擎名;

演示：ALTER TABLE `user` ENGINE = MyISAM; InnoDB → MyISAM 成功。

⚠️ 警告：大表變更引擎會全表重建、長時間鎖表、IO 飆升，生產環境嚴禁直接執行，請用 pt-online-schema-change 或藍綠部署遷移。

8. 刪除外鍵約束

ALTER TABLE 子表名 DROP FOREIGN KEY 外鍵別名;

演示：

CREATE TABLE `address` (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES `user`(id) ON DELETE CASCADE
);
ALTER TABLE `address` DROP FOREIGN KEY user_id;  -- 成功移除

MODIFY vs CHANGE 選擇口訣

• 只改類型 → MODIFY
• 改名（含同步改類型） → CHANGE
• 調整順序 → MODIFY ... FIRST/AFTER 或 CHANGE ... FIRST/AFTER

線上變更最佳實踐

1. 變更前備份、驗證回滾方案
2. 大表必須用 pt-online-schema-change / gh-ost 無鎖變更
3. 避開業務高峰，分批次、小步驟執行
4. 監控 Threads_running、Innodb_row_lock_waits、磁碟 IO、Replication Lag
5. 變更後驗證數據完整性、索引有效性、應用功能