ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_spec:
ADD [COLUMN] create_definition [FIRST | AFTER col_name]
| ADD [COLUMN] (create_definition, ...)
| ADD INDEX [index_name] (index_col_name, ...)
| ADD PRIMARY KEY (index_col_name, ...)
| ADD UNIQUE [index_name] (index_col_name, ...)
| ADD FULLTEXT [index_name] (index_col_name, ...)
| ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name, ...) [reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name create_definition [FIRST | AFTER col_name]
| MODIFY [COLUMN] create_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP INDEX index_name
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col
| table_options
RENAME 外,總是建立臨時表(即使僅改欄位名),未來版本將優化。myisam_sort_buffer_size 加速索引重建。需具備該表的 ALTER、INSERT、CREATE 權限。
MySQL 擴展。控制唯一鍵衝突處理:
IGNORE:衝突時回滾放棄。IGNORE:保留第一行,刪除後續重複行。ALTER TABLE t1 RENAME t2;
-- 改型別不改名(需指定舊名與新名相同)
ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
-- 3.22.16a+ 可用 MODIFY 簡化
ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
ALTER TABLE t1 ADD d TIMESTAMP;
ALTER TABLE t1 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST;
ALTER TABLE t1 ADD e VARCHAR(50) AFTER d;
-- 新增索引
ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
-- 刪除索引
ALTER TABLE t2 DROP INDEX idx_name;
ALTER TABLE t2 DROP PRIMARY KEY; -- 無 PK 時刪除第一個 UNIQUE
ALTER TABLE t2 DROP COLUMN c;
DROP COLUMN,請用 DROP TABLE。ALTER TABLE t1 ALTER COLUMN col_name SET DEFAULT 'new_default';
ALTER TABLE t1 ALTER COLUMN col_name DROP DEFAULT;
ORDER BY col:按指定欄位重排表物理順序(僅一次性效果,後續寫入不保持)。適合靜態資料或主要查詢順序固定的表。table_options:修改 ENGINE、AUTO_INCREMENT、COMMENT、ROW_FORMAT 等。ALTER TABLE ... DISABLE KEYS:停止更新非唯一索引。ALTER TABLE ... ENABLE KEYS:重建所有非唯一索引(特殊演算法,比逐筆插入快得多)。DISABLE KEYS,匯入後 ENABLE KEYS,大幅提速。FOREIGN KEY、CHECK、REFERENCES 子句僅為相容性保留,**僅 InnoDB 真正執行** ADD CONSTRAINT FOREIGN KEY ... REFERENCES ...。index_name。CREATE TABLE t1 (a INTEGER, b CHAR(10));
ALTER TABLE t1 RENAME t2;
ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
ALTER TABLE t2 ADD d TIMESTAMP;
ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
ALTER TABLE t2 DROP COLUMN c;
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c);
AUTO_INCREMENT 欄位必須被索引且 NOT NULL。新增時自動填充序列值,可用 SET INSERT_ID=# 或 AUTO_INCREMENT = # 設定起始值。