Oracle、MySQL兼容 -- merge into

Oracle、MySQL兼容 – merge into

1、测试环境及数据

​ Oracle 11g、MySQL 5.7

CREATE TABLE people_source ( 
  person_id  INTEGER NOT NULL PRIMARY KEY, 
  first_name VARCHAR2(20) NOT NULL, 
  last_name  VARCHAR2(20) NOT NULL, 
  title      VARCHAR2(10) NOT NULL 
);

CREATE TABLE people_target ( 
  person_id  INTEGER NOT NULL PRIMARY KEY, 
  first_name VARCHAR2(20) NOT NULL, 
  last_name  VARCHAR2(20) NOT NULL, 
  title      VARCHAR2(10) NOT NULL 
);

INSERT INTO people_target VALUES (1, 'John', 'Smith', 'Mr');
INSERT INTO people_target VALUES (2, 'alice', 'jones', 'Mrs');
INSERT INTO people_source VALUES (2, 'Alice', 'Jones', 'Mrs.');
INSERT INTO people_source VALUES (3, 'Jane', 'Doe', 'Miss');
INSERT INTO people_source VALUES (4, 'Dave', 'Brown', 'Mr');

2、Oracle – merge into

官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/MERGE.html#GUID-5692CCB7-24D9-4C0E-81A7-A22436DC968F

-- merge into语法规则
	MERGE [ hint ]
   INTO [ schema. ] { table | view } [ t_alias ]
   USING { [ schema. ] { table | view }
         | subquery 
         } [ t_alias ]
   ON ( condition )
   [ merge_update_clause ]
   [ merge_insert_clause ]
   [ error_logging_clause ] ;
   
-- 更新语法
merge_update_clause:
WHEN MATCHED THEN
UPDATE SET column = { expr | DEFAULT }
           [, column = { expr | DEFAULT } ]...
[ where_clause ]
[ DELETE where_clause ]

-- 新增语法
merge_insert_clause:
WHEN NOT MATCHED THEN
INSERT [ (column [, column ]...) ]
VALUES ({ expr | DEFAULT }
          [, { expr | DEFAULT } ]...
       )
[ WHERE condition ]

-- 错误日志
error_logging_clause:
LOG ERRORS 
  [ INTO [schema.] table ]
  [ (simple_expression) ]
  [ REJECT LIMIT { integer | UNLIMITED } ]

-- 官方例子
MERGE INTO people_target pt 
USING people_source ps 
ON    (pt.person_id = ps.person_id) 
WHEN matched THEN UPDATE 
  SET pt.first_name = ps.first_name, 
      pt.last_name = ps.last_name, 
      pt.title = ps.title 
  DELETE where pt.title  = 'Mrs.' 
WHEN NOT matched THEN INSERT 
  (pt.person_id, pt.first_name, pt.last_name, pt.title) 
  VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title) 
  WHERE ps.title = 'Mr';

3、MySQL – INSERT … ON DUPLICATE KEY UPDATE

insert into people_target 
select person_id,first_name,last_name,title from people_source s
ON DUPLICATE KEY update first_name = s.first_name, last_name = s.last_name, title = s.title;

此方法必须要有主键或唯一键，且插入数据为全字段数据插入。

4、MySQL采用存储过程，这种方法复杂但更加灵活

-- delimiter 定义边界符，若不定义边界符则存储过程中不能出现分号;
delimiter $$ 
-- 删除已有存储过程
drop procedure if exists people_procedure $$
-- 创建存储过程
create procedure people_procedure()
begin
	-- 原表是否已有此条数据
	declare num int;
	-- 定义接受游标值的变量
	declare id int(11);
	declare fname varchar(20);
	declare lname varchar(20);
	declare tit   varchar(10);
	-- 定义循环终止标志
	declare loop_finished int default 0;
	-- 定义游标
	declare people_cursor cursor for (select person_id, first_name, last_name, title from people_source);
	-- 循环达到最后位置时，设置loop_finished=1
	declare continue handler for not found set loop_finished = 1;
	-- 开启游标
	open people_cursor;
	-- 循环遍历游标
	people_loop: loop
		-- 获取游标中的值
		fetch people_cursor into id, fname, lname, tit;
		-- 若loop_finished = 1时，跳出循环
		if loop_finished = 1 then
			leave people_loop;
		end if;
		-- 查询目标表是否已有此条数据
		select count(*) into num from people_target where person_id = id;
		
		if num > 0 then 
			-- 已存在则更新
			update people_target set first_name = fname, last_name = lname, title = tit where person_id = id;
		else 
			-- 不存在则新增
			insert into people_target(person_id, first_name, last_name, title) values(id, fname, lname, tit);
		end if; 
		
	-- 结束循环
	end loop people_loop;
	-- 关闭游标
	close people_cursor;
end $$
-- 取消边界符
delimiter ;

-- 调用存储过程
call people_procedure();

-- 查看现有的存储过程信息
show procedure status;
-- 查看存储过程 people_procedure的详细信息
show create procedure people_procedure;

5、补充merge into 的另外两种实现方式 replace into、update + insert