LOAD DATA INFILE执行时自增主键出现Duplicate entry异常求助

原因分析 #

1. 自增计数器与表中实际ID不一致 #

当前表的auto_increment值为745448895，但报错的重复ID745285936小于该值，说明表中已存在这个ID，而自增计数器未与实际数据同步。常见触发场景：

• 手动执行过指定id值的INSERT语句，插入的ID小于当前auto_increment，且未更新自增计数器；
• 数据库重启后，InnoDB会依据表中最大ID重置auto_increment，但重启后存在手动插入更小ID的操作；
• 误执行ALTER TABLE urls AUTO_INCREMENT = ...，将自增计数器设置为小于现有最大ID的值。

2. 并发操作冲突（低概率） #

尽管每秒仅10次请求，但LOAD DATA INFILE与其他业务插入操作并发时，在特定隔离级别或自增锁机制下，可能出现ID分配竞争。不过InnoDB的自增锁机制通常会保证ID唯一性，此场景概率较低。

3. 主从复制配置问题（若存在主从架构） #

如果使用主从复制：

• 主从库的auto_increment_increment/auto_increment_offset参数配置不一致，会导致双方生成的ID范围重叠；
• 从库允许写操作，直接写入的ID同步到主库后引发冲突。

4. 备份恢复异常 #

近期若进行过备份恢复，备份文件中的AUTO_INCREMENT设置与实际数据不匹配，恢复后自增计数器与表中现有ID范围重叠。

解决建议 #

1. 修复自增计数器与数据的一致性 #

• 先查询表中最大ID：

  SELECT MAX(id) FROM urls;
  

• 将auto_increment设置为最大ID+1，确保后续自增ID不与现有ID冲突：

  -- 先计算MAX(id)+1的值，比如假设结果为745448896
  ALTER TABLE urls AUTO_INCREMENT = 745448896;
  

  注：部分MySQL版本不支持ALTER语句嵌套子查询，需先单独计算最大值再加1后执行ALTER。

2. 禁止手动指定ID的插入操作 #

排查应用代码和操作日志，移除所有手动指定id列的INSERT语句。若业务必须手动插入ID，需确保插入的ID大于当前auto_increment值。

3. 优化批量插入操作 #

• 若业务允许，执行LOAD DATA INFILE前暂时停止其他插入类业务操作，避免并发冲突；
• 将大批次数据拆分为多个小批次插入，减少锁持有时间，降低竞争概率。

4. 检查主从配置（若适用） #

• 统一主从库的auto_increment_increment和auto_increment_offset参数；
• 将从库设置为只读模式（read_only=1），禁止直接在从库写入数据。

5. 规范备份恢复流程 #

• 备份时确保表结构中的AUTO_INCREMENT值大于备份数据的最大ID；
• 恢复完成后立即执行SELECT MAX(id) FROM urls和SHOW CREATE TABLE urls，验证自增计数器与实际数据的一致性。

内容的提问来源于stack exchange，提问作者ajs