ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_spec:
ADD [COLUMN] col_def [FIRST | AFTER col_name]
| ADD INDEX [idx_name] (col_list)
| ADD PRIMARY KEY (col_list)
| ADD UNIQUE [idx_name] (col_list)
| ALTER [COLUMN] col_name {SET DEFAULT val | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name col_def
| MODIFY [COLUMN] col_def
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP INDEX idx_name
| RENAME [AS] new_tbl_name
| table_options
SELECT/INSERT/DELETE/UPDATE/CREATE/DROP 權限| 操作 | SQL 範例 |
|---|---|
| 重命名表 | ALTER TABLE t1 RENAME t2; |
| 修改列類型（同名） | ALTER TABLE t2 MODIFY a TINYINT NOT NULL; |
| 修改列類型並重命名 | ALTER TABLE t2 CHANGE b c CHAR(20); |
| 新增列到指定位置 | ALTER TABLE t2 ADD d TIMESTAMP FIRST;ALTER TABLE t2 ADD e INT AFTER c; |
| 新增索引/主鍵 | ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a); |
| 刪除列 | ALTER TABLE t2 DROP COLUMN c; |
| 新增 AUTO_INCREMENT 列 | ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c); |
| 修改默認值 | ALTER TABLE t2 ALTER COLUMN d SET DEFAULT CURRENT_TIMESTAMP; |
| 刪除索引 | ALTER TABLE t2 DROP INDEX idx_name; |
| 刪除主鍵 | ALTER TABLE t2 DROP PRIMARY KEY; |
| 更改存儲引擎 | ALTER TABLE t2 ENGINE=InnoDB; |
IGNORE：唯一鍵衝突時保留首行刪除其餘；不加則回滾ADD/ALTER/DROP/CHANGE（MySQL 擴展）CHANGE 需指定舊名、新名、類型；MODIFY 僅改類型FOREIGN KEY/CHECK/REFERENCES 僅語法兼容，不生效RENAME 時不建臨時表，直接改文件名mysql_info() -- 返回複製行數、刪除重複行數
