萬盛學電腦網

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

MySQL ALTER TABLE 完整語法指南:修改表結構、索引、欄位與表選項

ALTER TABLE 語法總覽

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 表可調大 myisam_sort_buffer_size 加速索引重建。

權限需求

需具備該表的 ALTER、INSERT、CREATE 權限。

IGNORE 選項

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;
  • 刪除預設值且欄位可為 NULL → 新預設值為 NULL。
  • 欄位 NOT NULL 且刪除預設值 → MySQL 依型別隱含賦予預設值(0、空字串等)。

表選項與排序

  • ORDER BY col:按指定欄位重排表物理順序(僅一次性效果,後續寫入不保持)。適合靜態資料或主要查詢順序固定的表。
  • table_options:修改 ENGINE、AUTO_INCREMENT、COMMENT、ROW_FORMAT 等。

MyISAM 批量索引優化

  • ALTER TABLE ... DISABLE KEYS:停止更新非唯一索引。
  • ALTER TABLE ... ENABLE KEYS:重建所有非唯一索引(特殊演算法,比逐筆插入快得多)。
  • 大量匯入資料前 DISABLE KEYS,匯入後 ENABLE KEYS,大幅提速。

外鍵與約束

  • FOREIGN KEY、CHECK、REFERENCES 子句僅為相容性保留,**僅 InnoDB 真正執行** ADD CONSTRAINT FOREIGN KEY ... REFERENCES ...。
  • InnoDB 不允許指定 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 = # 設定起始值。

copyright © 萬盛學電腦網 all rights reserved