MySQL 的 on duplicate key update用法和注意事项

1 前言-用法由来

MySQL 的 on duplicate key update 方法使用的由来
在实际应用中 会有两种地方用到这种写法

1、 数据同步 max（updateTime）
2、 导入数据或者初始化数据，要求如果有就新增，没有就更新。

在做项目的时候，因为要同步其他服务里的数据，因为没有经验，心里当时想的做法就是，先将我们自己服务库里面的数据先删掉，然后从其他服务里面新增过来。
但是。。。。。。。
先删后增，有可能连接超时，网关超时，IO流的异常… 等等异常
因为考虑的问题还是太少，所以才会造成这些奇奇怪怪的错误，这都不是真正想要的结果。。

没办法，只能想着换种写法来处理这个问题
然后才发现了这种做法

这种写法其实就是 ：数据库中存在某个记录时，执行这个语句会更新，而不存在这条记录时，就会插入,这里会找到数据库中的主键ID去做比较

注意点： 因为这是个插入语句，所以不能加where条件。

2 写法详解

2.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.2 多条记录写法

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)  
      ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b); 

2.3 MyBatis写法

  insert into table(
          XXX,YYY
          )VALUES
           <foreach collection ="list" item="item" index= "index" separator =",">
             (
                #{item.XXX}, #{item.YYY}
             )
           </foreach>
           ON DUPLICATE KEY UPDATE
               XXX = VALUES(XXX),YYY= VALUES(YYY)

2.4 还有就是 insert into 的时候可能会select 某张表是否有数据，依照查出来的哪张表来看是否有数据来做判断是否新增或更新

 <insert id="loadData">
      insert into tableA
      select id,create_by,create_by_id,create_time,update_by,update_by_id,update_time,is_deleted,ver,mof_div_code,ifnull(fiscal_year,'') as fiscal_year
      from tableA
      where asset_id in (
        select asset_id from tableB
        where 1=1
            <if test="agencyCodes != null and agencyCodes.size() > 0 ">
                and agency_code in
                <foreach collection="agencyCodes" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="fiscalYear != null and fiscalYear != ''">
              and fiscal_year=#{fiscalYear}
            </if>
      )
      on duplicate key
    update create_by=values(create_by),create_by_id=values(create_by_id),create_time=values(create_time),update_by=values(update_by),update_by_id=values(update_by_id),update_time=values(update_time),is_deleted=values(is_deleted),ver=values(ver),mof_div_code=values(mof_div_code),fiscal_year=values(fiscal_year)
    </insert>

当然，还可以写一些特殊的写法：

INSERT INTO t1 (a, b)
  SELECT c, d FROM t2
  UNION
  SELECT e, f FROM t3
ON DUPLICATE KEY UPDATE b = b + c;

INSERT INTO t1 (a, b)
SELECT * FROM
  (SELECT c, d FROM t2
   UNION
   SELECT e, f FROM t3) AS dt
ON DUPLICATE KEY UPDATE b = b + c;

3. 注意事项

3.1 业务主键ID不能重复 （最好不要用自增的列 ）

更新的内容中unique key或者primary key最好保证一个，不然不能保证语句执行正确(有任意一个unique key重复就会走更新,当然如果更新的语句中在表中也有重复校验的字段，那么也不会更新成功而导致报错,只有当该条语句没有任何一个unique key重复才会插入新记录)；尽量不对存在多个唯一键的table使用该语句，避免可能导致数据错乱。

3.2 死锁 （压测）

3.2.1 使用mysql5.6版本，可以看见这个是在5.7中引入的，5.6中不会出现这个情况，版本不一样可能会有死锁的可能性

3.3.2 一般的死锁日志都是由两个事务导致的，所以会给予一定的迷惑性，其实大部分的死锁都是由两个以上的事务导致的

3.3 如果数据表id是自动递增的不建议使用该语句；id不连续，如果前面更新的比较多，新增的下一条会相应跳跃的更大。

3.4 只能是MySQL 数据库语言，其他不行