基于 PG，实现只插入不存在的数据

需求说明

现有一批数据，需要插入到数据库中。但是，可能有部分数据是数据库中已经存在的，基于某种业务上的唯一性，为了避免业务数据的重复，插入时需要保证只插入库中不存在的数据，已经存在的数据不再重复插入。

解决方案

本文档均基于 PostgreSQL 数据库

场景设定

有一批用户需要存储到数据库中，表结构如下：

CREATE TABLE public.tb_user
(
    id          BIGINT      NOT NULL
        PRIMARY KEY,
    name        VARCHAR(64) NOT NULL,
    age         int         NOT NULL
);

同时，表中已经存在如下数据：

现有新增一批用户，名字集合：{"小红"，"张三"，"李四"，"王五"}

但是，业务上要求用户的名称必须是唯一的，所以我们需要给 name 列加上唯一索引

CREATE UNIQUE INDEX tb_user_name_uindex
    ON public.tb_user (name);

方案一

代码中先查询已有数据，然后把 小红 从新增集合中剔除，只插入不存在的记录

弊端：

1. 在并发场景下，如果不对这段操作做同步限制，可能会出现插入失败的场景：A 查询后插入之前，B 线程插入了新的数据 张三，A 在插入时，唯一性索引冲突，插入失败。
2. 多次进行数据库 IO 操作，效率不高

方案二

利用数据库提供的特性，实现此类需求

INSERT INTO ... ON CONFLICT DO NOTHING/UPDATE

INSERT INTO tb_user (name) 
VALUES ('小红'), ('张三'), ('李四'), ('王五')
ON CONFLICT (name) DO NOTHING

以上操作本身具有原子性，数据库会保障并发场景下的数据安全性，同时也避免的多次进行数据库 IO 操作

延伸扩展

不存在的数据插入，存在的数据更新

INSERT INTO tb_user (name,age) 
VALUES ('小红',21), ('张三',30), ('李四',31), ('王五',32)
ON CONFLICT (name) DO UPDATE SET age=EXCLUDED.age

不存在的数据插入，存在的数据不做操作；然后返回这批数据的 ID

INSERT INTO tb_user (name,age) 
VALUES ('小红',21), ('张三',30), ('李四',31), ('王五',32)
ON CONFLICT (name) DO UPDATE SET name=EXCLUDED.name
RETURNING id, name

当 name 存在时，更新 name 字段并不会导致数据有实质性的修改。此处更新主要为了获取到数据库中存在的数据的 ID 而已。比如以上 sql 执行完会获取到 小红的 ID 是 1

INSERT INTO tb_user (name,age) 
VALUES ('小红',21), ('张三',30), ('李四',31), ('王五',32)
ON CONFLICT (name) DO NOTHING
RETURNING id, name

以上这段 sql 就无法返回 小红的 ID 是 1，可以理解为只会返回被操作行的指定列