Mysql中INSERT ... ON DUPLICATE KEY UPDATE的实践

一、前言

在日常业务开发中经常有这样一个场景,首先创建一条记录,然后插入到数据库;如果数据库已经存在同一主键的记录,则执行update操作,如果不存在,则执行insert操作;

这个操作可以在业务层做,也可以在数据库层面做;

业务层一般做法是先查询,如果不存在在插入,如果存在则更新,但是查询和插入不是原子性操作,在并发量比较高的时候,可能两个线程都查询某个记录不存在,所以会执行两次插入,然后其中一条必然会因为主键(这里说的主键不是递增主键)冲突而失败。

数据库层mysql中INSERT ... ON DUPLICATE KEY UPDATE就可以做这个事情,并且是原子性操作,本文就来讲解的使用。

二、INSERT ... ON DUPLICATE KEY UPDATE命令

2.1单条记录下使用

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

如上sql假如t1表的主键或者UNIQUE 索引是a,那么当执行上面sql时候,如果数据库里面已经存在a=1的记录则更新这条记录的c字段的值为原来值+1,然后返回值为2。如果不存在则插入a=1,b=2,c=3到数据库,然后返回值为1。

2.2多记录下使用

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

三、MyBatis下使用

Mybatis作为经典的数据库持久层框架,自然要介绍下它下的使用

  • 在mapper.xml里面配置如下: 假设a为主键
<insert id="insertOrUpdate">
        INSERT INTO t1 (a,b,c) 
        values
        <foreach collection="list" item="item"  separator=",">
            (#{item.a},#{item.b},#{item.c})
        </foreach>
        ON DUPLICATE KEY UPDATE c=values(c),b=values(b)
 </insert>
  • 对应的mapper接口可以定义为:
long insertOrUpdate(List<Test> list);
class Test{
private int a;
private int b;
private int c;
...
}

注:mysql中sql字符串大小有限制,我本机的mysql上执行show VARIABLES like '%max_allowed_packet%';结果为max_allowed_packet为4M:

image.png

四、参考

https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏我是攻城师

如何在shell下使用JavaScript和Mongodb交互?

3965
来自专栏数据和云

INTERVAL分区升级了,你知道吗?!

辑手记: Oracle 11g新增的INTERVAL分区使得手工给RANGE分区添加新分区的工作变得异常简单,这也使得INTERVAL分区成为RANGE分区的最...

3005
来自专栏Java帮帮-微信公众号-技术文章全总结

【数据库】MySQL进阶二、索引简易教程

【数据库】MySQL进阶二、索引简易教程 Mysql索引简易教程 基本概念 索引是指把你设置为索引的字段A的内容储存在一个独立区间S里,里面只...

3529
来自专栏与神兽党一起成长

修改MySQL varchar类型字段的排序规则

记录一个在工作中遇到的问题,也不算是问题,为的是找一种简便的方法批量修改数据表字段的排序规则,在MySQL中叫collation,常常和编码CHARACTER一...

2313
来自专栏小勇DW3

唯一索引的一种使用情景【有则U无则I】

这个知识点是最近一位面试老师问我的,当时对这种方法不了解,所以只能说那个中效率低的方法了,也就是先进性select判断,然后在执行更新或者插入操作,显然这种是很...

1124
来自专栏用户2442861的专栏

关于sql中索引的优缺点(面试常考)

   原文       http://blog.csdn.net/dinglang_2009/article/details/5951428

1511
来自专栏散尽浮华

mysql操作命令梳理(4)-中文乱码问题

在平时的mysql运维操作中,经常会碰到插入中文字段后出现乱码的情况,产生中文乱码的原因一般有: 1)mysql的编码格式不对,是latin1编码。强烈推荐将m...

2098
来自专栏MYSQL轻松学

MYSQL RR隔离级别下MVCC及锁解读

MVCC(Multi-Version Concurrent Control):多版本并发控制,只作用于RC和RR隔离级别,主要是为了避免脏读、非重复读,而非幻读...

4958
来自专栏高爽的专栏

MySQL从5.5升级到5.6,TIMESTAMP的变化

前言 前段时间,系统MySQL从5.5升级到了5.6,系统出现了大量的异常。大部分异常引起原因是由于TIMESTAMP的行为发生了变化。 TIMESTAMP在M...

2300
来自专栏决胜机器学习

优化页面访问速度(二) ——数据库优化

数据库优化,主要包括数据表设计、索引、sql语句、表拆分、数据库服务器架构等方向的优化。

1185

扫码关注云+社区