MySQL ALTER TABLE 修改表結構完整語法與實戰演進範例：臨時表機制、IGNORE 行為、索引級聯、AUTO_INCREMENT 主鍵建立

MySQL ALTER TABLE 完整語法手冊與 t1→t2 演進實戰

系統整理官方文件 alter_specification 全清單，深度解析臨時表複製機制、IGNORE 唯一鍵衝突處理、索引級聯刪除規則，並以完整演進範例示範從建表到最終結構的全生命週期操作。

一、完整 alter_specification 清單

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

alter_spec:
  ADD [COLUMN] create_definition [FIRST | AFTER col_name]
  | ADD INDEX [index_name] (index_col_name, ...)
  | ADD PRIMARY KEY (index_col_name, ...)
  | ADD UNIQUE [index_name] (index_col_name, ...)
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name create_definition
  | MODIFY [COLUMN] create_definition
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | RENAME [AS] new_tbl_name
  | table_options

二、核心機制深度解析

三、型別變更關鍵規則

• CHANGE 需雙名稱（舊/新），可同步改名+改型別
• MODIFY 僅改型別，語法更簡潔（MySQL 3.22.16a+）
• 有索引前綴時，不可縮短欄位長度至前綴長度以下
• 型別轉換：MySQL 嘗試「盡力轉換」，資料截斷/溢位會發生警告而非錯誤

四、欄位位置控制

ALTER TABLE tbl ADD col INT FIRST;           -- 首欄
ALTER TABLE tbl ADD col INT AFTER other_col;  -- 指定欄後
-- 預設末尾

五、預設值管理

ALTER TABLE tbl ALTER col SET DEFAULT 'value';
ALTER TABLE tbl ALTER col DROP DEFAULT;  -- 可為 NULL 則預設 NULL，否則隱含預設值

六、完整演進範例：t1 → t2

-- 1. 建立初始表
CREATE TABLE t1 (a INTEGER, b CHAR(10));

-- 2. 重命名表
ALTER TABLE t1 RENAME t2;

-- 3. 型別變更 + 重命名欄位
ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

-- 4. 新增 TIMESTAMP 欄位
ALTER TABLE t2 ADD d TIMESTAMP;

-- 5. 建立索引與主鍵
ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

-- 6. 刪除欄位
ALTER TABLE t2 DROP COLUMN c;

-- 7. 新增自增主鍵（必須 NOT NULL + 索引）
ALTER TABLE t2
  ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD INDEX (c);

七、C API 驗證變更影響

mysql_info(&mysql);
// 輸出範例：Records: 100  Duplicates: 3  Warnings: 0
// Duplicates = IGNORE 模式下被刪除的重複列數

八、外鍵/CHECK/REFERENCES 相容性說明

MySQL 解析但不強制執行 FOREIGN KEY、CHECK、REFERENCES 子句，僅為相容其他 SQL 標準/遷移工具。實際約束需自行觸發器/應用層實現（InnoDB 除外，支援 FK）。

九、大表線上變更建議

1. MySQL 8.0+：優先 ALGORITHM=INSTANT（支援 ADD COLUMN、DROP COLUMN、RENAME INDEX 等）
2. 5.6-5.7：ALGORITHM=INPLACE + LOCK=NONE
3. 通用方案：pt-online-schema-change (Percona Toolkit) / gh-ost (GitHub)
4. 變更前：mysqldump --no-data db tbl > struct.sql 版本化結構

掌握完整語法與機制，配合線上變更工具，實現零停機、可回滾的結構演進。

1. 上一篇文章： MySQL ALTER TABLE 實戰運用指南：完整 alter_specification 清單、索引管理與欄位增刪改標準模板
2. 下一篇文章： MySQL 臨時表完整指南：建立、優化與實戰技巧

MySQL 綜合教學

MySQL 字元集知識體系總結：編碼原理、系統變數、轉換流程與實戰排查

常見字元集與編碼方式 字元集特點編碼方式 ASCII羅馬字

MySQL 5.6.12 從庫 Hang 住？Waiting for commit lock 死鎖深度剖析

MySQL 5.6.12 從庫 Hang 住？Waiting

MySQL 亂碼終極解決指南：編碼轉換全鏈路分析、錯進錯出陷阱與數據修復實戰

MySQL 字符集亂碼徹底解決：從編碼轉換原理到生產環境修復

SQL Server 追蹤旗標 (Trace Flags) 實戰手冊：DBA 必知的效能與診斷開關

什麼是 Trace Flag？ 用於啟用/停用 SQL Se