給線上表「加欄位」：從語法到生產級變更的全鏈路指南

ALTER TABLE ... ADD COLUMN 是最高頻的 DDL 操作之一。看似簡單，實則涉及預設值回填、鎖表行為、主從同步、工具選型等多重考量。本文以 Employee 表為例，完整演示新增欄位全流程，並給出生產環境安全變更清單。

一、基礎語法與核心選項

ALTER TABLE table_name
ADD [COLUMN] col_name column_definition
    [FIRST | AFTER col_name]
    [DEFAULT default_expr]
    [COMMENT 'comment']
    [GENERATED ALWAYS AS (expr) VIRTUAL|STORED];

• COLUMN 關鍵字可省略。
• FIRST：置於首位；AFTER col：置於指定欄位後（預設置末尾）。
• DEFAULT：MySQL 8.0.13+ 支援函數表達式（如 DEFAULT (UUID())），舊版僅支援常量。

二、完整實戰演示：從建表到驗證

1. 建立基準表並灌入測試數據

CREATE TABLE Employee (
    id INT,
    first_name VARCHAR(15),
    last_name VARCHAR(15),
    start_date DATE,
    end_date DATE,
    salary FLOAT(8,2),
    city VARCHAR(10),
    description VARCHAR(15)
);

-- 插入 8 筆測試數據（略）

2. 新增 DATE 類型欄位 new_date

ALTER TABLE Employee ADD new_date DATE;
-- Query OK, 8 rows affected (0.16 sec)
-- Records: 8  Duplicates: 0  Warnings: 0

驗證：

DESCRIBE Employee;
-- 新欄位出現在末尾，Type=date, Null=YES, Default=NULL

SELECT ID, new_date FROM Employee;
-- 8 行全為 NULL（無 DEFAULT 時隱式預設為 NULL）

3. 新增 VARCHAR 欄位 Middle_Name 並指定位置

ALTER TABLE Employee ADD Middle_Name VARCHAR(30) AFTER last_name;

DESCRIBE 確認位於 last_name 之後，first_name 之前。

三、進階場景：帶預設值、非空約束、生成欄位

場景 A：新增非空欄位並指定預設值（避免舊行報錯）

-- ❌ 直接加 NOT NULL 無 DEFAULT 會失敗（舊行無值）
ALTER TABLE Employee ADD status TINYINT NOT NULL; -- Error 1101

-- ✅ 正確姿勢：指定 DEFAULT
ALTER TABLE Employee ADD status TINYINT NOT NULL DEFAULT 1 COMMENT '1=在職 0=離職';

場景 B：新增生成欄位（虛擬/存儲）

-- 虛擬欄位：不佔用存儲，讀取時實時計算
ALTER TABLE Employee 
ADD full_name VARCHAR(60) 
GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL;

-- 存儲欄位：寫入時計算並持久化，佔用空間但查詢更快
ALTER TABLE Employee 
ADD name_hash CHAR(32) 
GENERATED ALWAYS AS (MD5(CONCAT(first_name, last_name))) STORED;

四、線上環境變更：風險評估與工具選型

風險矩陣

表大小	無預設值/NULL	有預設值/非空	生成欄位(STORED)
< 10 萬行	原生 INPLACE 秒級	原生 INPLACE 秒級	原生 INPLACE 秒級
10 萬 ~ 1 億行	原生 INPLACE 秒~分級	需回填，COPY 分鐘級	需計算回填，COPY 分鐘~小時級
> 1 億行	建議 pt-osc/gh-ost	必須 pt-osc/gh-ost	必須 pt-osc/gh-ost

工具實戰命令

# pt-online-schema-change (Percona Toolkit)
pt-online-schema-change \
  --alter "ADD COLUMN vip_level TINYINT NOT NULL DEFAULT 0" \
  D=your_db,t=Employee \
  --max-load Threads_running=100 \
  --critical-load Threads_running=200 \
  --chunk-time 0.5 \
  --execute

# gh-ost (GitHub Online Schema Tool)
gh-ost \
  --host=127.0.0.1 --port=3306 --user=root --password=xxx \
  --database=your_db --table=Employee \
  --alter="ADD COLUMN vip_level TINYINT NOT NULL DEFAULT 0" \
  --max-load=Threads_running=100 --critical-load=Threads_running=200 \
  --execute

五、常見坑點與解決方案

1. 字符集不一致導致亂碼

-- 表是 utf8mb4，新欄位未指定字符集會繼承表級別，但舊版可能預設 latin1
ALTER TABLE Employee ADD note VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. 外鍵約束阻礙

-- 若表有外鍵被引用，直接 ADD COLUMN 通常無影響
-- 但若新增欄位作為外鍵引用目標，需先確保被引用表有唯一索引

3. 分區表新增欄位

MySQL 8.0+ 支援 ALGORITHM=INSTANT 加欄位（無分區表限制）。分區表加欄位同樣走 INPLACE，但需注意分區定義不變。

4. 視圖依賴

新增欄位不影響現有視圖（SELECT * 視圖不會自動包含新欄位），但建議變更後重建相關視圖。

六、驗證清單（變更後必查）

• ✅ DESCRIBE table_name 確認欄位定義、順序、預設值、註釋
• ✅ SHOW CREATE TABLE table_name 對比變更前導出的 DDL
• ✅ 應用端寫入/讀取測試（含 ORM 實體類同步）
• ✅ 主從同步狀態：SHOW SLAVE STATUS\G 確認 Seconds_Behind_Master=0
• ✅ 監控指標：Threads_running、Innodb_buffer_pool_pages_dirty、磁盤 IOPS 無異常飆升

七、回滾預案

-- 若新增欄位導致嚴重問題，快速回滾
ALTER TABLE Employee DROP COLUMN new_date;
-- 注意：DROP COLUMN 同樣可能觸發 COPY 重建，大表同樣需用工具

給線上表加欄位，核心原則是：小表原生秒改，大表工具保命，預設值必帶，字符集显式指定，驗證清單走完再收工。