MySQL中INSERT ON DUPLICATE KEY UPDATE导致ID不连续的解决方法

这其实是MySQL InnoDB引擎（默认存储引擎）的正常行为——当执行INSERT ... ON DUPLICATE KEY UPDATE时，即便最终触发的是更新而非插入，自增计数器依然会被递增。原因是InnoDB在语句执行初期就会为可能的插入操作预分配自增ID，不管后续是否真的插入了新行，预分配的ID不会被回滚，所以就出现了ID断号但行数没增加的情况。

下面是几种应对方案，你可以根据业务需求选择：

方案1：放弃依赖自增ID的连续性（最推荐） #

自增ID的核心作用是给每行数据提供唯一标识，而非生成连续的序列。除了INSERT ... ON DUPLICATE KEY UPDATE，事务回滚、批量插入部分失败等场景也会导致ID断号，这是数据库设计的正常现象。如果业务逻辑不是必须依赖连续ID，建议接受这种情况，不要强行修改——强行维护连续ID反而可能引入并发问题或额外的性能开销。

方案2：改用"先查询再操作"的逻辑 #

如果业务确实需要ID保持连续，可以放弃INSERT ... ON DUPLICATE KEY UPDATE语法，拆分为两步操作：

1. 先查询是否存在符合唯一键（date, pair, action）的记录
2. 根据查询结果，不存在则执行插入，存在则执行更新

示例代码如下：

-- 第一步：查询是否存在目标记录
SELECT id FROM import_xlsx 
WHERE date = '2018-05-14 13:58:54' 
  AND pair = 'ETHUSDT' 
  AND action = 'SELL';

-- 第二步：根据查询结果执行对应操作
-- 如果不存在，执行插入
INSERT INTO import_xlsx (date, pair, action, volume, order_price, trading_price, status)
VALUES('2018-05-14 13:58:54', 'ETHUSDT', 'SELL', '0.1', '725.0', '724.95', 'Filled');

-- 如果存在，执行更新
UPDATE import_xlsx 
SET status = 'Filled', volume = '0.1', order_price = '725.0', trading_price = '724.95'
WHERE date = '2018-05-14 13:58:54' 
  AND pair = 'ETHUSDT' 
  AND action = 'SELL';

⚠️ 注意：这种方式需要考虑并发场景，建议在事务中执行这两步操作，或者使用行锁来避免竞态问题（比如在查询时加FOR UPDATE）。

方案3：手动重置自增计数器（仅用于一次性修复） #

如果只是想让后续新插入的ID回到连续状态，可以手动重置表的自增起始值：

-- 先查询当前表中最大的ID值
SELECT MAX(id) FROM import_xlsx;

-- 将自增起始值设置为最大ID+1（替换[max_id+1]为实际数值）
ALTER TABLE import_xlsx AUTO_INCREMENT = [max_id+1];

⚠️ 注意：这个操作只能修复当前的断号问题，之后如果再执行INSERT ... ON DUPLICATE KEY UPDATE，依然会出现ID断号。而且在高并发写入的场景下执行这个命令可能会引发锁问题，所以只适合作为一次性的修复手段，不适合常规使用。

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