MySQL AUTO_INCREMENT 自增列重置完全指南：DELETE、TRUNCATE、ALTER TABLE 三大方案對比

自增列歸零/指定起始值：三種方式、兩大陷阱、一條最佳建議

業務常需求：測試資料清理後自增 ID 歸零、遷移指定起始 ID、修復跳號。MySQL 提供 `DELETE`、`TRUNCATE`、`ALTER TABLE` 三種路徑，行為差異巨大 (特別是 InnoDB vs MyISAM)。本文實測對比，給出生產環境最佳選擇。

方案對比總表

關鍵行為細節

1. ALTER TABLE ... AUTO_INCREMENT = N

• **只能增大，不能縮小** (相對當前最大值/下一自增值)。
• InnoDB：若 N ≤ 當前下一自增值，**靜默忽略**，不報錯也不生效。
• MyISAM：若 N ≤ 最大值，自動調整為 `MAX(id)+1`。
• 適用場景：預留 ID 空間 (如分庫分表預留範圍)、遷移指定起始 ID。

2. TRUNCATE TABLE 行為細節

• **DDL 操作**，隱含 Commit，不可 Rollback。
• **不觸發 ON DELETE 觸發器**。
• **重置自增計數器為 1** (MySQL 5.0.13+ 保證 InnoDB/MyISAM 一致)。
• **外鍵約束**：被引用表無法 TRUNCATE (需先刪除 FK 或 `SET FOREIGN_KEY_CHECKS=0`)。
• **權限**：需 `DROP` 權限 (非 `DELETE`)。

3. 大表刪除效能實測 (90 萬行)

• `DELETE FROM big_table;` → **10 分鐘+** (產生大量 Undo Log、Binlog、索引維護)。
• `TRUNCATE TABLE big_table;` → **< 1 秒** (釋放 Tablespace/重建 .ibd)。
• 結論：**大表清理唯一可行方案是 TRUNCATE**。

進階：指定自增起始值最佳實踐

-- 方案 A: TRUNCATE + ALTER (最快、最標準)
TRUNCATE TABLE tbl;
ALTER TABLE tbl AUTO_INCREMENT = 10000;

-- 方案 B: 僅 ALTER (保留資料、調整起始值)
ALTER TABLE tbl AUTO_INCREMENT = 10000;  -- 僅當 10000 > 當前下一自增值時生效

-- 方案 C: 插入佔位行再刪除 (不推薦，產生碎片)
INSERT INTO tbl (id) VALUES (9999); DELETE FROM tbl WHERE id = 9999;

避坑檢查清單

• ☐ 確認表無被其他表外鍵引用 (或暫時關閉 FK 檢查)。
• ☐ 確認無 `ON DELETE` 觸發器邏輯依賴 (TRUNCATE 不觸發)。
• ☐ 確認 Binlog 格式 (ROW 模式下 TRUNCATE 記錄為 DDL，不影響主從同步)。
• ☐ 生產環境**必須備份**後再操作 (TRUNCATE/DROP 不可逆)。
• ☐ 權限驗證：執行帳號需 `DROP`、`ALTER`、`INDEX` 權限。

總結建議