Mysql on duplicate key update用法及优缺点

概述

在实际应用中，经常碰到导入数据的功能，当导入的数据不存在时则进行添加，有修改时则进行更新。
在刚碰到的时候，一般思路是将其实现分为两块，分别是判断增加，判断更新，后来发现在mysql中有ON DUPLICATE KEY UPDATE一步就可以完成（Mysql中独有的语法）。

SQL写法

在MySQL数据库中，如果在insert语句后面带上ON DUPLICATE KEY UPDATE子句，而要插入的行与表中现有记录的唯一索引或主键（可以是单一字段的唯一索引，也可以是复合字段的唯一索引）中产生重复值，那么就会发生旧行的更新；如果插入的行数据与现有表中记录唯一索引或者主键不重复，则执行新记录插入操作。
通俗点就是数据库中存在某条记录时，执行这个语句会更新，而不存在这条记录时，就会插入。

注意点：
因为这是个插入语句，所以不能加where条件。
如果是插入操作，受到影响行的值为1；如果更新操作，受到影响的行的值为2；如果更新的数据和已有的数据比对一样（就相当于没变，所有值保持不变），受到影响的行的值为0。

该语句是基于唯一索引（单一自动或复合字段的唯一索引）或者主键使用。

1. 比如一个字段a被加上了unique index，并且表中已经存在了一条记录值为1。
   下面两个语句会有相同的效果。

insert into table (a,b,c) values (1,2,3) on duplicate key update c = c+1;
update table set c = c+1 where a =1;

2. 比如两个字段a,b被加上unique index，并且表中已经存在了一个记录值a = 1, b =2，下面两个语句会有相同的效果。

insert into table (a,b,c) values (1,2,3) on duplicate key update c = c + 1;
update table set c = c + 1 where a = 1 and b =  2;

ON DUPLICATE KEY UPDATE后面可以放多个字段，用英文逗号分割。
如下面列子所示：

insert into table (a,b,c)
values
(1,2,3),(4,5,6)
on duplicate key update c = values(a) + values(b)

表中将更改（增加或修改）两条记录。
在mybatis中进行单个增加或修改sql的写法为：

<insert id="insertOrUpdateCameraInfoByOne" paramerType="com.pojo.AreaInfo">
    insert into camera_info( cameraId,zone1Id,zone1Name,zone2Id,zone2Name,zone3Id,zone3Name,zone4Id,zone4Name）
    VALUES(
        #{cameraId},#{zone1Id},#{zone1Name}, #{zone2Id},
        #{zone2Name}, #{zone3Id}, #{zone3Name},
        #{zone4Id}, #{zone4Name},)
    ON DUPLICATE KEY UPDATE 
    cameraId = VALUES(cameraId),
    zone1Id = VALUES(zone1Id),zone1Name = VALUES(zone1Name),
    zone2Id = VALUES(zone2Id),zone2Name = VALUES(zone2Name),
    zone3Id = VALUES(zone3Id),zone3Name = VALUES(zone3Name),
    zone4Id = VALUES(zone4Id),zone4Name = VALUES(zone4Name)
</insert>

在mybaits中进行批量增加或修改的sql为：

<insert id="insertOrUpdateCameraInfoByBatch" parameterType="java.util.List">
      insert into camera_info(
          zone1Id,zone1Name,zone2Id,zone2Name,zone3Id,zone3Name,zone4Id,zone4Name,
          cameraId
          )VALUES
           <foreach collection ="list" item="cameraInfo" index= "index" separator =",">
             (
                #{cameraInfo.zone1Id}, #{cameraInfo.zone1Name}, #{cameraInfo.zone2Id},
                #{cameraInfo.zone2Name}, #{cameraInfo.zone3Id}, #{cameraInfo.zone3Name},
                #{cameraInfo.zone4Id}, #{cameraInfo.zone4Name}, 
                #{cameraInfo.cameraId}, 
             )
           </foreach>
           ON DUPLICATE KEY UPDATE
               zone1Id = VALUES(zone1Id),zone1Name = VALUES(zone1Name),zone2Id = VALUES(zone2Id),
               zone2Name = VALUES(zone2Name),zone3Id = VALUES(zone3Id),zone3Name = VALUES(zone3Name),
               zone4Id = VALUES(zone4Id),zone4Name = VALUES(zone4Name),
               cameraId = VALUES(cameraId)
    </insert>

values函数

values函数相当于更新需要插入的值，更新外部传入的值（值是本来应该插入的值，也就是入参)。
VALUES(clomn_name)：表示更新指定的列，更新列值为外部传入的值。
比如：zone1Id = VALUES(zone1Id)，更新zone1Id列的值为#{cameraInfo.zone1Id}。

注意问题

• 更新的内容中unique key或者primary key最好按照一个来判断是否重复，不然不能保证语句执行正确（有任意一个unique key重复就会走更新）；尽量不对存在多个唯一键的talbe使用该语句，避免可能导致数据错乱。
• 在有可能有并发事物执行的insert语句下不要使用该语句，可能导致产生dead lock。
• 如果数据表id是自动递增的不建议使用该语句；id不连续，如果前面更新的比较多，新增的下一条会相应跳跃的更大。
• 该语句是mysql独有的语法，如果可能涉及到其他数据库语言要慎重使用。

主键不连续自增解决方法

源引自：https://www.linuxidc.com/Linux/2018-01/150427.htm

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

<!-- 批量保存软件使用时长表 -->
<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}

为了效率用到了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 app_table set duration=duration+#{duration} where userName=#{userName} and appName=#{appName}

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

insert into app_table(userName,appName,duration) values(#{userName},#{appName},#{duration}) # 产生dead lock原理 insert ... on duplicate key在执行时，innodb引擎会先判断插入的行是否产生重复key错误，如果存在对该现有的行加上S（共享锁）锁，返回该行数据给mysql，mysql修改完数据后，然后对该记录加上X（排它锁），最后进行update写入。 

参考

Mysql on duplicate key update用法及优缺点
VALUES（）函数用法
INSERT … ON DUPLICATE KEY UPDATE产生death lock死锁原理