MySQL AUTO_INCREMENT 列 DELETE 全表數據後的行為差異：MyISAM vs InnoDB

MySQL AUTO_INCREMENT 列 DELETE 全表數據後的行為差異：MyISAM vs InnoDB

帶 AUTO_INCREMENT 主鍵的表執行 DELETE FROM tbl 清空數據後，再次插入時自增 ID 的起始值在 MyISAM 與 InnoDB 表現截然不同，且 InnoDB 重啟後會重置計數器導致主鍵衝突風險。本文通過實驗驗證並給出生產規避建議。

實驗環境

• OS：RHEL 6.4 x86_64
• MySQL：5.6.19
• 測試表：id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

一、MyISAM 表：安全穩定

CREATE TABLE t7 (id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY(id)) ENGINE=MyISAM;
INSERT INTO t7(name) VALUES('GZ'),('SH'),('BJ'),('SZ'),('HZ');  -- id 1-5
DELETE FROM t7;
SHOW CREATE TABLE t7;  -- AUTO_INCREMENT=6
INSERT INTO t7(name) VALUES('NJ');  -- id=6 ✅
-- 重啟 mysqld 後 AUTO_INCREMENT 仍為 6，續號安全

結論：MyISAM 將 AUTO_INCREMENT 持久化在 .MYI 索引文件頭部，**重啟不重置**，極其安全。

二、InnoDB 表：存在重啟重置坑

CREATE TABLE t2 (id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY(id)) ENGINE=InnoDB;
INSERT INTO t2(name) VALUES('GZ'),('SH'),('BJ'),('SZ'),('HZ');  -- id 1-5
DELETE FROM t2;
INSERT INTO t2(name) VALUES('NJ');  -- id=6 ✅ (未重啟時正常遞增)

-- 關鍵差異：重啟 mysqld 後
INSERT INTO t2(name) VALUES('FJ');  -- id=1 ❌ 重置導致主鍵衝突風險！

原理深度解析

• MyISAM：計數器存於索引文件，持久化可靠
• InnoDB（≤5.7）：計數器僅保持在內存（dict_table_t::auto_inc），**不刷盤**，重啟時通過 SELECT MAX(id)+1 重新計算 → 表為空則歸 1
• InnoDB 8.0+ 引入 innodb_autoinc_persistent（默認 ON）將計數器寫入 Redo Log，重啟後可恢復，但仍建議避免依賴

生產環境規避指南

1. 清空表首選 TRUNCATE：TRUNCATE TABLE t2; 會重置自增計數器且釋放空間，行為一致且高效
2. 業務主鍵勿暴露遞增語義：使用 UUID、雪花算法 ID 或業務無關代理鍵
3. 若必須 DELETE：刪除後立即 ALTER TABLE t2 AUTO_INCREMENT = N; 手動校準（N 為業務預期下一值）
4. 監控報警：對自增列接近上限（INT 21億/BIGINT 922京）設置報警

對比總結表