MySQL 等效 INSERT IGNORE

使用 EXCEPTION WHEN DUP_VAL_ON_INDEX 处理重复记录错误是一种好习惯，还是使用 MERGE 更好？

提前分析数据并据此做出决策是一种很好的做法。

• 预计要插入多少行数据
• 重复数与新行数之比
• 第三方数据源和不可预测

比较可能的解决方案

EXCEPTION WHEN DUP_VAL_ON_INDEX使用in或 trigger捕获异常FOR ... LOOP显然不是比在插入之前显式检查重复数据更好的选择。因此，我不再考虑它。

假设已经有 50 万行，一个数据源有 100 万行，其中一半是新记录。应该注意的是，改变这个比率会显着影响结果。还从@volkovirus 获取了替代答案以进行比较。

create table t1 as
    select rownum id, 'memo '||rownum memo
    from dual connect by level <= 5e5;

create or replace view source as 
    select rownum id, 'memo '||rownum memo
    from dual connect by level <= 1e6; 

alter table t1 add constraint pk_t1 primary key (id);     
exec dbms_errlog.create_error_log (dml_table_name => 't1')

运行时间比较：

declare 
    n number; 
begin
    --#1 not exists
    n := dbms_utility.get_time; 
    insert into t1 select * from select * from source s
    where not exists (select 1 from t1 t where t.id = s.id);
    dbms_output.put_line ('not exists elapsed='||(dbms_utility.get_time - n)/100||' ('||sql%rowcount||' rows)');
    rollback;

    --#2 merge
    n := dbms_utility.get_time; 
    merge into t1 t using source s on (s.id = t.id)
    when not matched then insert values (s.id, s.memo);
    dbms_output.put_line ('merge      elapsed='||(dbms_utility.get_time - n)/100||' ('||sql%rowcount||' rows)');
    rollback;

    --#3 log errors 
    n := dbms_utility.get_time; 
    insert into t1 select * from source s
    log errors reject limit unlimited;
    dbms_output.put_line ('w/ errlog  elapsed='||(dbms_utility.get_time - n)/100||' ('||sql%rowcount||' rows)');
    rollback;
end;
/

结果：

Elapsed: 00:10:27.073

not exists elapsed=7,97 (500000 rows)
merge      elapsed=7,76 (500000 rows)
w/ errlog  elapsed=609,96 (500000 rows)

结论：

• 如果不需要更新/删除旧记录，
  那么检查#1 not exists就足够了
• 如果您需要更新/删除旧记录，
  那么肯定#2 MERGE
• 如果您需要立即记录错误并对其执行任何操作，
  那么这#3 log errors将是一个合适的解决方案

这一切都取决于你最终想要什么。MERGE在您的情况下，可以更新匹配字段上的记录。

您也可以不生成еxception，而是将日志表中出现插入错误的所有行都丢弃，稍后您将对其进行分析并做出决定：

为表创建一个错误日志表：

begin
  dbms_errlog.create_error_log (dml_table_name => 'table1');
end;

接下来，我们插入自身table1：

insert into table1
select *
from source_table
log errors into err$_table1 ('INSERT') reject limit unlimited;

您可以保留插入内容并添加IGNORE_ROW_ON_DUPKEY_INDEX提示。

文档指出，此提示是一般规则的例外，以避免在生产环境中使用提示：

注意：CHANGE_DUPKEY_ERROR_INDEX、和IGNORE_ROW_ON_DUPKEY_INDEX提示RETRY_ON_ROW_CHANGE与其他提示的不同之处在于它们具有语义效果。
提示中解释的一般哲学不适用于这三个提示。

create table tab (a number unique);
/
Table TAB created.

insert into tab
select 1 from dual union all
select 1 from dual
/
ORA-00001: unique constraint (DB.SYS_C0012764) violated

insert /*+ ignore_row_on_dupkey_index (tab (a)) */ into tab
select 1 from dual union all
select 1 from dual
/

1 row inserted.