Oracle、MySQL、SqlServer、PG数据库merge into语法实现总结

概述多表关联查询的时候会用到临时表插入数据，然后再用select查行查询，在往临时表里插入数据的时候，我们经常会用到判断如果临时表里有了这部分数据我们就要更新数据，如果临时表里没有这部分数据我们就要插入，这个时候可以怎么去实现呢？下面介绍各类型数据库实现的大致方式。一、存储过程实现各类型数据库都可以通过存储过程实现，因为是共性问题，所以就放前面了，这里以mysql数据库的存储过程为例。1、环境准备

淡定波007

5907人浏览 · 2021-04-18 21:34:47

淡定波007 · 2021-04-18 21:34:47 发布

概述

多表关联查询的时候会用到临时表插入数据，然后再用select查行查询，在往临时表里插入数据的时候，我们经常会用到判断如果临时表里有了这部分数据我们就要更新数据，如果临时表里没有这部分数据我们就要插入，这个时候可以怎么去实现呢？
下面介绍各类型数据库实现的大致方式。

一、存储过程实现

各类型数据库都可以通过存储过程实现，因为是共性问题，所以就放前面了，这里以mysql数据库的存储过程为例。

1、环境准备

--建表
create  table t1(
id  bigint(10),
name varchar(16),
sale bigint(10),
operatime datetime);
create table t2(
id  bigint(10),
name varchar(16),
sale bigint(20));

-- 插入数据
INSERT into t1 values(1,"xiaohong",1000,now());
INSERT into t1 values(2,"xiaoming",500,now());
INSERT into t2 values(1,"xiaohong",300);
INSERT into t2 values(2,"xiaoming",400);
INSERT into t2 values(3,"xiaoxiao",900);

在这里插入图片描述

2、存储过程实现

delimiter $
CREATE PROCEDURE merge_t2_to_t1 () BEGIN
-- 定义需要插入从a表插入b表的过程变量
DECLARE _ID bigint(10);
DECLARE _NAME VARCHAR (16);
DECLARE _SALE VARCHAR (16);

-- 游标遍历数据结束标志 
DECLARE done INT DEFAULT FALSE;
-- 游标指向a表结果集第一条-1位置
DECLARE cur_account CURSOR FOR SELECT ID, NAME,SALE FROM t2;
-- 游标指向a表结果集最后一条加1位置 设置结束标志
DECLARE CONTINUE HANDLER FOR NOT FOUND  SET done = TRUE;
-- 打开游标
OPEN cur_account;
-- 遍历游标
read_loop :
LOOP
--  取值a表当前位置数据到临时变量
    FETCH NEXT FROM cur_account INTO _ID,_NAME,_SALE;
 
-- 如果取值结束 跳出循环
IF done THEN LEAVE read_loop; 
END IF;
 
-- 当前数据做对比,如果b表存在则更新时间 不存在则插入
IF NOT EXISTS ( SELECT 1 FROM t1 WHERE ID = _ID AND NAME=_NAME ) 
    THEN
        INSERT INTO t1 (ID, NAME,sale,operatime) VALUES (_ID,_NAME,_sale,now());
    ELSE 
    UPDATE t1  set sale = _sale WHERE ID = _ID AND NAME=_NAME;
END IF;
 
END LOOP;
CLOSE cur_account;
 
END $

3、调用存储过程

call merge_t2_to_t1();

在这里插入图片描述

二、sqlserver的merge into语法

在SQL Server 2008的时候微软增加了一个强大的语句Merge。

1、语法

MERGE 语句是首先对源表和目标表都进行完全表扫描，然后拿源表和目标表检查，匹配条件，若成立则执行SQL语句1，不成立则执行SQL语句2,最执行SQL语句3。
语法：

MERGE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ INTO ] <操作表> --即将做插入、更新、删除的表
    USING <源表或者数据集或者子查询> --用户提供匹配条件来源的集合或者表
    ON <匹配条件> --可以是任意有效的条件组合
    [ WHEN MATCHED [ AND <clause_search_condition> ]--匹配条件成立
        THEN <SQL语句1> ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]--匹配条件不成立
        THEN <SQL语句2> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]--目标变不存在而源表存在的数据
        THEN <SQL语句3> ] 
;  -- 不要忘记分号

注意：
1）Merge操作的只是“操作表”，源表不会有任何变化
2）Merge的最后结尾必须是以分号结束的，不能忘了分号
3）语法严格要求关键字之间只能有一个英文空格,不能有多余的空格
4）不一定要把三个操作都写全，可以根据实际情况

2、实验

1)环境准备

--建表
create  table t1(
id   int,
name VARCHAR(16),
sale int,
Operatime date);
create  table t2(
id   int,
name VARCHAR(16),
sale int);

-- 插入数据
INSERT into t1 values(1,'xiaohong',1000,sysdate);
INSERT into t1 values(2,'xiaoming',500,sysdate);
INSERT into t2 values(1,'xiaohong',300);
INSERT into t2 values(2,'xiaoming',400);
INSERT into t2 values(3,'xiaoxiao',900);
commit;

