mysql 覆盖写入_INSERT ON CONFLICT覆盖写入

SQL示例

基本功能示例

首先来看一个例子，创建一个表t1，包含4列，其中第1列(a)是它的主键(primary key)：

create table t1 (a int primary key, b int, c int, d int default 0);

对该表t1插入一行数据，主键列a的值为0：

insert into t1 values (0,0,0,0);

select * from t1;

a | b | c | d

---+---+---+---

0 | 0 | 0 | 0

(1 row)

如果再对该表t1插入一行数据，主键列a的值还是0，会得到一个报错：

insert into t1 values (0,1,1,1);

ERROR: duplicate key value violates unique constraint "t1_pkey"

DETAIL: Key (a)=(0) already exists.

但是，在很多场景中，不希望这个插入报错。有些场景希望主键冲突后什么都不做，有些场景希望主键冲突后更新非主键列的数据。那么在这些场景中就需要用到本文重点介绍的覆盖写入特性了。

主键冲突后，希望什么都不做(适用于有冲突丢弃冲突数据的场景)：

-- 使用on conflict do nothing子句

insert into t1 values (0,1,1,1) on conflict do nothing;

select * from t1;

a | b | c | d

---+---+---+---

0 | 0 | 0 | 0

(1 row)

主键冲突后，希望更新非主键列(适用于全部列覆盖写入的场景)：

-- 使用on conflict do update子句

insert into t1 values (0,2,2,2) on conflict (a) do update set (b, c, d) = (excluded.b, excluded.c, excluded.d);

select * from t1;

a | b | c | d

---+---+---+---

0 | 2 | 2 | 2

(1 row)

可以从上面的例子看出，在INSERT语句中加入ON CONFLICT DO NOTHING/UPDATE子句后，就可以轻松实现覆盖写入了。其中excluded为表示插入values

(0,2,2,2)构成的伪表。

此外，上述语句等价为：

insert into t1 values (0,2,2,2) on conflict (a) do update set b = excluded.b, c = excluded.c, d = excluded.d;

当然，覆盖写入的功能还很强大，下面将具体介绍。

有冲突，覆盖部分列为待更新数据(适用于基于冲突数据覆盖部分列的场景)

-- 只覆盖c列的数据(excluded.c为冲突数据的c列)

insert into t1 values (0,0,3,0) on conflict (a) do update set c = excluded.c;

select * from t1;

a | b | c | d

---+---+---+---

0 | 2 | 3 | 2

(1 row)

有冲突，更新部分列的数据(适用于基于原始数据更新部分列场景)

-- 将原来的d列数据加1

insert into t1 values (0,0,3,0) on conflict (a) do update set d = t1.d + 1;

select * from t1;

a | b | c | d

---+---+---+---

0 | 2 | 3 | 3

(1 row)

有冲突，更新数据为默认值(适用于冲突后，回退数据到默认值的场景)

-- 将d列数据设置为其默认值(上文中d列的默认值为0)

insert into t1 values (0,0,3,0) on conflict (a) do update set d = default;

select * from t1;

a | b | c | d

---+---+---+---

0 | 2 | 3 | 0

(1 row)

插入多条数据

-- 插入2行数据，其中a=0的行有主键冲突(进行do nothing)，a=1的行没有主键冲突(进行了插入)

insert into t1 values (0,0,0,0), (1,1,1,1) on conflict do nothing;

select * from t1;

a | b | c | d

---+---+---+---

0 | 2 | 3 | 0

1 | 1 | 1 | 1

(2 rows)

-- 插入2行数据，其中a=0的行有主键冲突(进行覆盖写入)，a=2的行没有主键冲突(进行了插入)

insert into t1 values (0,0,0,0), (2,2,2,2) on conflict (a) do update set (b, c, d) = (excluded.b, excluded.c, excluded.d);

select * from t1;

a | b | c | d

---+---+---+---

0 | 0 | 0 | 0

1 | 1 | 1 | 1

2 | 2 | 2 | 2

(3 rows)

插入数据来自于子查询(用于合并两表数据或更复杂的INSERT INTO SELECT场景)

create table t2 (like t1);

insert into t2 values (2,22,22,22),(3,33,33,33);

-- 将t2的数据插入t1，如果有冲突，则覆盖写入

insert into t1 select * from t2 on conflict (a) do update set (b, c, d) = (excluded.b, excluded.c, excluded.d);

select * from t1;

a | b | c | d

---+----+----+----

0 | 0 | 0 | 0

1 | 1 | 1 | 1

2 | 22 | 22 | 22

3 | 33 | 33 | 33

(4 rows)