萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL ALTER TABLE 增刪改欄位、索引、主鍵、約束完整語法速查 (MySQL/SQL Server/Oracle/標準 SQL)

跨資料庫 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];

欄位操作對照表

操作MySQLSQL ServerOracle標準 SQL
新增欄位ADD COLUMN col INTADD col INTADD (col INT)ADD COLUMN col INT
刪除欄位DROP COLUMN colDROP COLUMN colDROP COLUMN colDROP COLUMN col
修改欄位名CHANGE old new INTsp_rename 't.old', 'new', 'COLUMN'RENAME COLUMN old TO newRENAME 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 0ADD CONSTRAINT df DEFAULT 0 FOR colMODIFY (col DEFAULT 0)ALTER COLUMN col SET DEFAULT 0
刪除預設值ALTER COLUMN col DROP DEFAULTDROP CONSTRAINT df_nameMODIFY (col DEFAULT NULL)ALTER COLUMN col DROP DEFAULT

索引與約束操作對照

操作MySQLSQL ServerOracle
加主鍵ADD PRIMARY KEY (col)ADD CONSTRAINT pk PRIMARY KEY (col)ADD CONSTRAINT pk PRIMARY KEY (col)
刪主鍵DROP PRIMARY KEYDROP CONSTRAINT pkDROP 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 tblDROP INDEX idx ON tDROP 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 fkDROP CONSTRAINT fkDROP CONSTRAINT fk
重命名表RENAME TABLE old TO newsp_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 等無鎖、無拷貝、毫秒級完成。

避坑清單

  1. 型別收縮前必檢查最大長度:SELECT MAX(LENGTH(col)) FROM tbl。
  2. 刪除欄位前檢視相依性:視圖、SP、觸發器、應用代碼、外鍵。
  3. 外鍵循環依賴:SET FOREIGN_KEY_CHECKS=0 臨時關閉,操作完開啟。
  4. 字符集轉換:ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 會重建表,大表必用線上工具。
  5. 主鍵變更:需先 DROP PRIMARY KEY 再 ADD PRIMARY KEY,期間表無主鍵,極度危險,建議維護窗口操作。
copyright © 萬盛學電腦網 all rights reserved