Mysql on duplicate key update用法及优缺点

Mysql on duplicate key update用法及优缺点  在实际应用中,经常碰到导入数据的功能,当导入的数据不存在时则进行添加,有修改时则进行更新,  在刚碰到的时候,一般思路是将其实现分为两块,分别是判断增加,判断更新,后来发现在mysql中有ONDUPLICATEKEYUPDATE一步就可以完成(Mysql独有的语法)。ONDUPLICATEKEYUPDATE单个增加更新及批量增加更新的sql在MySQL数据库中,如果在insert语句后面带上ONDUPLICATEKEYUPDATE子句,而要插入的行与表中现有记录的惟一索

  在实际应用中,经常碰到导入数据的功能,当导入的数据不存在时则进行添加,有修改时则进行更新,

  在刚碰到的时候,一般思路是将其实现分为两块,分别是判断增加,判断更新,后来发现在mysql中有ON DUPLICATE KEY UPDATE一步就可以完成(Mysql独有的语法)。

ON DUPLICATE KEY UPDATE单个增加更新及批量增加更新的sql

在MySQL数据库中,如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句,而要插入的行与表中现有记录的惟一索引或主键中产生重复值,那么就会发生旧行的更新;如果插入的行数据与现有表中记录的唯一索引或者主键不重复,则执行新纪录插入操作。

说通俗点就是数据库中存在某个记录时,执行这个语句会更新,而不存在这条记录时,就会插入。

注意点:

  因为这是个插入语句,所以不能加where条件。

  如果是插入操作,受到影响行的值为1;如果更新操作,受到影响行的值为2;如果更新的数据和已有的数据一样(就相当于没变,所有值保持不变),受到影响的行的值为0。

该语句是基于唯一索引或主键使用,比如一个字段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;

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>

在mybatis中进行批量增加或修改的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>

项目中数据的操作有时候会令人头大,遇到一个需求:

需要将数据从A数据库的a数据表同步到B数据库的b数据表中(ab表结构相同,但不是主从关系。。。just同步过去)

第一次同步过去,b表为空,同步很简单。

但是当a表中的某些数据更新且增加了新数据之后,再想让两个表同步就有些麻烦了。(如果把b表清空,重新同步,数据量过大的话耗费的时间太长,不是一个好办法)

想着能不能按照时间段来做更新,这段时间内有新数据了,就插入数据,有数据更新了就更新数据。先说下我的思路:

步骤:

  1.首先我从a表取出某一时间段的数据(分段更新)

  2.往b表内放数据,根据主键判断b表是否已经有此条记录,没有此数据则插入,有了记录则对比数据是否一样,一样则不做更改,不一样就做更新操作。

此时使用该语句可以满足需要,但是要注意几个问题:

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

  • 在有可能有并发事务执行的insert 语句情况下不使用该语句,可能导致产生death 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}
  </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>

产生death lock原理

insert … on duplicate key 在执行时,innodb引擎会先判断插入的行是否产生重复key错误,如果存在,在对该现有的行加上S(共享锁)锁,如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作,然后对该记录加上X(排他锁),最后进行update写入。

如果有两个事务并发的执行同样的语句,那么就会产生death lock,如:

Mysql on duplicate key update用法及优缺点

参考文章:

https://www.cnblogs.com/zjdxr-up/p/8319982.html

https://blog.csdn.net/pml18710973036/article/details/78452688

原文地址:https://www.cnblogs.com/better-farther-world2099/articles/11737376.html

今天的文章Mysql on duplicate key update用法及优缺点分享到此就结束了,感谢您的阅读,如果确实帮到您,您可以动动手指转发给其他人。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/32832.html

(0)
编程小号编程小号

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注