章
目
录
本文主要讲解关于MySQL实现merge into的4种方法相关内容,让我们来一起了解下吧!
在Oracle中,可以使用merge into,但是,MySQL中不支持,可以使用以下几种方法。
顺便说下,所谓MERGE INTO:其实就是不存在则 insert, 存在则 update
方法一: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 where exists (select 1 from B where A.sid=B.sid and A.tid=B.tid)
(2)原理
exists做为where条件时,是先对where 前的主查询询进行查询,然后用主查询的结果一个一个的代入exists的查询进行判断,如果为真则输出当前这一条主查询的结果,否则不输出。
查询时,一般情况下,子查询会分成两种情况:
1.子查询与外表的字段有关系时
select * from A where exists (select 1 from B where A.sid=B.sid and A.tid=B.tid)
它先执行A表的查询,再将查询结果一条一条放到B表的条件中去查询,如果存在,则显示此条
2.子查询与外表的字段没有任何关联
select * from A where exists (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...
INSERT INTO table (id, name, age) values (1, 'yourname', 18)
ON DUPLICATE KEY UPDATE name='yourname', age=18;
id字段是主键或者UNIQUE索引。上述语句的作用是:
如果id = 1
这条记录是不存在的,那么执行INSERT INTO
语句。
如果id = 1
在数据库中是存在的,那么执行UPDATE
命令,此时这条语句相当于:
UPDATE table SET name='yourname', age=18 WHERE id=1;
再如果 age 字段也是UNIQUE的,相当于
UPDATE table SET name='yourname' WHERE id=1 OR age=18 LIMIT 1;
执行UPDATE
语句的条件是INSERT
语句的执行会造成唯一键的重复。
通常情况下,我们应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY
子句。
还可以这样写:
INSERT INTO table
(SELECT id, 'hisname' as name FROM table WHERE id >= 3)
ON DUPLICATE KEY UPDATE name=VALUES(name);
这种方法还可以用来批量执行UPDATE
操作(因为单条UPDATE
语句只能执行一种update操作)
方法四:创建存储过程
我们看一下如何使用存储过程实现mysql的merge into,类似代码如下:
CREATE PROCEDURE name()
if exists(select 1 from 表 where ID = @ID)
begin
UPDATE 表 SET XX= XX WHERE ID = @ID
end
else
begin
INSERT 表 VALUES(XX...)
end
当然使用存储过程较为麻烦,也不是特别推荐。
好啦,以上就是关于MySQL实现merge into的4种方法相关的全部内容,希望对你有帮助。欢迎持续关注潘子夜个人博客(www.panziye.com),学习愉快哦!