MySQL ON DUPLICATE KEY UPDATE 语法

2023-03-16 22:43:19

一、前言

在日常业务开发中经常有这样一个场景，首先创建一条记录，然后插入到数据库，如果数据库已经存在同一主键的记录，执行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。如果insert语句中同时出现UNIQUE索引和PRIMARY KEY，则以后者为准。ON DUPLICATE KEY UPDATE后面可以放多个字段，用英文逗号分割。

2.2 多记录下使用

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

三、MyBatis下使用

Mybatis作为经典的数据库持久层框架，自然要介绍下它下的使用。假设t1表的主键是a。在mapper.xml里面批量操作配置如下：

<insert id="insertOrUpdateBatch">
        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 insertOrUpdateBatch(List<Test> list);

实体类定义

class Test{
private int a;
private int b;
private int c;
 // omit getter, setter and toString
}

Reference

https://www.jianshu.com/p/78ea17c6d190

码农公寓