INSERT ON DUPLICATE KEY UPDATE导致自增字段不连续

今天做一个自用小图库的时候发现保存的tag_id没一会就已经上万了,但是实际的tag数量才只有600个左右,找了一会儿发现是ON DUPLICATE KEY UPDATE 导致的。

简单来说就是由于mysql在执行语句之前不知道它会进入insert还是update模式,为了一致性只能先取一个自增id,不然如果进了insert模式就没有id用了,就是这样导致即使该语句实际上进入的是update模式也会导致自增id+1。

网上有找到修改innodb_autoinc_lock_mode来解决的方法,但是我改成0了之后依然会导致进入update模式后自增+1,不知道为什么。

还有的说在程序里先select,再决定是insert还是update,但这样并发会导致问题,如果select和后续步骤之间有别的请求插入了满足条件的条目,就会导致错误。

方案1

我用暂时了这样一个笨办法,关掉自增,然后手动设置id为最大值+1,其中tag_idname 都是unique 字段

INSERT INTO <code>{{EJS5}}</code> ( <code>{{EJS6}}</code>, <code>{{EJS7}}</code>, <code>{{EJS8}}</code> )
VALUES	(( SELECT id FROM ( SELECT max( tag_id )+ 1 AS id FROM tags ) AS tmp), 'valueA', 'valueB') 
ON DUPLICATE KEY UPDATE <code>{{EJS9}}</code>=VALUES(<code>{{EJS10}}</code>)

由于insert里不能直接select被insert的表,所以需要在select外面再套一个select,变成两层子查询。

但这样做也有一个很明显的缺陷,因为用的不是数据库自带的自增,于是自然也就没有LAST_INSERT_ID ,所以如果需要获取刚刚插入数据的id的话,就得再进行一次查询。

好在可以根据affectedRows判断此语句发生了更新还是插入,如果影响的行数是2的话那就是更新,如果是1就是插入了一条新数据。

注意,这个方法只适用于单个事务依次执行,如果是并发会造成死锁!

方案2

这个问题只在InnoDB会出现,所以如果对并发要求不高的话可以直接改成MyISAM引擎解决问题。

 

如果有什么别的好办法希望可以给我留言,谢谢。



本文发布于 https://luojia.me

本站文章未经文下加注授权不得拷贝发布。

0 0 投票数
打分
订阅评论
提醒
guest
0 评论
内联反馈
查看所有评论