MySQL ALTER TABLE 是 DDL 操作的核心,涉及鎖表、拷貝、重建、元數據鎖等復雜機制。錯誤的變更可能導致業務長時間阻塞、主從延遲甚至數據不一致。本文系統梳理 修改欄位類型、重命名欄位、新增/刪除索引 的語法、實戰案例、版本差異(5.6/5.7/8.0)與線上安全變更最佳實踐。
-- 修改欄位類型/名稱(CHANGE 需指定舊名,MODIFY 僅改類型)
ALTER TABLE tbl_name CHANGE old_col_name new_col_name column_definition [FIRST|AFTER col];
ALTER TABLE tbl_name MODIFY col_name column_definition [FIRST|AFTER col];
-- 新增/刪除欄位
ALTER TABLE tbl_name ADD col_name column_definition [FIRST|AFTER col];
ALTER TABLE tbl_name DROP COLUMN col_name;
-- 索引操作
ALTER TABLE tbl_name ADD INDEX idx_name (col1, col2);
ALTER TABLE tbl_name ADD UNIQUE uniq_name (col1);
ALTER TABLE tbl_name ADD PRIMARY KEY (col1);
ALTER TABLE tbl_name DROP INDEX idx_name;
CREATE TABLE Employee (
id INT,
first_name VARCHAR(15),
last_name VARCHAR(15),
start_date DATE,
end_date DATE,
salary FLOAT(8,2),
city VARCHAR(10),
description VARCHAR(15)
);
ALTER TABLE Employee
CHANGE First_Name FirstName VARCHAR(20);
CHANGE 必須指定舊欄位名、新欄位名、完整定義。COPY 算法(鎖表拷貝),5.7+ 支援 INPLACE(僅元數據修改,極快)。ALTER TABLE Employee
MODIFY Last_Name VARCHAR(50);
MODIFY 更語義化,避免誤輸入舊名。VARCHAR(50) → VARCHAR(10))需確保現有數據不超長,否則報錯 Data too long。ALTER TABLE Employee
MODIFY city VARCHAR(20) AFTER first_name;
-- 或
ALTER TABLE Employee
CHANGE city city VARCHAR(20) FIRST;
僅影響元數據,不重排數據頁(InnoDB 8.0+ 瞬間完成)。
ALTER TABLE myTable
ADD UNIQUE (OrderID, ModelID);
pt-online-schema-change。ALTER TABLE myTable DROP INDEX idx_name;
-- 或刪除主鍵
ALTER TABLE myTable DROP PRIMARY KEY;
| 操作類型 | 5.6 及之前 | 5.7 | 8.0 |
|---|---|---|---|
| 修改 VARCHAR 長度(增大) | COPY(鎖表) | INPLACE(秒級) | INPLACE + 瞬時元數據(毫秒級) |
| 修改 VARCHAR 長度(減小) | COPY | COPY(需檢查數據) | COPY(需檢查數據) |
| CHANGE/MODIFY 類型轉換 | COPY | COPY | 大多 COPY,部分 INPLACE |
| ADD/DROP INDEX | COPY | INPLACE | INPLACE + 並行構建 |
| RENAME COLUMN | ❌ 不支援 | ❌ | ✅ INPLACE 瞬時 |
查看算法:EXPLAIN ALTER TABLE ... 或 SHOW WARNINGS 後檢查 ALGORITHM 欄位。
| 工具 | 原理 | 優點 | 缺點 | 適用場景 |
|---|---|---|---|---|
| pt-online-schema-change | 觸發器同步 + 影子表換名 | 成熟穩定、支援過濾、可暫停 | 需 Perl 環境、觸發器有微小開銷 | 通用生產環境首選 |
| gh-ost | Binlog 同步 + 影子表(無觸發器) | 無觸發器開銷、可調節流控、支援切換驗證 | 需啟用 Row 格式 Binlog、學習曲線稍陡 | 高併發、極大表(TB 級) |
| MySQL 8.0 瞬時 DDL | 純元數據修改 | 零鎖、零拷貝、原生支援 | 僅限部分操作(擴大 VARCHAR、增減欄位預設值等) | 符合瞬時條件的變更 |
DROP FOREIGN KEY,否則報錯 Error 1832。ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 會重建全表並轉換數據;CHANGE col col VARCHAR(100) CHARSET utf8mb4 僅改欄位定義,不轉換現有數據。pt-osc --max-lag=1000 自動限速。SELECT * FROM information_schema.INNODB_TRX WHERE trx_mysql_thread_id IN (SELECT id FROM information_schema.PROCESSLIST WHERE db='your_db' AND time > 60);INSTANT → gh-ost → pt-osc → 原生 ALGORITHM=INPLACEThreads_running、Innodb_row_lock_waits、磁盤 IO變更表結構如同「給飛行中的飛機換引擎」,敬畏 DDL、工具先行、預案在手,才能在線上環境游刃有餘。