MySQL ALTER TABLE 修改表結構實戰：增刪欄位、調整順序、索引管理

前言

ALTER TABLE 是 MySQL 中修改現有資料表結構的核心指令。無論是新增欄位、調整順序、變更型別、管理索引，還是重命名表，都透過此指令完成。本文以實戰範例為主，系統整理常見操作語法與注意事項。

一、欄位管理操作

1. 新增欄位 (ADD COLUMN)

-- 基本新增（預設加在最後）
ALTER TABLE user_movement_log ADD COLUMN GatewayId INT NOT NULL DEFAULT 0;

-- 指定位置：AFTER 現有欄位 / FIRST 加在最前
ALTER TABLE user_movement_log ADD COLUMN GatewayId INT NOT NULL DEFAULT 0 AFTER RegionId;
ALTER TABLE new_table ADD COLUMN id INT PRIMARY KEY FIRST;

2. 刪除欄位 (DROP COLUMN)

ALTER TABLE user_movement_log DROP COLUMN GatewayId;

⚠️ 刪除欄位會同步移除該欄位相關的索引與約束，資料永久遺失，操作前請備份。

3. 修改欄位定義 (MODIFY / CHANGE)

-- MODIFY：只變更型別/屬性，欄位名不變
ALTER TABLE t1 MODIFY COLUMN b BIGINT NOT NULL;
ALTER TABLE infos MODIFY COLUMN list TINYINT NOT NULL DEFAULT 0;

-- CHANGE：可同時變更欄位名與定義（需寫兩次欄位名）
ALTER TABLE t1 CHANGE COLUMN a b INTEGER;
ALTER TABLE infos CHANGE COLUMN list list TINYINT NOT NULL DEFAULT 0;

4. 調整欄位順序

MySQL 不支援直接「移動欄位」，需透過 CHANGE ... AFTER 重新定義：

ALTER TABLE user_movement_log 
    CHANGE COLUMN GatewayId GatewayId INT NOT NULL DEFAULT 0 AFTER RegionId;

二、主鍵與約束管理

1. 新增主鍵 (AUTO_INCREMENT)

ALTER TABLE table_name 
    ADD new_field_id INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    ADD PRIMARY KEY (new_field_id);

2. 新增/刪除索引

-- 普通索引
ALTER TABLE tablename ADD INDEX idx_name (column_name);
ALTER TABLE tablename ADD INDEX idx_multi (col1, col2);

-- 唯一索引
ALTER TABLE tablename ADD UNIQUE uk_card (cardnumber);

-- 主鍵索引
ALTER TABLE tablename ADD PRIMARY KEY (id);

-- 刪除索引
ALTER TABLE tablename DROP INDEX idx_name;

三、表級操作

1. 重命名表

ALTER TABLE t1 RENAME TO t2;

2. 變更儲存引擎

ALTER TABLE t1 ENGINE = InnoDB;

3. 批次執行多項變更（單次鎖表）

ALTER TABLE users
    ADD COLUMN phone VARCHAR(20) AFTER email,
    MODIFY COLUMN name VARCHAR(100) NOT NULL,
    DROP COLUMN deprecated_col,
    ADD INDEX idx_phone (phone),
    ADD UNIQUE uk_email (email);

✅ 建議：生產環境盡量合併操作，減少鎖表時間與資料複製開銷。

四、常見陷阱與注意事項

五、驗證指令

-- 查看表結構
SHOW CREATE TABLE table_name;
DESCRIBE table_name;
SHOW COLUMNS FROM table_name;

-- 查看索引
SHOW INDEX FROM table_name;

結語

ALTER TABLE 功能強大但風險並存。掌握語法細節、理解底層鎖表行為、善用批次操作與線上變更工具，才能在生產環境安全、高效地演進資料庫結構。