MySQL ALTER TABLE 修改欄位完整語法指南與實戰範例
資料表上線後常需調整結構：新增欄位、調整型別、重命名、調整順序。本文整理 ALTER TABLE 所有欄位操作語法、參數細節與實戰輸出範例，助您精準執行 DDL 避免鎖表風險。
核心語法速查表
| 操作 | 語法 | 範例 |
|---|
| 新增欄位 (預設加在最後) | ALTER TABLE tbl ADD col_name col_def; | ALTER TABLE t ADD email VARCHAR(100) NOT NULL DEFAULT ''; |
| 新增欄位 (指定位置：AFTER) | ALTER TABLE tbl ADD col_name col_def AFTER exist_col; | ALTER TABLE t ADD phone VARCHAR(20) AFTER email; |
| 新增欄位 (置於最前) | ALTER TABLE tbl ADD col_name col_def FIRST; | ALTER TABLE t ADD id BIGINT NOT NULL FIRST; |
| 刪除欄位 | ALTER TABLE tbl DROP COLUMN col_name; | ALTER TABLE t DROP COLUMN phone; |
| 修改欄位型別/屬性 (保留名稱) | ALTER TABLE tbl MODIFY col_name new_def; | ALTER TABLE t MODIFY email VARCHAR(150) NOT NULL DEFAULT ''; |
| 修改欄位名稱 + 型別/屬性 | ALTER TABLE tbl CHANGE old_name new_name new_def; | ALTER TABLE t CHANGE email contact_email VARCHAR(150); |
| 重命名資料表 | ALTER TABLE old_name RENAME TO new_name; | ALTER TABLE t RENAME TO users; |
| 清空資料並重置自增 | TRUNCATE TABLE tbl; | TRUNCATE TABLE logs; |
實戰演示流程 (MySQL 客戶端輸出)
mysql> CREATE TABLE m1 (
-> id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
-> );
Query OK, 0 rows affected (0.62 sec)
mysql> DESC m1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
+-------+------------------+------+-----+---------+----------------+
mysql> ALTER TABLE m1 ADD username CHAR(20) NOT NULL DEFAULT '';
Query OK, 0 rows affected (0.49 sec)
mysql> ALTER TABLE m1 ADD birth DATE NOT NULL DEFAULT '0000-00-00';
Query OK, 0 rows affected (0.46 sec)
mysql> ALTER TABLE m1 ADD gender CHAR(1) NOT NULL DEFAULT '' AFTER username;
Query OK, 0 rows affected (0.36 sec)
mysql> ALTER TABLE m1 ADD pid INT NOT NULL FIRST;
Query OK, 0 rows affected (0.40 sec)
mysql> DESC m1;
+----------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------+----------------+
| pid | int(11) | NO | | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | | |
| gender | char(1) | NO | | | |
| birth | date | NO | 0000-00-00 | | |
+----------+------------------+------+-----+------------+----------------+
mysql> ALTER TABLE m1 DROP pid;
Query OK, 0 rows affected (0.37 sec)
mysql> ALTER TABLE m1 MODIFY gender CHAR(4) NOT NULL DEFAULT '';
Query OK, 0 rows affected (0.47 sec)
mysql> ALTER TABLE m1 CHANGE id uid INT UNSIGNED;
Query OK, 0 rows affected (0.44 sec)
mysql> DESC m1;
+----------+------------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------+-------+
| uid | int(10) unsigned | NO | PRI | 0 | |
| username | char(20) | NO | | | |
| gender | char(4) | NO | | | |
| birth | date | NO | 0000-00-00 | | |
+----------+------------------+------+-----+------------+-------+
生產環境執行 DDL 風險控制
- 鎖表影響：MySQL 5.6 前
ALTER TABLE 全程鎖表；5.6+ ALGORITHM=INPLACE 部分操作支援線上執行
- 大表策略：使用
pt-online-schema-change (Percona Toolkit) 或 gh-ost (GitHub) 實現無鎖變更
- 備份先行：執行前必備份結構與資料
- 驗證相容性：型別變更確保現有資料可轉換 (如
VARCHAR(10) → VARCHAR(5) 會截斷)
- 應用協調：欄位重命名需同步更新應用程式碼，建議雙寫過渡期
常見錯誤與解決
| 錯誤 | 原因 | 解決 |
|---|
ERROR 1067: Invalid default value | 型別不支援該預設值 (如 INT DEFAULT '') | 移除 DEFAULT 或給合法值 |
ERROR 1833: Cannot change column | 外鍵約束阻礙 | 先 DROP FOREIGN KEY 再修改 |
ERROR 1090: Can't drop all columns | 嘗試刪除最後一個欄位 | 保留至少一欄位或 DROP TABLE |
