数据库中批量插入数据时主键冲突怎么办

发布时间:2021-11-11 13:35:44 作者:小新
来源:亿速云 阅读:1359

小编给大家分享一下数据库中批量插入数据时主键冲突怎么办,希望大家阅读完这篇文章之后都有所收获,下面让我们一起去探讨吧!

客户有这样一个需求:有一个大表A,有主键,经常需要往这个表中批量插入大量数据,但插入的数据可能自身重复或者跟表A重复。

下面测试几种插入的方法:

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条数据做为重复数据

insert into scott.tb_02

SELECT c1,c2, c3

FROM scott.tb_01

where rownum <= 100;

commit;

现在需要将表scott.tb_02中和表scott.tb_01非重复的数据插入到scott.tb_01

方法1:关联插入

INSERT INTO SCOTT.TB_01

SELECT A.* --这里如果表SCOTT.TB_02自身有重复数据,还要加上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都很大,两个表关联查询的成本会很高,影响性能。

方法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会出现主键冲突,报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选项

创建dump目录

create directory dump_home as '/home/oracle';

导出表scott.tb_02

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

使用expdp导出表scott.tb_02,用于后续导入到表scott.tb_01中。

导入表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选项就会报主键冲突错误,导入失败。

导入表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

从上面可以看出,导入顺利完成,并跳过了主键冲突的行,并在impdp的log中显示行的信息。

看完了这篇文章,相信你对“数据库中批量插入数据时主键冲突怎么办”有了一定的了解,如果想了解更多相关知识,欢迎关注亿速云行业资讯频道,感谢各位的阅读!

推荐阅读:
  1. 主主复制时的主键冲突解决
  2. 使用Mybatis怎么批量插入数据并返回主键

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

数据库

上一篇:oracle中禁用sysdba远程登录遇见的问题有哪些

下一篇:Django中的unittest应用是什么

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》