您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL ALTER TABLE 變更表結構：基礎語法、線上變更優化策略、修改 .frm 文件極速方案

編輯：MySQL 綜合教學

前言：線上 ALTER TABLE 是運維高危動作，必須謀定而後動

MySQL 執行 ALTER TABLE 時，**預設會複製整張表 (Copy-Table 算法)**：建新表 → 拷貝數據 → 換名 → 刪舊表。百萬行以上可能耗時數小時、鎖表阻塞寫入、佔用雙倍磁碟。本文覆蓋 **基礎語法、Online DDL (5.6+) 優化、Instant ADD COLUMN (8.0+)、修改 .frm 文件繞過拷貝** 四大層面，助你實現「秒級變更、業務無感」。

一、基礎 ALTER TABLE 語法速查

操作	語法	示例
增加列	`ALTER TABLE tbl ADD col_name col_type [FIRST\|AFTER col]`	`ALTER TABLE pet ADD weight INT AFTER age;`
刪除列	`ALTER TABLE tbl DROP col_name`	`ALTER TABLE pet DROP weight;`
修改列屬性 (類型/預設/註釋)	`ALTER TABLE tbl MODIFY col_name new_type`	`ALTER TABLE pet MODIFY weight VARCHAR(30);`
修改列名 + 屬性	`ALTER TABLE tbl CHANGE old_name new_name new_type`	`ALTER TABLE pet CHANGE weight wei VARCHAR(30);`
僅修改列名	`ALTER TABLE tbl CHANGE old_name new_name col_type`	`ALTER TABLE pet CHANGE weight wei INT;`
重命名表	`ALTER TABLE tbl RENAME new_tbl`	`ALTER TABLE pet RENAME animal;`
修改預設值 (5.6+ 推薦)	`ALTER TABLE tbl ALTER col_name SET DEFAULT 'val'`	`ALTER TABLE user ALTER pwd SET DEFAULT '666666';`

二、MODIFY vs ALTER COLUMN：性能天壤之別

-- 慢：MODIFY COLUMN 會觸發全表拷貝、重建索引
ALTER TABLE user MODIFY COLUMN pwd VARCHAR(20) NOT NULL DEFAULT '666666';
-- Handler_write 110002 行，耗時 3 秒

-- 快：ALTER COLUMN 僅更新 .frm 元數據，**零拷貝、零鎖表、毫秒級**
ALTER TABLE user ALTER COLUMN pwd SET DEFAULT '666666';
-- Handler_write 0 行，耗時 0.03 秒

原理：預設值存儲在 .frm (表結構文件) 中，ALTER COLUMN ... SET DEFAULT 僅修改元數據；MODIFY COLUMN 會重寫數據文件 (.ibd/.MYD)。

三、可通過「只改 .frm」實現的極速變更 (風險自負)

適用場景：僅修改元數據、不涉及數據重組：

修改列預設值
增加/刪除 AUTO_INCREMENT 屬性
增加/刪除/修改 ENUM 集合值 (刪除被引用值會導致查詢返回空串)

操作流程 (需 root、停寫窗口)：

# 1. 備份數據目錄
cp -r /var/lib/mysql/db /backup/db_$(date +%F)

# 2. 建立結構完全一致的新表
CREATE TABLE tbl_new LIKE tbl;

# 3. 在新表上執行元數據變更
ALTER TABLE tbl_new ALTER COLUMN col SET DEFAULT 'new_val';

# 4. 全局鎖定、關閉所有表
FLUSH TABLES WITH READ LOCK;

# 5. 替換 .frm 文件 (關鍵步驟)
mv /var/lib/mysql/db/tbl.frm /var/lib/mysql/db/tbl.frm.bak
cp /var/lib/mysql/db/tbl_new.frm /var/lib/mysql/db/tbl.frm

# 6. 解鎖
UNLOCK TABLES;

# 7. 驗證
INSERT INTO tbl (col) VALUES (DEFAULT);
SELECT * FROM tbl WHERE col='new_val';

# 8. 清理
DROP TABLE tbl_new;

警告：繞過 Server 層直接改文件，**任何操作失誤可能導致表損壞、數據不一致、崩潰無法恢復**。僅在極端情況 (TB 級表、無維護窗口、業務允許短暫停寫) 下由資深 DBA 執行，並做好物理備份驗證。

四、Online DDL (5.6+) 與 Instant DDL (8.0+) 官方優化路線

版本	算法	支援操作	鎖模式
5.6	In-Place (INPLACE)	ADD/DROP INDEX、修改列預設值、RENAME COLUMN 等	共享鎖 (允許讀、阻塞寫) → 最後短暫排他鎖
5.7	In-Place 擴展	ADD COLUMN (末尾)、DROP COLUMN、TRUNCATE PARTITION 等	同上
8.0	Instant (INSTANT)	ADD COLUMN (末尾)、ADD VIRTUAL COLUMN、SET/DROP DEFAULT、RENAME COLUMN	無鎖、毫秒級、僅修改元數據

指定算法：ALTER TABLE tbl ADD COLUMN c INT, ALGORITHM=INPLACE, LOCK=NONE; (若不支援報錯，可回退 ALGORITHM=COPY)。

五、線上變更最佳實踐清單

優先用 Online/Instant DDL：ALGORITHM=INPLACE, LOCK=NONE 讓 Server 自動選擇最優算法。
大表必用 pt-online-schema-change (Percona Toolkit)：觸發器同步增量、分塊拷貝、可暫停/限流、無鎖切換，**生產環境大表變更標準工具**。
避開業務高峰：維護窗口執行，預留 2-3 倍表大小磁碟空間。
監控進度：SHOW PROCESSLIST 觀察 Copying to tmp table、Alter table 狀態；performance_schema.events_stages_current 看階段耗時。
備份先行：變更前做物理/邏輯備份，驗證可恢複。
外鍵處理：變更父表 PK/UK 時，需先 DROP FOREIGN KEY 子表約束，變更後再 ADD CONSTRAINT。

結語

**ALTER TABLE 不等於停機維護**。掌握 ALTER COLUMN SET DEFAULT 秒改預設值、ALGORITHM=INPLACE 就地執行、Instant ADD COLUMN 8.0 無鎖加列、**pt-online-schema-change** 大表神器，配合「備份先行、避開高峰、監控進度、驗證數據」流程，即可將表結構變更從「高危手術」變為「微創門診」。