在这里插入图片描述
2）merge into实现

MERGE INTO t1  USING  t2
ON t1.id-t2.id
WHEN MATCHED Then UpDate set t1.sale=t2.sale
When Not Matched 
When Not Matched then insert values(t2.id,t2.name,t2.sale,sysdate);
--Merge的最后结尾必须是以分号结束的，不能忘了分号 谨记:语法严格要求关键字之间只能有一个英文空格,不能有多余的空格

在这里插入图片描述

三、Oracle的merge into语法

1、语法

merge语法是根据源表对目标表进行匹配查询，匹配成功时更新，不成功时插入。

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]

2、实验

1)环境准备

--建表
create  table t1(
id   number,
name VARCHAR(16),
sale number,
Operatime date);
create  table t2(
id   number,
name VARCHAR(16),
sale number);

-- 插入数据
INSERT into t1 values(1,'xiaohong',1000,sysdate);
INSERT into t1 values(2,'xiaoming',500,sysdate);
INSERT into t2 values(1,'xiaohong',300);
INSERT into t2 values(2,'xiaoming',400);
INSERT into t2 values(3,'xiaoxiao',900);
commit;

在这里插入图片描述
2）Oracle merge into实现

merge into t1 using t2 on (t1.id=t2.id) WHEN MATCHED THEN
update set t1.sale=t2.sale
WHEN NOT MATCHED THEN
insert values(t2.id,t2.name,t2.sale,sysdate);

在这里插入图片描述

四、mysql的on duplicate key update语法

mysql并没有oracle、mssql的merge into语法，但是有个on duplicate key update语法（不是标准的sql语法）可以实现merge into语法。
ON DUPLICATE KEY UPDATE为Mysql特有语法，使用时应多注意主键和插入值是否是我们想要插入或修改的key、Value。
创建表，注意要有一个唯一索引 new_code_index，插入或者更新时，以此为标准。另外在高并发环境下，禁用insert into …on duplicate key update…,因为会造成mysql主从不一致。

1、相关语法

--单条记录下使用
--假如t1表的主键或者UNIQUE 索引是a，如果数据库里面已经存在a=1的记录则更新这条记录的c字段的值为原来值+1，然后返回值为2。
--如果不存在则插入a=1,b=2,c=3到数据库，然后返回值为1。
INSERT INTO t1(a,b,c) VALUES (1,2,3)   ON DUPLICATE KEY UPDATE c=c+1;
--多记录下使用
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)  ON DUPLICATE KEY UPDATE c=VALUES(c);

2、实验

1)环境准备

--建表
create  table t1(
id  bigint(10),
name varchar(16),
sale bigint(10),
operatime datetime,
UNIQUE KEY `idx_id` (`id`) USING BTREE
);
create table t2(
id  bigint(10),
name varchar(16),
sale bigint(20),
UNIQUE KEY `idx_id` (`id`) USING BTREE
);

-- 插入数据
INSERT into t1 values(1,"xiaohong",1000,now());
INSERT into t1 values(2,"xiaoming",500,now());
INSERT into t2 values(1,"xiaohong",300);
INSERT into t2 values(2,"xiaoming",400);
INSERT into t2 values(3,"xiaoxiao",900);

在这里插入图片描述
2）on duplicate key update实现

insert into t1 select id,name,sale,now() from t2 on duplicate key update sale=values(sale);

在这里插入图片描述
结果：实现了将表t2更新到表t1中去,存在就更新，不存在就插入,
注意：id字段是主键或UNIQUE索引，不然只会插入t2表所有行数据

五、mysql的replace into语法

Mysql replace与replace into都是经常会用到的功能；replace其实是做了一次update操作，而不是先delete再insert；而replace into其实与insert into很像，但对于replace into，假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。

1、相关语法

--前两种形式用的多些,对于那些没有给予值的列，MySQL将自动为这些列赋上默认值
replace into table(col, ...) values(...)
replace into table(col, ...) select ...
replace into table set col=value, ...

注意：
1）replace根据UNIQUE约束的字段(设置为Primary Key)，确定被替换的是哪一条记录。如果不存在要替换的记录，那么就会插入一条新的记录。
2）replace语句会删除原有的一条记录，并且插入一条新的记录来替换原记录。
3）一般用replace语句替换一条记录的所有列，如果在replace语句中没有指定某列，在replace之后这列的值被置空。
4）replace语句不能根据where子句来定位要被替换的记录。
5）常见update写法：update table set col=col+1 where id=1;
使用replace into不支持这样的写法：replace into table set col=col+1,id=1;

2、实验

1)环境准备

--建表
create  table t1(
id  bigint(10),
name varchar(16),
sale bigint(10),
operatime datetime,
UNIQUE KEY `idx_id` (`id`) USING BTREE
);
create table t2(
id  bigint(10),
name varchar(16),
sale bigint(20),
UNIQUE KEY `idx_id` (`id`) USING BTREE
);

