數(shù)據(jù)庫中批量插入數(shù)據(jù)時主鍵沖突怎么辦

小編給大家分享一下數(shù)據(jù)庫中批量插入數(shù)據(jù)時主鍵沖突怎么辦，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

客戶有這樣一個需求：有一個大表A，有主鍵，經(jīng)常需要往這個表中批量插入大量數(shù)據(jù)，但插入的數(shù)據(jù)可能自身重復(fù)或者跟表A重復(fù)。

下面測試幾種插入的方法：

DB:ORACLE 11.2.0.4

新建測試表：

新建表scott.tb_01

create table scott.tb_01

as

SELECT level c1,level c2,level c3

FROM DUAL

CONNECT BY LEVEL <= 100000;

在表scott.tb_01上增加主鍵

create unique index scott.pk_tb_01 on scott.tb_01(c1); --這一步不是必需的，因為下一步會自動建索引，但這種建索引再加主鍵的方式可以在建索引的時候加并行。

alter table scott.tb_01 add constraint pk_tb_01 primary key (c1)   using index;

新建表scott.tb_02

create table scott.tb_02

as

SELECT level+100000 c1,level c2,level c3

FROM DUAL

CONNECT BY LEVEL <= 100000;

在表scott.tb_02上插入表scott.tb_01的100條數(shù)據(jù)做為重復(fù)數(shù)據(jù)

insert into scott.tb_02

SELECT c1,c2, c3

FROM scott.tb_01

where rownum <= 100;

commit;

現(xiàn)在需要將表scott.tb_02中和表scott.tb_01非重復(fù)的數(shù)據(jù)插入到scott.tb_01

方法1：關(guān)聯(lián)插入

INSERT INTO SCOTT.TB_01

SELECT A.* --這里如果表SCOTT.TB_02自身有重復(fù)數(shù)據(jù)，還要加上distinct

FROM SCOTT.TB_02 A

LEFT JOIN SCOTT.TB_01 B

ON A.c1 = B.c2

WHERE B.c1 IS  NULL;

100000 rows created.

上面一種常見的插入方法，這個方法的的問題在于，如果表SCOTT.TB_02和表SCOTT.TB_01都很大，兩個表關(guān)聯(lián)查詢的成本會很高，影響性能。

方法2：使用HINT:IGNORE_ROW_ON_DUPKEY_INDEX

不使用HINT:

INSERT

INTO SCOTT.TB_01

SELECT * FROM SCOTT.TB_02 ;

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.PK_TB_01) violated

從上面可以看出直接將表SCOTT.TB_02插入到表SCOTT.TB_01會出現(xiàn)主鍵沖突，報ORA-00001錯誤。

使用HINT:

INSERT /*+IGNORE_ROW_ON_DUPKEY_INDEX(a PK_tb_01)*/

INTO SCOTT.TB_01 A

SELECT * FROM SCOTT.TB_02 ;

100000 rows created.

從上面可以看出，加上HINT后，表SCOTT.TB_02中和表SCOTT.TB_01沒有主鍵沖突的記錄插入到表SCOTT.TB_01中，沖突的100條記錄沒有插入，也沒有報錯。

方法3：使用impdp的skip_constraint_errors選項

創(chuàng)建dump目錄

create directory dump_home as '/home/oracle';

導(dǎo)出表scott.tb_02

expdp system tables=scott.tb_02  directory=dump_home dumpfile=expdp_tb_02.dmp logfile=expdp_tb_02.log

使用expdp導(dǎo)出表scott.tb_02，用于后續(xù)導(dǎo)入到表scott.tb_01中。

導(dǎo)入表scott.tb_02到scott.tb_01，不加skip_constraint_errors選項

impdp system tables=scott.tb_02 remap_table=scott.tb_02:tb_01  content=data_only directory=dump_home dumpfile=expdp_tb_02.dmp logfile=impdp_tb_02.log

Import: Release 11.2.0.4.0 - Production on Tue Dec 5 23:18:49 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** tables=scott.tb_02 remap_table=scott.tb_02:tb_01 content=data_only directory=dump_home dumpfile=expdp_tb_02.dmp logfile=impdp_tb_02.log

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

ORA-31693: Table data object "SCOTT"."TB_01" failed to load/unload and is being skipped due to error:

ORA-00001: unique constraint (SCOTT.PK_TB_01) violated

Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Tue Dec 5 23:19:02 2017 elapsed 0 00:00:09

從上面可以看出，如果不加skip_constraint_errors選項就會報主鍵沖突錯誤，導(dǎo)入失敗。

導(dǎo)入表scott.tb_02到scott.tb_01，加skip_constraint_errors選項

impdp system tables=scott.tb_02 remap_table=scott.tb_02:tb_01  content=data_only directory=dump_home dumpfile=expdp_tb_02.dmp logfile=impdp_tb_02.log data_options=skip_constraint_errors

Import: Release 11.2.0.4.0 - Production on Tue Dec 5 23:21:29 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** tables=scott.tb_02 remap_table=scott.tb_02:tb_01 content=data_only directory=dump_home dumpfile=expdp_tb_02.dmp logfile=impdp_tb_02.log data_options=skip_constraint_errors

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."TB_01"                             1.796 MB 100000 out of 100100 rows

100 row(s) were rejected with the following error:

ORA-00001: unique constraint (SCOTT.PK_TB_01) violated

Rejected rows with the primary keys are:

Rejected row #1:

column C1: 1

…

Rejected row #99:

column C1: 99

Rejected row #100:

column C1: 100

Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Dec 5 23:21:56 2017 elapsed 0 00:00:23

從上面可以看出，導(dǎo)入順利完成，并跳過了主鍵沖突的行，并在impdp的log中顯示行的信息。

看完了這篇文章，相信你對“數(shù)據(jù)庫中批量插入數(shù)據(jù)時主鍵沖突怎么辦”有了一定的了解，如果想了解更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！