MySQL 增加、刪除、修改欄位 (ALTER TABLE) 完整語法速查

ALTER TABLE 是 MySQL DDL 核心操作，涉及鎖表、重建表、線上 DDL (Online DDL) 等效能考量。本文提供四大類常用操作標準語法、MySQL 5.6/5.7/8.0 線上 DDL 支援差異、大表操作最佳實踐。

1. 增加欄位

-- 基本語法
ALTER TABLE table_name ADD column_name column_definition [FIRST | AFTER existing_column];

-- 範例：在 id 後增加 username VARCHAR(20) NOT NULL DEFAULT ''
ALTER TABLE user_info ADD username VARCHAR(20) NOT NULL DEFAULT '' AFTER id;

-- 批次增加多欄位 (單次重建表)
ALTER TABLE user_info
  ADD email VARCHAR(100) NOT NULL DEFAULT '' AFTER username,
  ADD phone VARCHAR(20) NOT NULL DEFAULT '' AFTER email,
  ADD status TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER phone;

2. 刪除欄位

-- 基本語法
ALTER TABLE table_name DROP COLUMN column_name;

-- 範例
ALTER TABLE user_info DROP COLUMN phone;

-- 批次刪除
ALTER TABLE user_info DROP COLUMN email, DROP COLUMN status;

3. 修改欄位名稱 (CHANGE)

-- 語法：必須重新指定完整欄位定義
ALTER TABLE table_name CHANGE old_name new_name column_definition;

-- 範例：id -> user_id，並保持 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
ALTER TABLE user_info CHANGE id user_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

4. 修改欄位定義/類型/屬性 (MODIFY)

-- 語法：不改名稱，僅改定義
ALTER TABLE table_name MODIFY column_name new_column_definition;

-- 範例：username VARCHAR(20) -> VARCHAR(50) NOT NULL DEFAULT ''
ALTER TABLE user_info MODIFY username VARCHAR(50) NOT NULL DEFAULT '';

-- 修改為 NOT NULL (需確保現有資料無 NULL)
ALTER TABLE user_info MODIFY email VARCHAR(100) NOT NULL DEFAULT '';

5. 線上 DDL (Online DDL) 支援對照 (InnoDB)

INSTANT：僅修改數據字典，無鎖、無重建、毫秒級。
INPLACE：在原表重建，允許並行 DML，短暫排他鎖。
COPY：建立臨時表複製資料，全程排他鎖，**大表嚴禁**。

6. 大表變更最佳實踐

1. 確認線上 DDL 支援：EXPLAIN ALTER TABLE ... (8.0.18+) 查看 alter_table_algorithm。
2. 低峰期執行：即使 INPLACE，大表重建耗時小時級，影響主從延遲、備份窗口。
3. 使用 pt-online-schema-change (Percona Toolkit)：

   pt-online-schema-change --alter "ADD COLUMN status TINYINT DEFAULT 1" \
     D=db,t=user_info --execute --max-load Threads_running=100 --critical-load Threads_running=200

   觸發器同步、分塊複製、可暫停/恢復、**零停機**。
4. 預留磁碟空間：重建需 1.5-2 倍表大小臨時空間 (tmpdir 可指定大分區)。
5. 監控進度：SHOW PROCESSLIST 觀察 State: copy to tmp table / alter table。
6. 外鍵處理：變更被引用欄位需先 DROP FOREIGN KEY，變更後重建。

7. 常見錯誤與對策

1. 上一篇文章： 修改 MySQL 錯誤日誌路徑：從啟動腳本與 systemd 入手
2. 下一篇文章： SQL Server 並行查詢原理、配置與調優實戰指南

MySQL 資料庫相關知識

MySQL 常見錯誤速查與快速修復方案集錦

錯誤 1：TokuDB 磁碟空間預留不足 ERROR: T

SQLite Database Browser 2.0 圖文操作指南

工具簡介 SQLite Database Browser 2

NetBeans 操作 MySQL 實戰教程（二）：JDBC 驅動配置、建立連線與資料庫樹狀瀏覽

承接上篇 IDE 環境準備，本篇詳細演示 **JDBC 驅動

MySQL 5.7.13 Windows 10 圖形化安裝配置完整指南

前言 MySQL 5.7.13 引入了全新的圖形化安裝器，支