-- 插入数据
INSERT into t1 values(1,"xiaohong",1000,now());
INSERT into t1 values(2,"xiaoming",500,now());
INSERT into t2 values(1,"xiaohong",300);
INSERT into t2 values(2,"xiaoming",400);
INSERT into t2 values(3,"xiaoxiao",900);

2）replace into实现

replace into t1 select id,name,sale,now() from t2;

在这里插入图片描述
结果：实现了将表t2更新到表t1中去,存在就更新，不存在就插入。

六、pg自定义函数实现

oracle数据库中有merge函数，可在插入数据前判断：如果指定列数据不存在，则正常插入数据；如果指定列数据存在，则将此条数据更新为插入的数据。 postgresql数据库中没有类似自带函数，只能自己实现此功能
以下方法只实现了oracle中merge函数的部分功能，而最大的问题是必须针对每个表创建自己的merge函数，比较适合在进行数据库迁移的时候配合外部表和触发器使用。

1、环境准备

--建表
create  table t1(
id  int PRIMARY KEY,
name varchar(16),
sale int,
operatime timestamp with time zone);

-- 插入数据
INSERT into t1 values(1,'xiaohong',1000,now());
INSERT into t1 values(2,'xiaoming',500,now());
commit;

在这里插入图片描述

2、pg自定义函数实现

该函数基于表级别。

CREATE FUNCTION merge_db(id_new INT,name_new varchar(10),sale_new int) RETURNS VOID AS
$$
BEGIN
LOOP
 UPDATE t1 SET sale = sale_new WHERE id = id_new and name = name_new;
 IF found THEN
       RETURN;
 END IF;
BEGIN
 INSERT INTO t1 VALUES (id_new,name_new,sale_new,now());
 RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;

在这里插入图片描述

3、实现

使用merge函数插入key列字段已经在表中存在的数据

select merge_db(1,'xiaohong',400);
select merge_db(2,'xiaoming',300);
select merge_db(3,'xiaoxiao',700);
select * from t1;

在这里插入图片描述

七、PG的WITH Queries语法

PostgreSQL中不直接支持merge into这个语法，但PostgreSQL可以使用WITH Queries (Common Table Expressions)的方法实现相同的功能。

1、语法

主要是利用了postgresql的一个update特性—RETURNING，返回一个update的结果集，因为查询条件的存在(也因为它是主键，是唯一)，就会将两张表重叠的部分给过滤出来，再用where not exists将这些重叠的部分给忽略掉，这样就将数据merge进去了。

[WITH with_queries] SELECT select_list FROM table_expression [sort_specification]

2、实验

1)环境准备

--建表
create  table t1(
id  int primary key,
name text,
sale int,
operatime timestamp
);
create  table t2(
id  int primary key,
name text,
sale int
);

-- 插入数据
INSERT into t1 values(1,'xiaohong',1000,now());
INSERT into t1 values(2,'xiaoming',500,now());
INSERT into t2 values(1,'xiaohong',300);
INSERT into t2 values(2,'xiaoming',400);
INSERT into t2 values(3,'xiaoxiao',900);

在这里插入图片描述
2）with 实现
用t2 这张表去更新t1 ，会将test1中没有的数据插入

WITH upsert AS (
  UPDATE t1  SET sale = t2.sale  FROM t2  WHERE t1.id = t2.id  RETURNING t1.*
 )
INSERT INTO t1 SELECT id,name,sale,now() FROM t2
WHERE NOT EXISTS ( SELECT 1  FROM upsert b  WHERE t2.id = b.id);

在这里插入图片描述

八、pg的UPSERT语法

PostgreSQL 9.5 引入了一项新功能，UPSERT(insert on conflict do)，当插入遇到约束错误时，直接返回，或者改为执行UPDATE。

1、语法

[ WITH [ RECURSIVE ] with_query [, ...] ]  
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]  
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }  
    [ ON CONFLICT [ conflict_target ] conflict_action ]  
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]  
  
where conflict_target can be one of:  
  
    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]  
    ON CONSTRAINT constraint_name  
  
and conflict_action is one of:  
  
    DO NOTHING  
    DO UPDATE SET { column_name = { expression | DEFAULT } |  
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |  
                    ( column_name [, ...] ) = ( sub-SELECT )  
                  } [, ...]  
              [ WHERE condition ]

2、实验

1)环境准备

--建表
create  table t1(
id  int primary key,
name text,
sale int,
operatime timestamp
);
create  table t2(
id  int primary key,
name text,
sale int
);

-- 插入数据
INSERT into t1 values(1,'xiaohong',1000,now());
INSERT into t1 values(2,'xiaoming',500,now());
INSERT into t2 values(1,'xiaohong',300);
INSERT into t2 values(2,'xiaoming',400);
INSERT into t2 values(3,'xiaoxiao',900);

在这里插入图片描述
2）insert on implict实现

--不存在则插入，存在则更新
insert into t1 select id,name,sale,now() from t2 on conflict (id) do update set sale=excluded.sale;
--不存在则插入，存在则直接返回(不做任何处理)
insert into t1 select id,name,sale,now() from t2 on conflict (id) do nothing;