Oracle, PostgreSQl, MySQL针对INSERT时可能唯一键重复的SQL
一、问题提出
向数据库INSERT记录时,经常会遇到唯一键(包括主键)重复而执行失败的情况。最简单和原始的解决办法,是先用SELECT判断唯一键是否存在,然后再用IF...THEN...ELSE分别执行不同的操作。
能不能用一条SQL直接完成上述功能?当然可以,但Oracle, PostgreSQL和MySQL对此的SQL写法是不一样的。
为叙述方便,假设有表定义如下:
Create TABLE Test_Table ( Ukey_A int not null, Ukey_B varchar(10) not null, Col_C varchar(100), primary key (Ukey_A, Ukey_B) );
插入操作有两种情形需要考虑,一种是不重复INSERT否则UPDATE,另一种是不重复INSERT否则Do Nothing。
二、Oracle
Oracle可使用Merge Into来实现此功能,大概在9i时已经支持,后来在10g时又进行了增强。
示例SQL如下:
-- 情形1 MERGE INTO Test_Table A USING (SELECT COUNT(*) AS cnt FROM Test_Table WHERE Ukey_A=1 AND Ukey_B='bbb') B ON (B.cnt>0) WHEN MATCHED THEN UPDATE SET Col_c = 'cccccc'; WHEN NOT MATCHED THEN INSERT VALUES(1, 'bbb', 'ccccc'); -- 情形2 MERGE INTO Test_Table A USING (SELECT COUNT(*) AS cnt FROM Test_Table WHERE Ukey_A=1 AND Ukey_B='bbb') B ON (B.cnt>0) WHEN NOT MATCHED THEN INSERT VALUES(1, 'bbb', 'ccccc');
实际上,Oracle的Merge Into功能远比这里讲到的强大,限于篇幅不展开。
三、PostgreSQL
PostgreSQL可使用On Conflict语法,在9.5版后提供。
示例SQL如下:
-- 情形1 INSERT INTO Test_Table VALUES (1, 'aaa', 'ccccc') ON CONFLICT (Ukey_A, Ukey_B) DO UPDATE Col_C = 'ccccc'; -- 情形2 INSERT INTO Test_Table VALUES (1, 'aaa', 'ccccc') ON CONFLICT (Ukey_A, Ukey_B) DO NOTHING;
四、MySQL
MySQL对此有多种选择:REPLACE、IGNORE和ON DUPLICATE KEY等。对于情形一可以使用ON DUPLICATE KEY或REPLACE,对于情形2只能使用IGNORE。
示例SQL如下:
-- 情形1 INSERT INTO Test_Table VALUES (1, 'aaa', 'ccccc') ON DUPLICATE KEY UPDATE Col_C = 'ccccc'; -- 情形2 INSERT IGNORE INTO Test_Table VALUES (1, 'aaa', 'ccccc');
REPLACE是先删除重复记录,然后再插入新记录。个人以为应当慎用。
声明:内容可以转载,但须标明出处(http://www.cnblogs.com/wggj),请尊重作者(闻歌感旧)的劳动,谢谢!