您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

SQL ALTER TABLE 完全指南：欄位增刪改、索引管理、主鍵約束與跨平台語法差異

編輯：MySQL 綜合教學

前言

ALTER TABLE 是資料庫結構變更的核心指令，涵蓋欄位增刪改、索引管理、約束調整等高頻操作。本文系統整理 MySQL、SQL Server、Oracle 三大主流資料庫的語法差異，提供標準化範例與避坑指南，助您安全高效完成 Schema 變更。

核心操作語法對照表

操作	MySQL	SQL Server	Oracle
新增欄位	`ALTER TABLE t ADD col INT;`	`ALTER TABLE t ADD col INT;`	`ALTER TABLE t ADD col INT;`
刪除欄位	`ALTER TABLE t DROP COLUMN col;`	`ALTER TABLE t DROP COLUMN col;`	`ALTER TABLE t DROP COLUMN col;`
修改欄位型別	`ALTER TABLE t MODIFY col BIGINT;`	`ALTER TABLE t ALTER COLUMN col BIGINT;`	`ALTER TABLE t MODIFY col NUMBER;`
重命名欄位	`ALTER TABLE t CHANGE old_col new_col INT;`	`EXEC sp_rename 't.old_col', 'new_col', 'COLUMN';`	`ALTER TABLE t RENAME COLUMN old_col TO new_col;`
重命名資料表	`ALTER TABLE t RENAME TO t2;`	`EXEC sp_rename 't', 't2';`	`ALTER TABLE t RENAME TO t2;`
新增主鍵	`ALTER TABLE t ADD PRIMARY KEY (id);`	`ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (id);`	`ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (id);`
新增唯一索引	`ALTER TABLE t ADD UNIQUE idx_name (col);`	`CREATE UNIQUE INDEX idx_name ON t(col);`	`CREATE UNIQUE INDEX idx_name ON t(col);`
新增普通索引	`ALTER TABLE t ADD INDEX idx_name (col);`	`CREATE INDEX idx_name ON t(col);`	`CREATE INDEX idx_name ON t(col);`
刪除索引	`ALTER TABLE t DROP INDEX idx_name;`	`DROP INDEX idx_name ON t;`	`DROP INDEX idx_name;`
新增外鍵	`ALTER TABLE t ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES ref_t(ref_col);`	`ALTER TABLE t ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES ref_t(ref_col);`	`ALTER TABLE t ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES ref_t(ref_col);`
刪除外鍵	`ALTER TABLE t DROP FOREIGN KEY fk_name;`	`ALTER TABLE t DROP CONSTRAINT fk_name;`	`ALTER TABLE t DROP CONSTRAINT fk_name;`

標準 ANSI SQL 語法（跨平台通用核心）

-- 新增欄位
ALTER TABLE table_name ADD column_name datatype [CONSTRAINT ...];

-- 刪除欄位
ALTER TABLE table_name DROP COLUMN column_name;

-- 修改欄位型別
ALTER TABLE table_name ALTER COLUMN column_name datatype;  -- SQL Server
ALTER TABLE table_name MODIFY column_name datatype;        -- MySQL/Oracle

實戰範例：Persons 表完整生命週期

1. 初始建表

CREATE TABLE persons (
    id INT PRIMARY KEY,
    lastname VARCHAR(50),
    firstname VARCHAR(50),
    address VARCHAR(100),
    city VARCHAR(50)
);

2. 新增 birthday 欄位

ALTER TABLE persons ADD birthday DATE;

3. 修改 birthday 型別為 YEAR

-- MySQL
ALTER TABLE persons MODIFY birthday YEAR;

-- SQL Server
ALTER TABLE persons ALTER COLUMN birthday SMALLINT;  -- 無 YEAR 型別，用 SMALLINT 存年份

4. 刪除 birthday 欄位

ALTER TABLE persons DROP COLUMN birthday;

進階操作：自增欄位種子重置

SQL Server

-- 新增自增欄位
ALTER TABLE t ADD new_id INT IDENTITY(1,1);

-- 重置種子值（下一筆從 101 開始）
DBCC CHECKIDENT ('t', RESEED, 100);

MySQL

-- 修改自增起始值
ALTER TABLE t AUTO_INCREMENT = 101;

避坑指南與最佳實踐

大表結構變更：MySQL 5.6+ 支援 ALGORITHM=INPLACE、LOCK=NONE 線上 DDL；SQL Server 企業版支援線上索引操作；Oracle 12c+ 支援 DBMS_REDEFINITION。生產環境大表必須評估鎖表風險。
型別變更數據截斷：VARCHAR(100) → VARCHAR(50) 可能截斷數據，先用 MAX(LEN(col)) 檢查。
刪除欄位前檢查相依性：視圖、存儲過程、觸發器、外鍵可能引用該欄位，需先移除相依物件。
索引命名規範：建議 idx_表名_欄位名、uk_表名_欄位名、fk_表名_參考表名，利於維護識別。
備份優先：任何結構變更前，務必備份資料庫或建立快照（LVM/ZFS/雲端快照）。