前言

如果不特别指出，默认mysql版本为8.0

简介

往数据库中插入记录时，如果发生唯一索引值冲突，insert on duplicate允许进行进一步的crud操作。伪代码如下：

insert record
IF exist duplicate record THEN
  do something on duplicated rows
ELSE 
  do nothing
END IF

具体用法

先初始化将要用到的表跟数据

create table t1
(
    id bigint primary key auto_increment,
    a  integer unique,
    b  integer default 999
);
INSERT INTO test_insert_on_dup_update(id, a)
VALUES (1, 1);
INSERT INTO test_insert_on_dup_update(id, a)
VALUES (5, 5);
INSERT INTO test_insert_on_dup_update(id, a)
VALUES (10, 10);

1. 单个唯一索引插入冲突

通过如下sql进行数据插入

insert into t1(a,b) values(1,199) on duplicate update b = 1;

因为表中已经存在a=1的记录，这个时候会触发on duplicate后面的update操作，将a=1的记录的b从999修改为1.

在这种情况下，上面的sql等价于

update b=1 where a = 1;

1.2 多个唯一索引插入冲突

如果插入的记录与a跟b上的索引值都发生了冲突，且发生冲突的记录有多条会怎么样呢？

insert into t1(id, a) values(1,5) 
  on duplicate update b = 1;

因为a=1跟b=5都存在，这个时候有两行记录与即将插入的记录有冲突。按照前面介绍的规则来看，貌似id=1跟a=5这两条记录的b都会被更新成1。但事实是只有一条有冲突的记录会应用on duplicate后面的子句。而这条被命中记录就是在所有满足条件的记录中，其id值在聚集索引叶节点的链表中最靠前的那条记录。在本例中也就是id=1的那条记录。该sql的实际效果等价于

update t1 set b=1 where id=1 or a=5 limit 1;

所以，当发生这种情况时，我们很难去预料语句的行为。应当尽量避免这种情况。

1.3 子句获取插入列即将插入的值

在8.0.19之前

insert into t1(id, a) values(1,5) 
  on duplicate update b = values(a);

等价于

insert into t1(id, a) values(1,5) 
  on duplicate update b = 5;

values(a)获取的是原本准备插入的a=5这个值.

要注意的是：这种写法将在8.0.20版本被废弃，对应的功能在未来会被移除。

在8.0.19之后

insert into t1(id, a) values(1,5) as new
  on duplicate update b = new.a;

这里为新插入的记录设置了一个别名new，通过这个别名可以获取到准备插入的数据。另外，还可以基于这个别名更进一步的为里面的每个列设置别名

insert into t1(id, a) values(1,5) as new(x,y)
  on duplicate update b = x;

1.4 根据查询结果进行插入

insert into t1(id, a) select x,y from t2
  on duplicate update b = x

像这类语句，由于插入的顺序依赖于select的结果集里行的顺序，而mysql不能保证这个select的结果集在主从上的顺序是完全一致的，这就会导致基于statement的主从复制会出现数据不一致的问题。而基于行的复制模式不存在这个问题。所以，如果存在这类子句中带select的sql，注意将复制模式设置为row-based或者mixed

跟锁相关的部分

根据不同的隔离级别，有如下特征：

1. repeatable read

   • 普通唯一索引（非主键）发生唯一key冲突，这种情况会锁住该索引以及聚集索引。
   • 主键值发生冲突。则会为发生冲突的主键值设置行锁。

2. READ COMMITTED：会为冲突的索引值设置行锁