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是先删除重复记录,然后再插入新记录。个人以为应当慎用。

 

posted @ 2020-06-24 13:22  闻歌感旧  阅读(850)  评论(0编辑  收藏  举报