ON DUPLICATE KEY UPDATE 导致自增ID跳跃式增长

1. 语法

INSERT INTO table_name VALUES(null,param,..) ON DUPLICATE KEY UPDATE param_name = VALUES(param_name);
1

2. 介绍

ON DUPLICATE KEY UPDATE 会根据主键或唯一索引检索当前记录是否已经存在，存在更新，不存在插入；
优先级：主键 > 唯一索引

• 当主键重复时，更新UPDATE后面的字段值，不重复插入新数据；
• 当主键不重复，唯一索引重复时，更新UPDATE后面的字段值，不重复插入新数据；
• 当表中不存在索引重复时，直接插入；

3. 存在问题

在执行ON DUPLICATE KEY UPDATE会发现，如果存在自增ID，默认情况下每次执行结束后，会发现ID跳跃式增长。

3.1 原因

mysql中的innodb_autoinc_lock_mode参数存在三种模式：0,1,2。

1. tradition（innodb_autoinc_lock_mode = 0）
   在这个模式下，在insert语句执行的时候，mysql会为当前语句添加auto_inc锁，只有当前语句执行完毕后，才会释放auto_inc锁，这种方法虽然保证了语句在执行过程中的数据安全性，但是在高并发的情况下，会影响效率。
2. consecutive（innodb_autoinc_lock_mode = 1）默认模式
   在执行批量insert操作的时候，会一次生成多个连续的自增ID值，这也导致了自增ID的跳跃式增长，相比于tradition模式，提高了并发效率。
3. interleaved（innodb_autoinc_lock_mode = 2）
   在这种模式下，是每执行一个insert语句同时会获得一个自增ID值，同时取消了anto_inc锁，但是不能保证ID连续性。

4. 解决办法

4.1 拆分逻辑

将ON DUPLICATE KEY UPDATE语句从逻辑层面拆分为两步，先执行update操作，如果update操作影响语句为0，则执行insert操作。

4.2 修改

编辑/etc/my.cnf，修改innodb_autoinc_lock_mode=0;

4.3 修改auto_increment值

在ON DUPLICATE KEY UPDATE语句后执行
ALTER TABLE table_name auto_increment=1;，但是每次执行这个语句的时候，都会执行alter语句，会导致效率变低。

具体解决办法根据实际业务场景需要选择。