跨資料庫 ALTER TABLE 語法差異對照表與最佳實務
ALTER TABLE 是 DDL 核心,各資料庫方言差異極大。本文提供 MySQL、SQL Server、Oracle、標準 SQL 四大方言的完整語法對照,涵蓋欄位增刪改、重命名、型別變更、索引/主鍵/唯一/外鍵約束操作。
通用語法框架
ALTER TABLE table_name
[ADD column_definition | DROP COLUMN col | ALTER/MODIFY/CHANGE col ...]
[ADD/DROP CONSTRAINT ...]
[ADD/DROP INDEX ...]
[RENAME TO new_name];
欄位操作對照表
| 操作 | MySQL | SQL Server | Oracle | 標準 SQL |
| 新增欄位 | ADD COLUMN col INT | ADD col INT | ADD (col INT) | ADD COLUMN col INT |
| 刪除欄位 | DROP COLUMN col | DROP COLUMN col | DROP COLUMN col | DROP COLUMN col |
| 修改欄位名 | CHANGE old new INT | sp_rename 't.old', 'new', 'COLUMN' | RENAME COLUMN old TO new | RENAME COLUMN old TO new |
| 修改型別/屬性 | MODIFY col VARCHAR(50) | ALTER COLUMN col VARCHAR(50) | MODIFY (col VARCHAR2(50)) | ALTER COLUMN col SET DATA TYPE VARCHAR(50) |
| 修改預設值 | ALTER COLUMN col SET DEFAULT 0 | ADD CONSTRAINT df DEFAULT 0 FOR col | MODIFY (col DEFAULT 0) | ALTER COLUMN col SET DEFAULT 0 |
| 刪除預設值 | ALTER COLUMN col DROP DEFAULT | DROP CONSTRAINT df_name | MODIFY (col DEFAULT NULL) | ALTER COLUMN col DROP DEFAULT |
索引與約束操作對照
| 操作 | MySQL | SQL Server | Oracle |
| 加主鍵 | ADD PRIMARY KEY (col) | ADD CONSTRAINT pk PRIMARY KEY (col) | ADD CONSTRAINT pk PRIMARY KEY (col) |
| 刪主鍵 | DROP PRIMARY KEY | DROP CONSTRAINT pk | DROP CONSTRAINT pk |
| 加唯一索引 | ADD UNIQUE idx (col) | CREATE UNIQUE INDEX idx ON t(col) | CREATE UNIQUE INDEX idx ON t(col) |
| 加普通索引 | ADD INDEX idx (col) | CREATE INDEX idx ON t(col) | CREATE INDEX idx ON t(col) |
| 刪索引 | DROP INDEX idx ON tbl | DROP INDEX idx ON t | DROP INDEX idx |
| 加外鍵 | ADD CONSTRAINT fk FOREIGN KEY (col) REFERENCES p(col) | ADD CONSTRAINT fk FOREIGN KEY (col) REFERENCES p(col) | ADD CONSTRAINT fk FOREIGN KEY (col) REFERENCES p(col) |
| 刪外鍵 | DROP FOREIGN KEY fk | DROP CONSTRAINT fk | DROP CONSTRAINT fk |
| 重命名表 | RENAME TABLE old TO new | sp_rename 'old', 'new' | RENAME old TO new |
實戰範例:MySQL 完整修改流程
-- 1. 新增欄位
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
-- 2. 修改型別並加 NOT NULL
ALTER TABLE users MODIFY phone VARCHAR(20) NOT NULL DEFAULT '';
-- 3. 重命名欄位
ALTER TABLE users CHANGE phone mobile VARCHAR(20) NOT NULL DEFAULT '';
-- 4. 加唯一索引
ALTER TABLE users ADD UNIQUE uk_mobile (mobile);
-- 5. 刪除索引
ALTER TABLE users DROP INDEX uk_mobile;
-- 6. 修改表名
RENAME TABLE users TO app_users; -- 或 ALTER TABLE users RENAME TO app_users;
大表線上變更工具 (避免長時間鎖表)
- pt-online-schema-change (Percona):影子表複製 → 觸發器同步 → 原子切換,支援 MySQL/Percona/MariaDB。
- gh-ost (GitHub):無觸發器,解析 binlog 同步,可暫停/限流/閾值控制。
- MySQL 8.0+ Instant DDL:
ADD COLUMN、DROP VIRTUAL COLUMN、EXTEND VARCHAR、SET DEFAULT 等無鎖、無拷貝、毫秒級完成。
避坑清單
- 型別收縮前必檢查最大長度:
SELECT MAX(LENGTH(col)) FROM tbl。
- 刪除欄位前檢視相依性:視圖、SP、觸發器、應用代碼、外鍵。
- 外鍵循環依賴:
SET FOREIGN_KEY_CHECKS=0 臨時關閉,操作完開啟。
- 字符集轉換:
ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 會重建表,大表必用線上工具。
- 主鍵變更:需先
DROP PRIMARY KEY 再 ADD PRIMARY KEY,期間表無主鍵,極度危險,建議維護窗口操作。