MySQL四种方法实现merge into

MERGE INTO：不存在则 insert, 存在则 update

**

Mysql 8.x 版本引入了 MERGE INTO 语法

**

Merge 语句的基本语法：

MERGEINTO target_table USING source_table ON condition
WHENMATCHEDTHENUPDATESET column1 = value1, column2 = value2,...WHENNOTMATCHEDTHENINSERT(column1, column2,...)VALUES(value1, value2,...);

• target_table: 要更新或插入数据的目标表。
• source_table: 包含要合并数据的来源表。
• condition: 指定用于匹配目标表和来源表数据的条件。
• WHEN MATCHED: 当目标表和来源表数据匹配时，执行的更新操作。
• WHEN NOT MATCHED: 当目标表和来源表数据不匹配时，执行的插入操作。

本文主要介绍将oracle中的merge into转换成MySQL的语法

在Oracle中，可以使用merge into，但是，MySQL中不支持，可以使用以下几种方法。

方法一：update + insert

merge其实就是不存在则insert，存在则update，所以可以把它拆分成：

update ... where exist(select 1 from ... where 条件)

insert .... where not exist(select 1 from ... where 条件)

先介绍where exists的用法

1.where exists

（1）介绍

exists和in都有过滤功能,他俩最大的差别就是in引导的子句只能对一个字段进行限制,比如

对id字段进行限定
select*from A where  sid in(1,2,3)

但是如果我们想对多个字段进行限制,使用in就不合适了,例如

select*from A where(sid,tid)in(select sid,tid from B)
不过很可惜，上面的语句只能再DB2上执行，SQL Server不行

此时就可以使用exists 来对多个字段进行限制了

select*from A whereexists(select1from B where A.sid=B.sid and A.tid=B.tid)

（2）原理

exists做为where条件时,是先对where 前的主查询询进行查询,然后用主查询的结果一个一个的代入exists的查询进行判断，如果为真则输出当前这一条主查询的结果，否则不输出。

查询时，一般情况下，子查询会分成两种情况:

1.子查询与外表的字段有关系时

select*from A whereexists(select1from B where A.sid=B.sid and A.tid=B.tid)

它先执行A表的查询,再将查询结果一条一条放到B表的条件中去查询,如果存在,则显示此条

2.子查询与外表的字段没有任何关联

select*from A whereexists(select*from B where B.id=‘条件‘)

在这种情况下，只要子查询的条件成立，就会查询出表1中的所有记录，反之，如果子查询中没有查询到记录，则表1不会查询出任何的记录。

以上两种方式本质上都是对A表查询进行过滤

2、update + insert

此种替代方式较为灵活，表可以无主键，自定义匹配规则。

注意：使用insert的时候需要加where not exists(select 1 from 表明 where 条件)，防止重复插入

insert可以不加 where exists

方法二：replace into

语法同insert into，使用简单，但有限制， replace into是根据主键去匹配，故replace into的表必须有主键，常用于单表更新新增。

REPLACE

的运行与

INSERT

很相似。只有一点例外，假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除 ！删除 ！

所以还需要你有删除数据的权限。

注意，除非表有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义。该语句会与INSERT相同，因为没有索引被用于确定是否新行复制了其它的行。

需要注意的问题就是replace into的时候会删除老记录。如果表中有一个自增的主键，那么就要出问题了。

方法三：on duplicate key update

在MYSQL中有语句

insert into ... on duplicate key update...

INSERTINTOtable(id, name, age)values(1,'yourname',18)ONDUPLICATEKEYUPDATE name='yourname', age=18;

id字段是主键或者UNIQUE索引。上述语句的作用是：

如果

id = 1

这条记录是不存在的，那么执行

INSERT INTO

语句。

如果

id = 1

在数据库中是存在的，那么执行

UPDATE

命令，此时这条语句相当于：

UPDATEtableSET name='yourname', age=18WHERE id=1;

再如果 age 字段也是UNIQUE的，相当于

UPDATEtableSET name='yourname'WHERE id=1OR age=18LIMIT1;

**执行

UPDATE

语句的条件是

INSERT

语句的执行会造成唯一键的重复。**

通常，应该尽量避免对带有多个唯一关键字的表使用

ON DUPLICATE KEY

子句。

还可以这样写：

INSERTINTOtable(SELECT id,'hisname'as name FROMtableWHERE id >=3)ONDUPLICATEKEYUPDATE name=VALUES(name);

这种方法还可以用来批量执行

UPDATE

操作（因为单条

UPDATE

语句只能执行一种update操作)

方法四：创建存储过程

CREATEPROCEDURE name()ifexists(select1from 表 where ID =@ID)beginUPDATE 表 SET XX= XX WHERE ID =@IDendelsebeginINSERT 表 VALUES(XX...)end

小菜鸟的个人博客已经正式上线且对外开放啦…

博客访问地址：小菜鸟的大梦想

欢迎扫码关注 ↓↓↓ 更多优质内容将 首发 公众号