萬盛學電腦網

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

MySQL 自增 ID 耗盡導致 Duplicate Entry 1062 錯誤:根因、重現與解決方案

線上業務突然無法寫入,報錯 #1062 Duplicate entry '2147483647' for key 'PRIMARY'。根因是 INT 類型自增主鍵達到有符號 32 位整數上限 2,147,483,647,後續隱式插入無法獲取合法 ID。本文詳細分析故障機制、重現步驟與多種修復方案。

故障重現

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');
-- ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

核心機制解析

  • INT 有符號範圍:-2,147,483,648 ~ 2,147,483,647
  • AUTO_INCREMENT 計數器單調遞增,不回收已用值
  • 顯式插入大值會推進計數器,後續隱式插入嘗試同值 → 主鍵衝突
  • UNSIGNED INT 上限為 4,294,967,295,僅延緩問題

四種修復方案(按推薦度排序)

方案一:修改列類型為 BIGINT(徹底根治)

ALTER TABLE test MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT;
-- 上限 9,223,372,036,854,775,807,實際無窮
  • ✅ 一勞永逸,無業務代碼修改
  • ⚠️ 大表需 Online DDL(5.6+)或 pt-online-schema-change
  • ⚠️ 應用層 ID 類型需兼容(Java Long、Go int64、JS Number 安全)

方案二:重置自增值(臨時緩解)

-- 刪除毒藥數據
DELETE FROM test WHERE id = 2147483647;

-- 重置自增計數器
ALTER TABLE test AUTO_INCREMENT = 1;
-- 或具體值
ALTER TABLE test AUTO_INCREMENT = (SELECT MAX(id)+1 FROM test);
  • ⚠️ 僅在無業務數據缺口時可用
  • ⚠️ 若已有接近上限的正常數據,重置無效

方案三:邏輯重建表(大表適用)

CREATE TABLE test_new LIKE test;
ALTER TABLE test_new MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT;
INSERT INTO test_new SELECT * FROM test;
RENAME TABLE test TO test_old, test_new TO test;
  • 配合 pt-online-schema-change 零鎖遷移

方案四:業務層規避(不改表結構)

  • 應用層檢測 ID 逼近上限時告警/熔斷
  • 分庫分表:按業務維度拆分,單表 ID 空間充足
  • 改用 UUID/Snowflake ID 作為主鍵(需架構重構)

預防體系建設

  1. 表結構審核:新建表主鍵預設 BIGINT,納入 SQL 規範檢查
  2. 監控告警:SELECT MAX(id)/4294967295 * 100 AS pct FROM table,超 80% 告警
  3. 權限管控:收回應用賬號 INSERT ... VALUES (id, ...) 顯式指定 ID 權限
  4. 工具攔截:中間件/Proxy 層識別並阻斷顯式插入自增列的 SQL

延伸思考

  • INT(11) 中的 11 僅為顯示寬度,不影響存儲範圍
  • InnoDB 隱式主鍵(無顯式 PK 時)同樣受 6 字節限制(~281 兆行)
  • 分布式 ID 生成器 雪花算法 等方案可從根源避免自增衝突

自增 ID 耗盡是「遲來的災難」,早規劃 BIGINT、建監控、禁顯式插入,比事後修復成本低千倍。

copyright © 萬盛學電腦網 all rights reserved