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

MySQL ALTER TABLE 完整語法詳解：欄位、索引、分區與表選項修改

編輯：MySQL 綜合教學

資料庫上線後，業務變更常要求調整表結構。MySQL 的 ALTER TABLE 是執行線上結構變更（DDL）的核心命令，支援欄位增減修改、索引管理、外鍵約束、分區操作、字符集轉換等數十種子句。本文系統整理完整語法分類，並針對高頻場景提供最佳實踐建議。

ALTER TABLE 核心語法架構

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

alter_specification:
    -- 欄位操作
    ADD [COLUMN] col_def [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_def, ...)
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name col_def [FIRST | AFTER col_name]
  | MODIFY [COLUMN] col_def [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name

    -- 索引與約束
  | ADD INDEX [index_name] [index_type] (index_col_name, ...)
  | ADD PRIMARY KEY [index_type] (index_col_name, ...)
  | ADD UNIQUE [index_name] [index_type] (index_col_name, ...)
  | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name, ...)
  | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) [reference_definition]
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DROP FOREIGN KEY fk_symbol

    -- 索引維護
  | DISABLE KEYS
  | ENABLE KEYS

    -- 表級選項
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
  | DISCARD TABLESPACE | IMPORT TABLESPACE
  | table_options

    -- 分區操作（MySQL 5.1+）
  | ADD PARTITION partition_definition
  | DROP PARTITION partition_names
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | ANALYZE PARTITION partition_names
  | CHECK PARTITION partition_names
  | OPTIMIZE PARTITION partition_names
  | REBUILD PARTITION partition_names
  | REPAIR PARTITION partition_names

高頻場景最佳實踐

1. 新增欄位：指定位置提升可讀性

-- 新增欄位放在首位
ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP FIRST;

-- 新增欄位放在指定欄位之後
ALTER TABLE users ADD COLUMN status TINYINT DEFAULT 1 AFTER email;

2. 修改欄位定義：CHANGE vs MODIFY

-- 修改欄位名稱 + 定義（必須用 CHANGE）
ALTER TABLE users CHANGE COLUMN email user_email VARCHAR(100) NOT NULL;

-- 僅修改定義（類型、長度、預設值等），推薦用 MODIFY
ALTER TABLE users MODIFY COLUMN user_email VARCHAR(150) NOT NULL DEFAULT '';

3. 索引管理：線上操作注意事項

新增索引：ALTER TABLE t ADD INDEX idx_name (col); — 5.6+ 支援 Online DDL，僅鎖表極短時間
刪除索引：ALTER TABLE t DROP INDEX idx_name;
主鍵變更：需先 DROP PRIMARY KEY 再 ADD PRIMARY KEY，操作會重建表，大表請評估影響

4. 字符集轉換：避免亂碼關鍵步驟

-- 僅修改表預設字符集（不影響現有資料）
ALTER TABLE t DEFAULT CHARACTER SET utf8mb4;

-- 轉換現有資料編碼（會重建表，大表慎用）
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

5. 分區維護：大表管理利器

-- 新增分區
ALTER TABLE sales ADD PARTITION (PARTITION p2024 VALUES LESS THAN (20250101));

-- 刪除歷史分區（瞬間完成，資料同步刪除）
ALTER TABLE sales DROP PARTITION p2020;

-- 重組分區
ALTER TABLE sales REORGANIZE PARTITION p2021, p2022 INTO (
    PARTITION p2021 VALUES LESS THAN (20220101),
    PARTITION p2022 VALUES LESS THAN (20230101)
);

效能與鎖定策略提示

操作類型	MySQL 5.6+ Online DDL	鎖定程度	建議
新增/刪除欄位	支援	短暫元資料鎖	業務低峰執行
新增/刪除索引	支援	短暫元資料鎖	可線上執行
修改欄位類型	不支援	全表鎖（複製表）	大表用 pt-online-schema-change
字符集轉換	不支援	全表鎖	大表分批遷移
分區增刪	支援	分區級鎖	極快，無感知