[大数据]Mysql/Oracle 批量新增(存在则更新)

一、需求说明

1、记录用户多次每周考核的表t_result，表字段以及表数据如下：
result：0 参加考核 1通过 2未通过

2、从另一个平台获得的 用户考核记录临时表t_result_tmp，表字段和数据如下：

3、根据临时表t_result_tmp，更新t_result表：时间相同的更新，时间不存在的新增数据。

二、Mysql实现：批量新增(存在则更新)

sql文

INSERT INTO t_result ( id, uuid, result, `time`, creator, create_time ) 
    (
		SELECT DISTINCT
			tr.id,
			trt.uuid,
			trt.result,
			trt.`time`,
			'平台A',
			NOW() 
		FROM
			t_result_tmp trt
			LEFT JOIN t_result tr ON tr.uuid = trt.uuid AND tr.`time` = trt.`time` 
	) 
	ON DUPLICATE KEY UPDATE result = VALUES(result), editor = '平台A', edit_time = NOW()

ON DUPLICATE KEY UPDATE 方法说明

在insert的时候出现重复主键的时候，会执行后面的update语句。
重复主键包含：①主键 ②唯一索引

update字段可以直接赋固定值，写法如例子columnName = '平台A'；
也可以赋值当前冲突数据中的值，写法如例子columnName = VALUES(columnName)。

三、Oracle实现：批量新增(存在则更新)

sql文

MERGE INTO t_result TR
USING (
    SELECT UUID,
           RESULT,
           TIME
    FROM t_result_tmp
) TRT
ON (TR.UUID = TRT.UUID AND TR.TIME = TRT.TIME)
WHEN MATCHED THEN
    UPDATE
    SET TR.RESULT = TRT.RESULT,
        TR.EDITOR = '平台A',
        TR.EDIT_TIME = NOW()
WHEN NOT MATCHED THEN
    INSERT ( UUID, RESULT, TIME, CREATOR, CREATE_TIME )
    VALUES ( TRT.UUID, TRT.RESULT, TRT.TIME, '平台A', NOW() )

MERGE 语句语法

MERGE [hint] INTO [schema.] table [t_alias]
USING
[schema.]{ table | view | subquery } [t_alias]
ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause

① USING的数据集避免为null，数据库不为空的字段A，可以在ON后面加上条件 A IS NOT NULL
② ON子句的使用的字段不能够用于update，即Oracle不允许更新用于连接的列
③ 更新/插入的sql，不需要再写表名，merge后面已经加过表名。
update set 列 = 固定值 或者 update set 列 = using的表字段
insert values (值) 或者 insert (指定字段) values (对应的字段值)
④ update语句也可以加where条件，相当于on的补充写法