项目背景:
现在项目上所有数据库较多,最近研发需要对postgresql,mysql数据库进行类似oracle merge操作的运算,这里对oracle,mysql,postgresql merge 操作的用法进行实操与总结。
oracle merge
merge into的形式:
MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and […]…)
WHEN MATCHED THEN
[UPDATE sql]
WHEN NOT MATCHED THEN
[INSERT sql]
作用:判断B表和A表是否满足ON中条件,如果满足则用B表去更新A表,如果不满足,则将B表数据插入A表但是有很多可选项,如下:
1.正常模式
2.只update或者只insert
3.带条件的update或带条件的insert
4.全插入insert实现
5.带delete的update(觉得可以用3来实现)
postgresql merge
用了postgresql的一个update特性————RETURNING,返回一个update的结果集,因为查询条件的存在(也因为它是主键,是唯一),就会将两张表重叠的部分给过滤出来,再用where not exists将这些重叠的部分给忽略掉。这样就将数据merge进去了
with upsert as (
update test1 set name=test2.name from test2 where test1.id=test2.id
returning test1.*
)
insert into test1 select * from test2 where not exists (select 1 from upsert b where test2.id=b.id);
mysql merge
mysql> create table test1(id int,name varchar(100),phone varchar(12));
mysql> create table test2(id int,name varchar(100),phone varchar(12));
mysql> insert into test1 values(1,‘aa’,‘123’),(2,‘bb’,‘234’),(3,‘cc’,null);
mysql> insert into test2 values(1,‘aa’,‘123’),(2,‘bb’,‘234’),(3,‘cc’,‘345’);
mysql> select * from test1;
±-----±-----±------+
| id | name | phone |
±-----±-----±------+
| 1 | aa | 123 |
| 2 | bb | 234 |
| 3 | cc | NULL |
±-----±-----±------+
3 rows in set (0.00 sec)
mysql> select * from test2;
±-----±-----±------+
| id | name | phone |
±-----±-----±------+
| 1 | aa | 123 |
| 2 | bb | 234 |
| 3 | cc | 345 |
±-----±-----±------+
insert into test1(id,name,phone) select * from test2 on duplicate key update phone=values(phone);
结果:实现了将表tes2更新到表test1中去,存在就更新(可指定更新字段),不存在就插入
注意:id字段是主键或UNIQUE索引,不然只会插入dupnew表所有行数据