ALTER [IGNORE] TABLE tbl_name
[ADD [COLUMN] col_def [FIRST | AFTER col_name]]
[ADD INDEX [idx_name] (col_name,...)]
[ADD PRIMARY KEY (col_name,...)]
[ADD UNIQUE [idx_name] (col_name,...)]
[ADD FOREIGN KEY [idx_name] (col_name,...) REFERENCES ...]
[ADD FULLTEXT [idx_name] (col_name,...)]
[ADD SPATIAL [idx_name] (col_name,...)]
[ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}]
[CHANGE [COLUMN] old_col_name new_col_def]
[MODIFY [COLUMN] col_def]
[DROP [COLUMN] col_name]
[DROP PRIMARY KEY]
[DROP INDEX idx_name]
[DROP FOREIGN KEY fk_name]
[RENAME [TO] new_tbl_name]
[table_options]
[partition_options];
-- 增加欄位（指定位置） ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email; ALTER TABLE users ADD COLUMN id INT FIRST; -- 修改欄位名 + 類型 (CHANGE) ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(20); -- 僅修改類型/屬性 (MODIFY) ALTER TABLE users MODIFY COLUMN mobile VARCHAR(20) NOT NULL DEFAULT ''; -- 刪除欄位 ALTER TABLE users DROP COLUMN mobile;
-- 普通索引 ALTER TABLE users ADD INDEX idx_email (email); -- 唯一索引 ALTER TABLE users ADD UNIQUE INDEX uk_email (email); -- 主鍵 ALTER TABLE users ADD PRIMARY KEY (id); -- 全文索引 (MyISAM/InnoDB 5.6+) ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content); -- 空間索引 ALTER TABLE geom ADD SPATIAL INDEX sp_loc (location); -- 刪除索引 ALTER TABLE users DROP INDEX idx_email;
-- 重命名表 ALTER TABLE users RENAME TO members; -- 或 RENAME TABLE users TO members; -- 修改表選項 ALTER TABLE users ENGINE=InnoDB; ALTER TABLE users COMMENT='會員表'; ALTER TABLE users AUTO_INCREMENT=10000; ALTER TABLE users ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
-- 添加
ALTER TABLE orders ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE ON UPDATE CASCADE;
-- 刪除
ALTER TABLE orders DROP FOREIGN KEY fk_user;
| 算法 | 全稱 | 鎖級別 | 適用場景 | 版本支持 |
|---|---|---|---|---|
| COPY | 拷貝重建 | 排他鎖 (全程阻塞讀寫) | 早期版本、不支持 INPLACE 的操作 | 所有版本 |
| INPLACE | 原地修改 | 共享鎖 (允許讀、阻塞寫) | 加/刪索引、修改列屬性、重命名 | 5.6+ |
| INSTANT | 元數據修改 | 極短元數據鎖 (近乎無感) | 加欄位(末尾)、修改默認值、刪除虛擬列 | 8.0+ |
-- 強制指定 ALTER TABLE users ADD COLUMN phone VARCHAR(20), ALGORITHM=INPLACE, LOCK=NONE; -- ALGORITHM: DEFAULT/INSTANT/INPLACE/COPY -- LOCK: DEFAULT/NONE/SHARED/EXCLUSIVE
graph TD
A[需要變更] --> B{操作類型}
B -->|加欄位末尾/改默認值| C[8.0+ INSTANT 秒級]
B -->|加索引/改類型/重命名| D[5.6+ INPLACE 允許讀]
B -->|改主鍵/分區/字符集| E[COPY 需停寫]
C --> F[生產直接執行]
D --> G[低峰期執行]
E --> H[用 pt-osc/gh-ost 無鎖變更]
pt-online-schema-change \ --alter "ADD COLUMN phone VARCHAR(20)" \ D=db,t=users \ --execute \ --max-load Threads_running=100 \ --critical-load Threads_running=200 \ --chunk-time 0.5
原理：建影子表 → 建觸發器同步增量 → 分塊拷貝歷史數據 → 切換表名。
gh-ost \ --host=127.0.0.1 --user=root --password=pass \ --database=db --table=users \ --alter="ADD COLUMN phone VARCHAR(20)" \ --execute --allow-on-master
優勢：無觸發器、基於 Binlog 同步、可暫停/限流、支持從庫執行。
-- 支持 INSTANT 的操作 ALTER TABLE users ADD COLUMN last_login DATETIME; -- 末尾加欄位 ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active'; -- 改默認值 ALTER TABLE users DROP COLUMN virtual_col; -- 刪虛擬列 -- 幾乎無鎖，秒級完成，TB 級大表同樣適用
| 操作 | 風險 | 規避 |
|---|---|---|
| 修改主鍵 | COPY 算法、全程鎖表、主從延遲巨大 | 業務設計階段確定主鍵，避免後期變更 |
| 字符集轉換 | 全表重建、數據校驗風險 | 分批遷移、驗證 HEX 值 |
| 大表加索引 | INPLACE 仍需掃描全表、佔用臨時空間 | 低峰期、監控磁盤、pt-osc |
| 刪除欄位 | COPY 算法、外鍵依賴檢查 | 先刪外鍵、確認無依賴 |
| ENUM/SET 修改 | 需重建表、值映射風險 | 改用 VARCHAR+CHECK 約束 |
-- 查看表結構 SHOW CREATE TABLE users; -- 查看索引 SHOW INDEX FROM users; -- 查看分區 SHOW PARTITIONS FROM users; -- 信息表查詢 SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME='users'; SELECT * FROM information_schema.STATISTICS WHERE TABLE_NAME='users';
ALTER TABLE 核心心法：**了解算法分級、評估鎖影響、選擇右工具**。MySQL 8.0+ Instant DDL 解決 80% 加欄位場景，INPLACE 覆蓋 15% 索引/屬性變更，剩餘 5% 核心結構變更請務必用 pt-osc/gh-ost。**生產環境無小事，變更前必備份、必演練、必有回滾預案**。
