ON DUPLICATE KEY UPDATE 导致mysql自增主键ID跳跃增长

迈不过友情╰ 2024-04-20 11:59 14阅读 0赞

### 一. 问题点: ###

1.  如果mysql表只是设置了联合主键且不包含自增的id, 则使用ON DUPLICATE KEY UPDATE不会有问题
2.  如果mysql表设置了自增主键id, 则使用ON DUPLICATE KEY UPDATE可能会造成自增id跳跃增长

### 二. 解决: ###

##### 1、从项目代码逻辑出发 #####

可以改变代码逻辑，变成先执行update方法，然后判断返回值是否等于0，如果返回值等于0则证明没有数据变动，否则执行insert方法。

![在这里插入图片描述][e618a935f38f44c79f3d20b74e314de1.png]

![在这里插入图片描述][5743bf3f6fef49e7928ab3a0e4c87711.png]

这样可以解决问题，但是从之前的一次sql变为了两次，会导致效率低下。

##### 2、从问题出发：ON DUPLICATE key update会在update时主键也会自增+1 #####

首先需要在配置中支持多条sql语句执行, 默认为false，这里需要改为true：

![img][]

然后在insert之前增加一条sql语句

![在这里插入图片描述][b14e5a1a7b214a54aaf79004273505cb.png]

设置完之后就可以解决ON DUPLICATE key update会在update时主键也会自增+1这个问题了，但是这样每次在执行这个sql时都会调用alter table wss\_production\_model auto\_increment=1，这样也会导致效率会变低。

##### 3、从Mysql配置出发 #####

修改innodb\_autoinc\_lock\_mode配置

innodb_autoinc_lock_mode中有3种模式, 0、1、2, mysql默认为1,
    
    0：每次分配自增id的时候都会锁表，这个对并发不太支持
    1：只有在bulk　insert的时候才会锁表，简单insert的时候只会使用一个light-weight mutex，比0的并发性能高
    2：很多不保证，不太安全，不建议使用

修改为0则可以解决问题，然而修改完后会导致在高并发下效率低下，而且线上数据库和本地数据库的配置要同步，这里不推荐使用这种方法。

### 三. 总结 ###

具体解决方案可以根据项目来选择，如果项目不大，可以考虑1和2。如果不考虑高并发问题，可以考虑3。

[e618a935f38f44c79f3d20b74e314de1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/11804a247fcf4ebdb947d10d1cefec6e.png
[5743bf3f6fef49e7928ab3a0e4c87711.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/06303f53ef3c4637ad9306e6b20882fd.png
[img]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/c12a7a07319d44f683dbaf7810e3ef7e.png
[b14e5a1a7b214a54aaf79004273505cb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/6610714e018040e8826452eef6885c03.png