MySQL DDL 與 DML 快速參考：增刪改查、索引、外鍵一網打盡

整理 MySQL 常用結構變更與資料操作語句，涵蓋欄位管理、索引維護、外鍵約束、資料表重命名等高頻場景，附帶實戰範例與官方文檔重點摘錄，適合開發與 DBA 日常查閱。

欄位與資料表結構變更

1. 新增欄位

ALTER TABLE tablename ADD colname type NOT NULL DEFAULT '0';

範例：ALTER TABLE mmanapp_mmanmedia ADD appid_id INT NOT NULL DEFAULT 372;

2. 刪除欄位

ALTER TABLE tablename DROP COLUMN colname;

3. 修改欄位型別/重命名

ALTER TABLE t1 CHANGE a b INTEGER;
ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
ALTER TABLE infos CHANGE list list TINYINT NOT NULL DEFAULT '0';

4. 重命名資料表

ALTER TABLE t1 RENAME t2;

索引管理全攻略

-- 新增普通索引
ALTER TABLE tablename ADD INDEX idx_name (col1, col2);

-- 新增主鍵
ALTER TABLE tablename ADD PRIMARY KEY (id);

-- 新增唯一索引
ALTER TABLE tablename ADD UNIQUE uk_card (cardnumber);

-- 刪除索引
ALTER TABLE tablename DROP INDEX idx_name;

外鍵約束操作

新增外鍵（完整語法）

ALTER TABLE yourtablename
ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}];

範例：ALTER TABLE mmanapp_mmanmedia ADD CONSTRAINT fk_mdappid FOREIGN KEY (appid_id) REFERENCES app(id);

刪除外鍵

ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

注意：若建立時未指定 CONSTRAINT 名稱，需用 SHOW CREATE TABLE 查看系統生成的符號名（如 0_38775）。

外鍵相關進階操作

• 查看表外鍵：SHOW CREATE TABLE tbl_name 或 SHOW TABLE STATUS FROM db_name LIKE 'tbl_name'（Comment 欄位顯示）
• 匯入轉儲檔忽略外鍵檢查：

  SET FOREIGN_KEY_CHECKS = 0;
  SOURCE dump_file.sql;
  SET FOREIGN_KEY_CHECKS = 1;

• 刪除被引用表：需先 SET FOREIGN_KEY_CHECKS = 0，否則報錯 1005 (errno 150)
• 重建被引用表：結構必須完全符合外鍵定義（欄位名、型別、索引）

常見陷阱提醒

1. 主鍵與唯一索引重複建立（如範例中 PRIMARY KEY + UNIQUE KEY 同一欄位）
2. 低基數欄位建索引（如 class_status 只有兩值）
3. 描述型長字串欄位建索引（class_desc VARCHAR(2048)）
4. 聯合索引順序錯誤導致前綴索引失效

最佳實踐原則

• 主鍵優先使用 BIGINT AUTO_INCREMENT
• 枚舉/狀態欄位用 TINYINT 而非 CHAR/VARCHAR
• 定期 OPTIMIZE TABLE 整理碎片（高頻 DELETE/UPDATE 表）
• 大字段（圖片/文件）僅存路徑，不存內容