萬盛學電腦網

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

MySQL Duplicate Entry 錯誤排查與解決:自增衝突、主鍵衝突、最大值耗盡全分析

Duplicate entry 'xxx' for key 'PRIMARY' 或 for key 3 是 MySQL 寫入失敗的高頻錯誤。本文系統分析三大根因:主鍵/唯一鍵值衝突、自增計數器異常、自增值耗盡,並提供排查步驟與解決方案。

錯誤訊息解讀

ERROR 1062 (23000): Duplicate entry 'value' for key 'key_name'
  • key_name = 'PRIMARY':主鍵衝突
  • key_name = 'uk_xxx':唯一索引衝突
  • key_name = '3':第 3 個索引(含主鍵)衝突,用 SHOW INDEX FROM tbl 確認對應索引

根因一:業務邏輯導致真實重複

  • 並發插入未做去重/鎖控制
  • 業務鍵(訂單號、手機號、身份證)重複提交
  • 分庫分表路由錯誤導致同鍵落入不同分片

解決

  • 應用層冪等設計:INSERT IGNORE、ON DUPLICATE KEY UPDATE、REPLACE INTO
  • 分佈式鎖/本地鎖序列化關鍵寫入
  • 業務鍵加唯一索引,讓資料庫拒絕重複

根因二:自增計數器異常(最常見運維故障)

現象

表有自增主鍵 id,當前最大值 54,但 AUTO_INCREMENT=54(應為 55),下次插入嘗試用 54 衝突。

排查步驟(phpMyAdmin/命令列)

  1. 進入 phpMyAdmin,選擇資料庫 → 表
  2. 找到自增欄位(有 auto_increment 標識)
  3. 點擊「瀏覽」,按該欄位**降序排序**,記下**當前最大值**
  4. 點擊「操作」標籤,查看 AUTO_INCREMENT 值
  5. 若 AUTO_INCREMENT ≤ 最大值,即為原因

修復

-- 設置為 最大值 + 1
ALTER TABLE table_name AUTO_INCREMENT = 55;

或在 phpMyAdmin「操作」頁直接修改 AUTO_INCREMENT 輸入框執行。

常見觸發場景

  • 手動 INSERT 指定了大 ID,後續自增未跟進
  • TRUNCATE TABLE 重置自增,但手動插入舊 ID
  • 主從複製中斷、mysqldump 導入不含 AUTO_INCREMENT 值
  • 崩潰恢復後自增計數器持久化丟失(MySQL 8.0 前不持久化)

根因三:自增值耗盡(數據類型上限)

類型有符號上限無符號上限建議用途
TINYINT127255極小枚舉/狀態
SMALLINT32,76765,535小型字典表
MEDIUMINT8,388,60716,777,215中型業務表
INT2,147,483,6474,294,967,295**通用推薦**
BIGINT9.22e181.84e19超大表/分布式 ID

案例:MEDIUMINT UNSIGNED 上限 16,777,215,業務增長超出導致無法插入。

解決

  1. 修正異常大 ID 數據(如測試數據寫入生產)
  2. 修改欄位類型:ALTER TABLE t MODIFY id BIGINT UNSIGNED AUTO_INCREMENT;(大表需 pt-osc/gh-ost)
  3. 重設計 ID 生成策略:雪花算法、UUID、分段號段模式

預防與監控體系

  • 監控自增消耗率:(AUTO_INCREMENT - 1) / MAX_UNSIGNED_VALUE * 100%,閾值 80% 告警
  • 定期檢查AUTO_INCREMENT 與 MAX(id) 差值:差值 > 1000 時告警
  • 禁止業務手動指定自增 ID:代碼審查攔截
  • 使用無符號類型:INT UNSIGNED 翻倍容量
  • 新表預設 BIGINT:一勞永逸,空間成本可忽略

監控查詢範例

SELECT 
  TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE,
  AUTO_INCREMENT,
  CASE DATA_TYPE
    WHEN 'tinyint' THEN 255
    WHEN 'smallint' THEN 65535
    WHEN 'mediumint' THEN 16777215
    WHEN 'int' THEN 4294967295
    WHEN 'bigint' THEN 18446744073709551615
  END AS max_unsigned,
  ROUND(AUTO_INCREMENT / 
    CASE DATA_TYPE
      WHEN 'tinyint' THEN 255
      WHEN 'smallint' THEN 65535
      WHEN 'mediumint' THEN 16777215
      WHEN 'int' THEN 4294967295
      WHEN 'bigint' THEN 18446744073709551615
    END * 100, 2) AS pct_used
FROM INFORMATION_SCHEMA.COLUMNS
WHERE EXTRA = 'auto_increment'
  AND TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND AUTO_INCREMENT IS NOT NULL
ORDER BY pct_used DESC;

建立完善的自增監控與冪等寫入模式,能從根源上消除 Duplicate entry 導致的業務中斷與數據不一致風險。

copyright © 萬盛學電腦網 all rights reserved