MySQL批量插入非重复行时如何避免自增字段无故增长

嘿，这个问题问到点子上了——批量插入时不想让自增ID因为重复数据平白无故跳号，确实是很多人优化插入性能时的常见痛点。针对批量场景，这里有几个经过实践验证的方案，帮你解决问题：

方案1：用INSERT ... ON DUPLICATE KEY UPDATE（原子性强，适合高并发） #

这是最常用的方案，前提是你的表上已经有唯一索引（主键或唯一约束）来定义“重复行”。核心思路是：当插入的行触发唯一键冲突时，执行一个“无意义”的更新操作——既不会改变数据，也不会消耗自增ID。

示例SQL：

INSERT INTO myBigTable (value1, value2)
VALUES 
  ('val1_row1', 'val2_row1'),
  ('val1_row2', 'val2_row2'),
  -- 更多行...
ON DUPLICATE KEY UPDATE value1 = value1; -- 更新一个与原值相同的字段

这里的value1 = value1只是一个占位操作，MySQL会识别到没有实际数据变更，不会触发自增ID的增长，同时也会跳过重复行的插入。如果你的表有多个唯一键，要确保这个操作只会在你关心的重复场景下触发。

方案2：先筛选不存在的行再插入（零自增浪费，适合低并发） #

如果你的场景并发不高，可以先通过子查询筛选出目标表中不存在的记录，再批量插入。这种方式从根源上避免了重复行，自然不会消耗多余的自增ID。

示例SQL：

INSERT INTO myBigTable (value1, value2)
SELECT temp.value1, temp.value2
FROM (
  SELECT 'val1_row1' AS value1, 'val2_row1' AS value2
  UNION ALL
  SELECT 'val1_row2' AS value1, 'val2_row2' AS value2
  -- 更多行...
) AS temp
WHERE NOT EXISTS (
  SELECT 1 FROM myBigTable 
  WHERE myBigTable.value1 = temp.value1 -- 匹配唯一键条件
);

⚠️ 注意：这个方法不是原子操作，如果在子查询和插入之间有其他线程插入了相同数据，可能会出现唯一键冲突。如果是高并发场景，建议结合方案1使用，或者在操作时加表锁（但会影响性能）。

一定要避开的坑 #

• 别用INSERT IGNORE：它确实会跳过重复行，但会消耗自增ID——MySQL会为每一条重复行分配自增ID然后丢弃，导致ID跳号。
• 别用REPLACE INTO：它的逻辑是先删除重复行再插入新行，不仅会消耗自增ID，还可能删除你想保留的旧数据，甚至触发外键关联的删除操作，风险极大。

关键前提 #

所有方案的核心前提是：你的表必须有唯一索引（主键或唯一约束）。没有唯一索引的话，MySQL根本无法判断什么是“重复行”，上述所有方法都不生效。

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