Mysql 8.x 版本引入了 MERGE INTO 語(yǔ)法

Merge 語(yǔ)句的基本語(yǔ)法：

MERGE INTO target_table USING source_table ON condition
WHEN MATCHED THEN
    UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...) VALUES (value1, value2, ...);

• target_table: 要更新或插入數(shù)據(jù)的目標(biāo)表。
• source_table: 包含要合并數(shù)據(jù)的來(lái)源表。
• condition: 指定用于匹配目標(biāo)表和來(lái)源表數(shù)據(jù)的條件。
• WHEN MATCHED: 當(dāng)目標(biāo)表和來(lái)源表數(shù)據(jù)匹配時(shí)，執(zhí)行的更新操作。
• WHEN NOT MATCHED: 當(dāng)目標(biāo)表和來(lái)源表數(shù)據(jù)不匹配時(shí)，執(zhí)行的插入操作。

本文主要介紹將oracle中的merge into轉(zhuǎn)換成MySQL的語(yǔ)法

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

方法一：update + insert

merge其實(shí)就是不存在則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都有過(guò)濾功能,他倆最大的差別就是in引導(dǎo)的子句只能對(duì)一個(gè)字段進(jìn)行限制,比如

對(duì)id字段進(jìn)行限定
select * from A where  sid in (1,2,3)

但是如果我們想對(duì)多個(gè)字段進(jìn)行限制,使用in就不合適了,例如

select * from A where (sid,tid) in (select sid,tid from B)
不過(guò)很可惜，上面的語(yǔ)句只能再DB2上執(zhí)行，SQL Server不行

此時(shí)就可以使用exists 來(lái)對(duì)多個(gè)字段進(jìn)行限制了

select * from A where exists (select 1 from B where A.sid=B.sid and A.tid=B.tid)

（2）原理

exists做為where條件時(shí),是先對(duì)where 前的主查詢?cè)冞M(jìn)行查詢,然后用主查詢的結(jié)果一個(gè)一個(gè)的代入exists的查詢進(jìn)行判斷，如果為真則輸出當(dāng)前這一條主查詢的結(jié)果，否則不輸出。

查詢時(shí)，一般情況下，子查詢會(huì)分成兩種情況:

1.子查詢與外表的字段有關(guān)系時(shí)

select * from A where exists (select 1 from B where A.sid=B.sid and A.tid=B.tid)

它先執(zhí)行A表的查詢,再將查詢結(jié)果一條一條放到B表的條件中去查詢,如果存在,則顯示此條

2.子查詢與外表的字段沒有任何關(guān)聯(lián)

select * from A where exists (select * from B where B.id=‘條件‘)

在這種情況下，只要子查詢的條件成立，就會(huì)查詢出表1中的所有記錄，反之，如果子查詢中沒有查詢到記錄，則表1不會(huì)查詢出任何的記錄。

以上兩種方式本質(zhì)上都是對(duì)A表查詢進(jìn)行過(guò)濾

2、update + insert

此種替代方式較為靈活，表可以無(wú)主鍵，自定義匹配規(guī)則。

注意：使用insert的時(shí)候需要加where not exists(select 1 from 表明 where 條件)，防止重復(fù)插入

insert可以不加 where exists

方法二：replace into

 語(yǔ)法同insert into，使用簡(jiǎn)單，但有限制， replace into是根據(jù)主鍵去匹配，故replace into的表必須有主鍵，常用于單表更新新增。

REPLACE的運(yùn)行與INSERT很相似。只有一點(diǎn)例外，假如表中的一個(gè)舊記錄與一個(gè)用于PRIMARY KEY或一個(gè)UNIQUE索引的新記錄具有相同的值，則在新記錄被插入之前，舊記錄被刪除 ！刪除 ！

所以還需要你有刪除數(shù)據(jù)的權(quán)限。

注意，除非表有一個(gè)PRIMARY KEY或UNIQUE索引，否則，使用一個(gè)REPLACE語(yǔ)句沒有意義。該語(yǔ)句會(huì)與INSERT相同，因?yàn)闆]有索引被用于確定是否新行復(fù)制了其它的行。

需要注意的問題就是replace into的時(shí)候會(huì)刪除老記錄。如果表中有一個(gè)自增的主鍵，那么就要出問題了。

方法三：on duplicate key update

在MYSQL中有語(yǔ)句 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索引。上述語(yǔ)句的作用是：

如果id = 1這條記錄是不存在的，那么執(zhí)行INSERT INTO語(yǔ)句。

如果id = 1在數(shù)據(jù)庫(kù)中是存在的，那么執(zhí)行UPDATE命令，此時(shí)這條語(yǔ)句相當(dāng)于：

UPDATE table SET name='yourname', age=18 WHERE id=1; 

再如果 age 字段也是UNIQUE的，相當(dāng)于

UPDATE table SET name='yourname' WHERE id=1 OR age=18 LIMIT 1; 

執(zhí)行UPDATE語(yǔ)句的條件是INSERT語(yǔ)句的執(zhí)行會(huì)造成唯一鍵的重復(fù)。

通常，應(yīng)該盡量避免對(duì)帶有多個(gè)唯一關(guān)鍵字的表使用ON DUPLICATE KEY子句。

還可以這樣寫：

INSERT INTO table 
    (SELECT id, 'hisname' as name FROM table WHERE id >= 3)
ON DUPLICATE KEY UPDATE name=VALUES(name);

這種方法還可以用來(lái)批量執(zhí)行UPDATE操作（因?yàn)閱螚lUPDATE語(yǔ)句只能執(zhí)行一種update操作)

方法四：創(chuàng)建存儲(chǔ)過(guò)程

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

總結(jié) 

到此這篇關(guān)于MySQL實(shí)現(xiàn)merge into四種方法的文章就介紹到這了,更多相關(guān)MySQL實(shí)現(xiàn)merge into內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論