Oracle、MySQL、SqlServer、PG数据库merge into语法实现总结
概述
多表關聯查詢的時候會用到臨時表插入數據，然后再用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 ) THENINSERT 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。
語法：
注意：
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實現
三、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實現
四、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實現
結果：實現了將表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 LOOPUPDATE t1 SET sale = sale_new WHERE id = id_new and name = name_new;IF found THENRETURN;END IF; BEGININSERT 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中沒有的數據插入
八、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實現
總結
以上是生活随笔為你收集整理的Oracle、MySQL、SqlServer、PG数据库merge into语法实现总结的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 【2021版】吐血整理_专升本计算机文化
- 下一篇： NOIP2017提高组模拟赛4 （总结）