目的

学习

MySQL中的 INSERT … ON DUPLICATE KEY UPDATE 语句
PgSQL中的 INSERT … ON CONFLICT [ conflict_target ] conflict_action 语句

的使用。这两个语句不光在语法上相似，在适用的场景中也有重合的部分，接下来我将举例说明这两个语句的使用。

需求介绍

今天在工作时，遇到了这样的一个需求，对一些数据做批量打印时，需要记录下这些数据的打印次数——每打印一次就将该条数据的打印次数加一，在这个场景下我就用到了PgSQL中的ON CONFLICT语法，也想把这个方法整理出来分享给大家，顺便扩展到MySQL中的ON DUPLICATE语法。

为了更好的描述这个需求模型，我们用下面这个例子进行简化：

现在有一个书籍表book，里面记录各个书籍的内容，其中book_id作为主键，即某本书籍的唯一标识。现在有一个借阅的操作，要求每借阅一次这本书，就更新这本书的总借阅次数。而由于某些原因，我们不能直接在这个书籍表book中加入一个字段borrow_times表示书籍的借阅次数(实际上，把这种跟书籍自身内容关系不大的字段放到book中也确实不是什么好的办法，往往会导致日后该表越发臃肿以及难以扩展)。

于是，我们打算新建一个表book_borrow_record专门来存放这些书籍的借阅次数。由于业务模型较为简单，只需三个字段即可：id，该表的自增主键；book_id，书籍的唯一标识；borrow_times，借阅次数。

解决问题的思路很简单：如果从未借阅过本书，那么便在book_borrow_record中新增一条数据；反之，只修改该条记录的借阅次数即可。

比如现在我们有一个book表，存储书籍内容：

book_borrow_record表中也有了一些书的借阅记录了：

现在有个同学又来借这三本书了，等借完以后数据库中的内容应该是：

好像很简单的样子，只要新增一条 b0002 的书的借阅记录，更新另外两条数据 [b0001,b0003] 的借阅次数(次数+1)即可。

那具体怎么做呢？

不是很好的思路：先Select 再分别Insert、Update

如果是刚入职的小伙伴没了解过这个上面提到的语句的话，解决这个问题的思路一般就是：

先在表book_borrow_record中Select查出要借阅的书籍中已经有过借阅记录的书籍 [b0001,b0003]，对这些书籍做更新操作；

select * from book_borrow_record where in ('b0001','b0002','b0003')
复制代码

update book_borrow_record set borrow_times = borrow_times + 1 where book_id in ('b0001','b0003');
复制代码

然后剩下的做新增操作

insert into book_borrow_record (book_id,borrow_times) values ('b0002',1)
复制代码

看得出来，这有点小麻烦了，接下来我们就用本文一开始提到的两个语句来替代这个繁琐的过程。

PgSQL中的ON CONFLICT

先上代码：

INSERT INTO book_borrow_record ( book_id, borrow_times )
VALUES
    ( 'b0001', 1 ),
    ( 'b0002', 1 ),
    ( 'b0003', 1 )
ON CONFLICT ( book_id )
DO UPDATE
    SET borrow_times = book_borrow_record.borrow_times + 1;
复制代码

解释：插入三行数据，对每一行数据进行判断，若不满足book_id已存在的情况就插入该条数据，即插入( ‘b0002’, 1 )；反之更新数据，即borrow_times + 1；

就是这么简单，但是有几个注意点需要说明：

要为book_id这个字段设置为主键、唯一键或者唯一索引 作为一个唯一约束的字段 [在mysql中则是主键与索引，Mysql没有唯一键]，只有这样ON CONFLICT才会生效，反之会提示报错。关于ON CONFLICT的内容就可以直观的看为是冲突，这个冲突必须是由一些唯一的约束引起（反过来想，都不是唯一的约束，重复了也不能算是冲突啊，也就是随便重复啊，显然一本书的编号对于一本书来说就是一个唯一约束）。
SET borrow_times = book_borrow_record.borrow_times + 1;中的book_borrow_record不能省略，否则会报字段 Column reference ‘xxx’ is ambiguous的错误。为什么呢？其实这里的borrow_times还可以由另一个固定名称的“表” excluded 来调用：

DO UPDATE
    SET borrow_times = excluded.borrow_times + 1;
复制代码

上面的book_borrow_record.borrow_times很好理解，就是数据库表中的字段值，excluded.borrow_times是什么呢？实际上它的值是

被排除在外（产生冲突）的行数据的传入的更新值，所以如果你想通过values(……)中的值来给borrow_times字段赋值你就可以使用

SET borrow_times = excluded.borrow_times;
复制代码

关于ON CONFLICT中的条件以及INSERT … ON CONFLICT [ conflict_target ] conflict_action语法的更多详细信息见官方文档说明

MySQL中的ON DUPLICATE

在这个场景下（新增没有的，更新已有的），MySQL中的ON DUPLICATE与前文的ON CONFLICT可以说是十分相似了，在这里Conflict和Duplicate都可以看做是近义词了：前者是当准备新增的内容产生冲突时，就取消插入操作执行更新操作；照葫芦画瓢，ON DUPLICATE是不是就可以解释为当准备新增的内容重复时，取消插入操作而做执行更新操作。这里的冲突和重复都是指唯一索引和主键的冲突和重复——唯一约束绝不能重复。

接下来我们用MySQL来具体的做一下这个实验：

book表的构建（其实这个表跟我们的实验没啥关系，只是帮助理解）

CREATE TABLE `book` (
  `book_id` varchar(10),
  `book_name` varchar(30),
  `book_author` varchar(20),
  PRIMARY KEY (`book_id`)
) ENGINE=InnoDB
复制代码

INSERT INTO `book` VALUES ('b0001', '《Java》', 'java');
INSERT INTO `book` VALUES ('b0002', '《MySQL》', 'mysql');
INSERT INTO `book` VALUES ('b0003', '《Spring》', 'spring');
复制代码

book_borrow_record表的构建

CREATE TABLE `book_borrow_record`  (  `book_id` varchar(10) CHARACTER NOT NULL,  `borrow_times` int DEFAULT NULL,  PRIMARY KEY (`book_id`) USING BTREE) ENGINE = InnoDB 
复制代码

INSERT INTO `book_borrow_record` VALUES ('b0001', 1);INSERT INTO `book_borrow_record` VALUES ('b0003', 3);
复制代码

执行 INSERT … ON DUPLICATE KEY UPDATE 语句

INSERT INTO book_borrow_record ( book_id, borrow_times )VALUES    ( 'b0001', 1 ),    ( 'b0002', 1 ),    ( 'b0003', 1 )ON DUPLICATE KEYUPDATE borrow_times = borrow_times + 1;
复制代码

检查结果

确实生效了！

还有几点注意需要说明：

仔细观察两个语句的使用，在细节上是有一些不同的。
与PgSQL不同的是，如果你不给book_id加上主键或唯一索引作为行数据唯一约束，上面的语句也是能运行通过的，只不过它达不到预期效果，它会认为，这个字段既然不是唯一键那么就不存在是不是重复这个概念，三条数据都会执行插入操作。
没有Exclude这个字段。
PgSQL中是有唯一键这个概念的，在Navicat中就能设置，但是Mysql中是没有的，但是只要记住，我们想要检测冲突或者重复的字段必须是能够作为数据的唯一标识的即可。

详细信息请见：MySQL官方文档。