• 关于Mysql 的on duplicate key update操作,导致主键不连续自增的问题
  • 发布于 1个月前
  • 82 热度
    0 评论
一 相关说明
在实际的开发中,经常会遇到这样的场景:若数据库里面不存在数据,则插入;若存在,则更新。在Mysql中,可以使用ON DUPLICATE KEY UPDATE,一步就能完成上述操作。简单说,就是数据库中存在某个记录时,执行这个语句会更新,而不存在这条记录时,就会插入。

需要说明的是该语句是基于唯一索引或主键使用,比如字段加上了unique index,如果没有设置则不会生效。在执行的过程中,如果是插入操作,受到影响行的值为1;如果更新操作,受到影响行的值为2;如果更新的数据和已有的数据一样(就相当于没变,所有值保持不变),受到影响的行的值为0。记录存在时,只会更新on duplicate key update之后指定的字段。

一般的SQL格式如下:
INSERT INTO table (a,b,c) VALUES (1,2,3)  
 ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b); 
二 出现的问题
在开发中,使用了这个语句,然后就发现问题了。首先说下我的操作,就是针对2个不同的唯一键,mock数据,然后执行,再看数据库里面的记录,如下:

发现id(是主键)是3,而不是2;毕竟针对id,设置得有自动增长的属性。那为啥会出现这样的情况呢?查了网上的一些资料,如下:

1、mysql数据库innodb_autoinc_lock_mode锁的问题,默认为1
(1)为0时是每次分配自增id的时候都会锁表.
(2)为1时只有在bulk insert的时候才会锁表,简单insert的时候只会使用一个light-weight mutex,比0的并发性能高
(3)为2时取消auto_inc锁,这个模式下的性能是最好的;但是它也有一个问题,对于同一个语句来说它所得到的auto_incremant值可能不是连续的。

2、使用ON DUPLICATE KEY UPDATE时,如果将行作为新行插入,则每行的受影响行值为 1,如果更新现有行,则为 2,如果将现有行设置为其当前值,则为 0。(换句话说就是默认级别为1,在进行insert操作时此时会锁表所以只能影响一行,但是ON DUPLICATE KEY UPDATE本质是先删除后插入,遇到重复是会进行回滚所以它占了两个索引,一切是以主键索引自增为基础的)


三 如何解决问题
最简单的做法就是把这条SQL拆分成2条:先查是否存在记录,然后再来决定是插入还是更新。或者按照上面所说,既然是数据库锁的问题,那就改变数据库锁的级别(虽然可以这样做,但不建议这样搞,毕竟会影响到数据库性能)。最后跟大佬协商了下 ,采取第一种方案解决问题。
用户评论