SQL进阶（一）ON CONFLICT

SQL进阶 ON CONFLICT

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

PgSQL中的ON CONFLICT

先上代码：

INSERT INTO bookborrowrecord ( bookid, borrowtimes ) VALUES ( 'b0001', 1 ), ( 'b0002', 1 ), ( 'b0003', 1 ) ON CONFLICT ( bookid ) DO UPDATE SET borrowtimes = bookborrowrecord.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官方文档ON DUPLICATE。