使用INSERT ... ON DUPLICATE KEY UPDATE时自增ID跳号问题求助

我使用以下SQL语句存储用户搜索结果：

$sql = "INSERT INTO search_data (SEARCH, COUNT) VALUES (?,1) ON DUPLICATE KEY UPDATE COUNT = COUNT + 1";

我的search_data表包含3个字段：

• ID：自增、唯一、主键
• SEARCH：唯一约束
• COUNT：整数类型

遇到的问题是更新数据时自增ID会跳号，例如：

1. 搜索hii → ID=1
2. 搜索hello → ID=2
3. 再次搜索hii → 触发更新操作
4. 新增搜索时ID直接变为4，跳过了3，实际ID序列为1、2、4，而非预期的1、2、3

这是MySQL的正常机制：执行INSERT ... ON DUPLICATE KEY UPDATE时，数据库会先尝试插入新行，此时自增ID已被预分配；当检测到唯一键冲突转而执行更新后，预分配的ID不会回滚，下一次插入就会跳过这个已占用的ID，导致序列断号。

根据业务需求选择对应的处理方式：

1. 拆分语句保证ID连续
   先查询目标搜索词是否存在：

   SELECT COUNT FROM search_data WHERE SEARCH = ?;
   

   • 若存在，执行更新：

     UPDATE search_data SET COUNT = COUNT + 1 WHERE SEARCH = ?;
     

   • 若不存在，执行插入：

     INSERT INTO search_data (SEARCH, COUNT) VALUES (?, 1);
     

   这种方式仅在插入时分配自增ID，能保证序列连续，但需注意并发场景下的竞态问题，可通过事务或行锁避免重复插入。

2. 接受ID断号，保留原语句
   自增ID的核心作用是唯一标识数据行，而非保证序列连续。INSERT ... ON DUPLICATE KEY UPDATE具备原子性，能更好地处理并发场景，若业务对ID连续性没有强制要求，建议保留原语句，忽略断号问题。

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