on duplicate key update导致主键不连续自增解决方法

共计 1501 个字符，预计需要花费 4 分钟才能阅读完成。

最近项目上需要实现这么一个功能: 统计每个人每个软件的使用时长, 客户端发过来消息, 如果该用户该软件已经存在增更新使用时间, 如果没有则新添加一条记录, 代码如下:

    <!– 批量保存软件使用时长表 –>
    <update  id=”saveApp”  parameterType=”java.util.List”>
    <foreach collection=”appList” item=”item” index=”index”  separator=”;”>
     insert into app_table(userName,app,duration)
     values(#{userName},#{item.app},#{item.duration})
     on duplicate key update duration=duration+#{item.duration}
  </foreach>
  </update>

为了效率用到了 on duplicate key update 进行自动判断是更新还是新增, 一段时间后发现该表的主键 id(已设置为连续自增), 不是连续的自增, 总是跳跃的增加, 这样就造成 id 自增过快, 已经快超过最大值了, 通过查找资料发现,on duplicate key update 有一个特性就是, 每次是更新的情况下 id 也是会自增加 1 的, 比如说现在 id 最大值的 5, 然后进行了一次更新操作, 再进行一次插入操作时,id 的值就变成了 7 而不是 6.
为了解决这个问题, 有两种方式, 第一种是修改 innodb_autoinc_lock_mode 中的模式, 第二种是将语句修拆分为更新和操作 2 个动作

第一种方式:innodb_autoinc_lock_mode 中有 3 中模式,0,1 和 2,mysql5 的默认配置是 1,

0 是每次分配自增 id 的时候都会锁表.

1 只有在 bulk insert 的时候才会锁表, 简单 insert 的时候只会使用一个 light-weight mutex, 比 0 的并发性能高

2. 没有仔细看, 好像是很多的不保证 … 不太安全.

数据库默认是 1 的情况下, 就会发生上面的那种现象, 每次使用 insert into .. on duplicate key update 的时候都会把简单自增 id 增加, 不管是发生了 insert 还是 update

由于该代码数据量大, 同时需要更新和添加的数据量多, 不能使用将 0 模式, 只能将数据库代码拆分成为更新和插入 2 个步骤, 第一步先根据用户名和软件名更新使用时长, 代码如下:

  <update id=”updateApp” parameterType=”App”>
   update app_table
   set duration=duration+#{duration}
   where userName=#{userName} and appName=#{appName}
  </update>

然后根据返回值, 如果返回值大于 0, 说明更新成功不再需要插入数据, 如果返回值小于 0 则需要进行插入该条数据, 代码如下:

<insert id=”saveApp” keyProperty = “id” useGeneratedKeys = “true”  parameterType=”App”>
   insert into app_table(userName,appName,duration)
   values(#{userName},#{appName},#{duration})
  </insert>

这样解决效率上肯定为受到影响, 不知道会不会丢数据, 观察一段时间再优化吧!

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2018-01/150427.htm