MySQL Auto Increment 故障排查：當自增 ID 觸頂整數上限

在一個月黑風高的夜晚，值班工程師被一陣急促的告警短信驚醒。資料庫的某張表出現了詭異現象：查詢操作一切正常，但所有寫入操作卻全部失敗。經過緊急排查，發現罪魁禍首是一筆「問題資料」——有人在插入時顯式指定了自增主鍵為 2147483647（有號 32 位整數的最大值），導致後續隱式插入無法獲取合法的主鍵值。

問題複現

我們可以建立一個測試表來模擬該場景：

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

插入那筆「毒藥」資料：

INSERT INTO test (id, name) VALUES (2147483647, 'x');

此後，嘗試正常插入：

INSERT INTO test (name) VALUES ('y');

資料庫立即拋出錯誤：

#1062 – Duplicate entry '2147483647' for key 'PRIMARY'

根因分析

InnoDB 在內存中維護一個自增計數器，每次插入新資料時，計數器更新為 MAX(id) + 1。當有人強行插入了 INT 上限值，計數器試圖遞增到 2147483648，卻超出了有號 32 位整數的表示範圍，導致計數器「卡死」在最大值，後續插入只能不斷嘗試同一個已存在的主鍵，從而引發主鍵衝突。

解決方案對比

方案一：ALTER TABLE 重置自增值

ALTER TABLE test AUTO_INCREMENT = 123;

優點：直觀、標準。
缺點：MySQL 執行 ALTER TABLE 實際上會重建整張表，若資料量大，鎖表時間極長，業務影響巨大。

方案二：業務層顯式指定 ID

採用 SELECT MAX(id) ... FOR UPDATE 獲取最大值後，應用層自行計算並顯式插入。
缺點：侵入業務代碼，維護成本高，且並發性能差。

方案三：觸發器模擬序列

CREATE TABLE seq (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) Engine=InnoDB;

DELIMITER |;
CREATE TRIGGER test_seq BEFORE INSERT ON test
FOR EACH ROW BEGIN
  INSERT INTO seq (created) VALUES (NOW());
  SET NEW.id = LAST_INSERT_ID();
END; |;

利用獨立序列表 + 觸發器繞過原表自增限制。雖然透明度較好，但引入了額外寫入開銷與觸發器複雜度。

方案四：重啟 MySQL 服務（最簡單有效）

經同事 @linux流浪貓 提點，查閱官方文檔發現：InnoDB 的自增計數器**僅存在於內存中**，不持久化到磁碟。因此，刪除問題資料後，重啟 MySQL 服務即可自動重置計數器。

If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.

…

A server restart also cancels the effect of the AUTO_INCREMENT = N table option in CREATE TABLE and ALTER TABLE statements, which you can use with InnoDB tables to set the initial counter value or alter the current counter value.

這招「踏破鐵鞋無覓處，得來全不費工夫」，零停機（若有主從可切換）、零代碼變更、零鎖表風險，是處理此類故障的最佳實踐。

預防建議

• 嚴格控制生產環境的顯式主鍵插入權限，避免人為注入極大值。
• 對核心表的自增列類型改為 BIGINT，將上限推至 9223372036854775807，徹底消除觸頂風險。
• 建立監控告警，當自增值逼近類型上限 80% 時自動通知 DBA 擴容或清理。