MySQL ALTER TABLE 命令全維度解析：列操作、索引管理、表選項變更、分區維護與線上 DDL 最佳實踐

核心語法結構

ALTER TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

alter_specification:
    ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition, ...)
  | ADD {INDEX | KEY} [index_name] [index_type] (key_part, ...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part, ...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (key_part, ...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name, ...) reference_definition
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]
  | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP {INDEX | KEY} index_name
  | DROP FOREIGN KEY fk_symbol
  | RENAME [TO | AS] new_tbl_name
  | RENAME {INDEX | KEY} old_index_name TO new_index_name
  | {ENABLE | DISABLE} KEYS
  | {WITHOUT VALIDATION | WITH VALIDATION}  -- 8.0.16+ 外鍵/CHECK
  | ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}
  | LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
  | ORDER BY col_name [, col_name] ...
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE collation_name]
  | DISCARD TABLESPACE | IMPORT TABLESPACE
  | {ADD | DROP | REORGANIZE | COALESCE | ANALYZE | OPTIMIZE | REBUILD | REPAIR | CHECK} PARTITION ...

一、 列增刪改查 (DDL 核心高頻操作)

1. 添加列

ALTER TABLE t ADD COLUMN email VARCHAR(100) NOT NULL DEFAULT '' COMMENT '郵箱' AFTER name;
ALTER TABLE t ADD COLUMN (a INT, b INT);  -- 批量添加

2. 修改列定義

• MODIFY：僅修改類型/屬性，**列名不變**。

  ALTER TABLE t MODIFY COLUMN email VARCHAR(200) CHARACTER SET utf8mb4;

• CHANGE：可同時修改**列名 + 類型/屬性** (必須提供新列名)。

  ALTER TABLE t CHANGE COLUMN email user_email VARCHAR(200);

注意： 縮短列長度可能截斷數據，需先檢查 SELECT MAX(CHAR_LENGTH(col)) FROM t;。

3. 移動列位置

FIRST (移至第一列)、AFTER col_name (移至指定列後)。僅 ADD、MODIFY、CHANGE 支持。

4. 刪除列

ALTER TABLE t DROP COLUMN email;
-- 含索引/外鍵的列需先刪除依賴對象

5. 默認值管理

ALTER TABLE t ALTER COLUMN status SET DEFAULT 1;
ALTER TABLE t ALTER COLUMN status DROP DEFAULT;

二、 索引與約束管理

-- 添加普通索引
ALTER TABLE t ADD INDEX idx_status (status);
ALTER TABLE t ADD KEY idx_user_status (user_id, status);  -- 聯合索引

-- 添加唯一索引
ALTER TABLE t ADD UNIQUE INDEX uk_email (email);

-- 添加主鍵 (表無主鍵時)
ALTER TABLE t ADD PRIMARY KEY (id);

-- 刪除索引
ALTER TABLE t DROP INDEX idx_status;
ALTER TABLE t DROP PRIMARY KEY;  -- 刪除主鍵

-- 外鍵 (謹慎)
ALTER TABLE child ADD CONSTRAINT fk_parent FOREIGN KEY (pid) REFERENCES parent(id) ON DELETE CASCADE;
ALTER TABLE child DROP FOREIGN KEY fk_parent;

三、 表級屬性變更

• 重命名表：ALTER TABLE old_name RENAME TO new_name; (或 RENAME TABLE old TO new;)
• 修改字符集：ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; (轉換存量數據，**會重建表**)。
• 修改存儲引擎：ALTER TABLE t ENGINE = InnoDB; (重建表)。
• 表選項：ALTER TABLE t COMMENT = '訂單表', AUTO_INCREMENT = 10000, ROW_FORMAT = DYNAMIC;

四、 線上 DDL (Online DDL) 算法與鎖模式選擇 (5.6+ 核心特性)

顯式控制算法與鎖 (生產大表必加)

-- 添加分區
ALTER TABLE t ADD PARTITION (PARTITION p2024 VALUES LESS THAN (2025));

-- 刪除分區 (數據丟失)
ALTER TABLE t DROP PARTITION p2020;

-- 合併/重組分區 (保留數據)
ALTER TABLE t REORGANIZE PARTITION p2020, p2021 INTO (PARTITION p2021 VALUES LESS THAN (2022));

-- 維護單個分區
ALTER TABLE t REBUILD PARTITION p2023;      -- 重建 (整理碎片)
ALTER TABLE t ANALYZE PARTITION p2023;      -- 更新統計信息
ALTER TABLE t OPTIMIZE PARTITION p2023;     -- 優化 (InnoDB 等同 REBUILD + ANALYZE)
ALTER TABLE t CHECK PARTITION p2023;        -- 檢查損壞

六、 生產環境大表變更黃金法則

1. 原則：小步快跑、可回滾、可監控。
2. 工具：gh-ost (GitHub) 或 pt-online-schema-change (Percona) — 觸發器+影子表機制，近零鎖、可限流、可暫停、主從延遲感知。
3. 禁忌：直接在高峰期對大表執行 COPY 算法操作 (修改字符集、引擎、刪除列、修改主鍵)。
4. 驗證：先在備庫/測試庫驗證語法、執行時間、主從延遲影響。
5. 監控：變更期間監控 Threads_running、Innodb_row_lock_waits、主從延遲 (pt-heartbeat)。