POSTGRES、MYSQL插入数据的UPDATE_INSERT实践
POSTGRES:
后续操作均建立在如下版本 14.1 基础之上，低版本的POSTGRES还不支持ON CONFLICT 命令，可以通过给表创建 RULE 达到UPDATE_INSERT效果
1、创建表
create table tbl_user(
id serial PRIMARY KEY,
name varchar(256),
addr varchar(256),
age int,
score int,
fav varchar(256)
);
2、创建唯一约束
alter table tbl_user add constraint name_add_age_unique unique(name,addr,age);
3、首先插入两条数据
INSERT INTO tbl_user
(name, addr, age, score, fav)
VALUES
('aaa','aaa_addr',10,22,'aaa_fav'),
('bbb','ccc_addr',10,22,'bbb_fav');
4、此时直接INSERT INTO 主键重复的数据，会直接报唯一键冲突，插入失败
INSERT INTO tbl_user
(name, addr, age, score, fav)
VALUES
('aaa','aaa_addr',10, 23,'aaa_fav_new'),
('bbb','ccc_addr',10, 23,'bbb_fav_new'),
('ddd','ddd_addr',10, 22,'ccc_fav'),
('eee','eee_addr',10, 22,'ddd_fav');
5、采用UPDATE_INSERT方式，插入成功，并且对冲突列执行指定更新操作
语法说明：
1、ON conflict(唯一约束键列表) DO，用于指定对于冲突的唯一键，可以是联合唯一键
2、excluded.列名 用于获取插入的时入参
INSERT INTO tbl_user
(name, addr, age, score, fav)
VALUES
('aaa','aaa_addr',10, 23,'aaa_fav_new'),
('bbb','ccc_addr',10, 23,'bbb_fav_new'),
('ddd','ddd_addr',10, 22,'ccc_fav'),
('eee','eee_addr',10, 22,'ddd_fav')
ON conflict(name,addr,age) DO
UPDATE set score=excluded.score, fav=excluded.fav;
6、在唯一索引列中存在 null 时，会导致前述 UPDATE_INSERT 失效
例如：
1）在tbl_user中先插入一条数据：
INSERT INTO tbl_user
(name, addr, age, score, fav)
VALUES
('aaa',null,10, 23,'aaa_fav_new')
ON conflict(name,addr,age) DO
UPDATE set score=excluded.score, fav=excluded.fav;
2）再次执行相同的语句，插入数据，发现出现了两条一样的数据，UPDATE_INSERT 操作失效了，原因在于addr为null,导致前述创建的唯一索引(name,age,addr) 失效了
INSERT INTO tbl_user
(name, addr, age, score, fav)
VALUES
('aaa',null,10, 23,'aaa_fav')
ON conflict(name,addr,age) DO
UPDATE set score=excluded.score, fav=excluded.fav;
解决方案：
创建唯一索引的时候通过COALESCE指定索引默认值
例：
1）重新创建表
create table tbl_user(
id serial PRIMARY KEY,
name varchar(256),
addr varchar(256),
age int,
score int,
fav varchar(256)
)
2）创建唯一索引，注意使用COALESCE指定默认值
CREATE UNIQUE INDEX tbl_user_solution_idx
ON tbl_user (COALESCE(name,''),COALESCE(addr,''),COALESCE(age,-1));
3）插入预置数据，注意构造 索引为null的情况
INSERT INTO tbl_user
(name, addr, age, score, fav)
VALUES
('aaa',null,10, 23,'aaa_fav');
4）再次执行相同的空值插入，会报唯一索引冲突, 说明唯一约束生效
INSERT INTO tbl_user
(name, addr, age, score, fav)
VALUES
('aaa',null,10, 23,'aaa_fav');
5）使用UPDATE_INSERT进行数据插入
INSERT INTO tbl_user
(name, addr, age, score, fav)
VALUES
('aaa',null,10, 23,'aaa_fav_new')
ON conflict(COALESCE(name,''),COALESCE(addr,''),COALESCE(age,-1))
DO
UPDATE set score=excluded.score, fav=excluded.fav;
MYSQL:
后续操作均建立在如下版本 8.0.27 基础之上，其他版本可以查询替换方式
1、创建表
create table tbl_user(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(256),
addr varchar(256),
age int,
score int,
fav varchar(256)
);
2、创建唯一约束
alter table tbl_user add constraint name_add_age_unique unique(name,addr,age);
3、首先插入两条数据
INSERT INTO tbl_user
(name, addr, age, score, fav)
VALUES
('aaa','aaa_addr',10,22,'aaa_fav'),
('bbb','ccc_addr',10,22,'bbb_fav');
4、直接INSERT INTO，会直接报唯一键冲突，插入失败
INSERT INTO tbl_user
(name, addr, age, score, fav)
VALUES
('aaa','aaa_addr',10, 23,'aaa_fav_new'),
('bbb','ccc_addr',10, 23,'bbb_fav_new'),
('ddd','ddd_addr',10, 22,'ccc_fav'),
('eee','eee_addr',10, 22,'ddd_fav')
5、采用UPDATE_INSERT方式，插入成功，并且对冲突列执行指定更新操作
语法说明：
1、ON DUPLICATE KEY，用于识别唯一键冲突操作
2、CALUES(列名)， 用于获取插入的时入参
INSERT INTO tbl_user
(name, addr, age, score, fav)
VALUES
('aaa','aaa_addr',10, 23,'aaa_fav_new'),
('bbb','ccc_addr',10, 23,'bbb_fav_new'),
('ddd','ddd_addr',10, 22,'ccc_fav'),
('eee','eee_addr',10, 22,'ddd_fav')
ON DUPLICATE KEY
UPDATE score=VALUES(score), fav=VALUES(fav);
6、在唯一索引列中存在 null 时，会导致前述 UPDATE_INSERT 失效
解决方案：才疏学浅，暂时采用存储过程，在插入数据进行判断处理，若有其他更好的方案，请大佬指点
1）在建表之后，创建存储过程
主要逻辑：查询name、addr、age 相同的记录，若是存在相同的记录（唯一性) 则执行update更新，否则执行insert插入
注意：
1、创建存储过程时，为了避免存储过程中分号的影响，需要先声明语句分隔符为反斜线， delimiter // ，在创建完成之后，重新将其声明 delimiter ;
delimiter //
CREATE PROCEDURE update_insert_tbl_user(IN in_name VARCHAR(256),IN in_addr VARCHAR(256), IN in_age INT, IN in_score INT, IN in_fav VARCHAR(256))
BEGIN
DECLARE total INT;
select count(*) into total from tbl_user
where ((name is null and in_name is null) or name = in_name)
and ((addr is null and in_addr is null) or addr = in_addr)
and ((age is null and in_age is null) or age = in_age);
if total > 0 THEN
update tbl_user set score = in_score, fav = in_fav
where ((name is null and in_name is null) or name = in_name)
and ((addr is null and in_addr is null) or addr = in_addr)
and ((age is null and in_age is null) or age = in_age);
else
insert into tbl_user(name, addr, age, score, fav) values(in_name, in_addr, in_age, in_score, in_fav);
end if;
END;
//
delimiter ;
2）验证存储过程
先插入两条数据，注意插入数据的addr列为null
INSERT INTO tbl_user
(name, age, score, fav)
VALUES
('aaa',10, 23,'aaa_fav');
INSERT INTO tbl_user
(name, addr, age, score, fav)
VALUES
('bbb', null,10, 23,'bbb_fav');
通过存储过程执行数据查询：
call update_insert_tbl_user('aaa',null,10,23,'aaa_fav_new');
发现如预期更新了fav字段
