MySQL 新增資料表欄位完整教學：ALTER TABLE ADD COLUMN 實戰詳解

業務擴展時常需為既有資料表新增欄位。MySQL 使用 ALTER TABLE ... ADD COLUMN 支援指定資料型別、NULL 約束、預設值、欄位位置等選項。本文透過實戰案例詳細說明各種新增場景。

基本語法

ALTER TABLE <表名> ADD [COLUMN] <欄位名> <資料型別> [約束條件] [FIRST | AFTER 既有欄位];

實戰案例演示

1. 最簡單新增：僅指定欄位名與型別

mysql> ALTER TABLE test ADD COLUMN birth DATE;
Query OK, 0 rows affected (0.36 sec)

mysql> SELECT * FROM test;
+------+--------+----------------------------------+------------+-------+
| t_id | t_name | t_password                       | t_birth    | birth |
+------+--------+----------------------------------+------------+-------+
|    1 | name1  | 12345678901234567890123456789012 | NULL       | NULL  |
|    2 | name2  | 12345678901234567890123456789012 | 2013-01-01 | NULL  |
+------+--------+----------------------------------+------------+-------+

預設值為 NULL，新欄位置於表尾。

2. 指定 NOT NULL（無預設值）

mysql> ALTER TABLE test ADD COLUMN birth1 DATE NOT NULL;
Query OK, 0 rows affected (0.16 sec)

mysql> SELECT * FROM test;
+------+--------+----------------------------------+------------+-------+------------+
| t_id | t_name | t_password                       | t_birth    | birth | birth1     |
+------+--------+----------------------------------+------------+-------+------------+
|    1 | name1  | ...                              | NULL       | NULL  | 0000-00-00 |
|    2 | name2  | ...                              | 2013-01-01 | NULL  | 0000-00-00 |
+------+--------+----------------------------------+------------+-------+------------+

MySQL 自動填入型別的「零值」（DATE 為 0000-00-00），不會報錯。但嚴格模式（sql_mode 含 STRICT_TRANS_TABLES）下會拋出錯誤。

3. 指定自訂預設值

mysql> ALTER TABLE test ADD COLUMN birth2 DATE DEFAULT '2013-01-01';
Query OK, 0 rows affected (0.28 sec)

mysql> SELECT * FROM test;
+------+--------+----------------------------------+------------+-------+------------+------------+
| t_id | t_name | t_password                       | t_birth    | birth | birth1     | birth2     |
+------+--------+----------------------------------+------------+-------+------------+------------+
|    1 | name1  | ...                              | NULL       | NULL  | 0000-00-00 | 2013-01-01 |
|    2 | name2  | ...                              | 2013-01-01 | NULL  | 0000-00-00 | 2013-01-01 |
+------+--------+----------------------------------+------------+-------+------------+------------+

既有資料自動填入指定預設值 2013-01-01。

進階選項

指定欄位位置

-- 作為第一個欄位
ALTER TABLE test ADD COLUMN new_id INT FIRST;

-- 放在指定欄位之後
ALTER TABLE test ADD COLUMN email VARCHAR(100) AFTER name;

批次新增多欄位

ALTER TABLE test
  ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  ADD COLUMN status TINYINT DEFAULT 1 AFTER email;

關鍵注意事項

• 鎖表影響：MySQL 5.6+ 支援 INPLACE 算法（僅元數據鎖），但特定型別/操作仍可能觸發 COPY（全表重建）。大表請評估影響。
• 嚴格模式：NOT NULL 無預設值時，嚴格模式下會報錯。建議永遠提供 DEFAULT。
• 預設值限制：TEXT/BLOB、JSON、GEOMETRY 不支援 DEFAULT。
• 線上變更工具：生產環境大表建議使用 pt-online-schema-change 或 gh-ost 避免長時間鎖表。

新增欄位看似簡單，實則涉及資料遷移、應用相容、鎖表風險等多重考量。請遵循「指定預設值、明確 NULL 約束、規劃位置、預估影響」原則，並配合變更管理流程執行。