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

小编给大家分享一下数据库中批量插入数据时主键冲突怎么办，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！客户有这样一个需求：有一个大表A，有主键，经常需要往这个表中批量插入大量数据，但插入的数据可能自身重复或者跟表A重复。下面测试几种插入的方法：DB:ORACLE 11.2.0.4新建测试表：新建表scott.tb_01create table scott.tb_01asSELECT level c1,level c2,level c3FROM DUALCONNECT BY LEVEL
在表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_02create table scott.tb_02asSELECT level+100000 c1,level c2,level c3FROM DUALCONNECT BY LEVEL
在表scott.tb_02上插入表scott.tb_01的100条数据做为重复数据insert into scott.tb_02SELECT c1,c2, c3FROM scott.tb_01where rownum
commit;现在需要将表scott.tb_02中和表scott.tb_01非重复的数据插入到scott.tb_01方法1：关联插入INSERT INTO SCOTT.TB_01SELECT A.* –这里如果表SCOTT.TB_02自身有重复数据，还要加上distinctFROM SCOTT.TB_02 ALEFT JOIN SCOTT.TB_01 BON A.c1 = B.c2WHERE B.c1 IS NULL;100000 rows created.上面一种常见的插入方法，这个方法的的问题在于，如果表SCOTT.TB_02和表SCOTT.TB_01都很大，两个表关联查询的成本会很高，影响性能。方法2：使用HINT:IGNORE_ROW_ON_DUPKEY_INDEX不免费主机域名使用HINT:INSERTINTO SCOTT.TB_01SELECT * 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 ASELECT * FROM SCOTT.TB_02 ;100000 rows created.从上面可以看出，加上HINT后，表SCOTT.TB_02中和表SCOTT.TB_01没有主键冲突的记录插入到表SCOTT.TB_01中，冲突的100条记录没有插入，也没有报错。方法3：使用im免费主机域名pdp的skip_constraint_errors选项创建dump目录create directory dump_home as ‘/home/oracle’;导出表scott.tb_02expdp 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.logImport: Release 11.2.0.4.0 – Production on Tue Dec 5 23:18:49 2017Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloadedStarting “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.logProcessing object type TABLE_EXPORT/TABLE/TABLE_DATAORA-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) violatedJob “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_errorsImport: Release 11.2.0.4.0 – Production on Tue Dec 5 23:21:29 2017Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloadedStarting “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_errorsProcessing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported “SCOTT”.”TB_01″ 1.796 MB 100000 out of 100100 rows100 row(s) were rejected with the following error:ORA-00001: unique constraint (SCOTT.PK_TB_01) violatedRejected rows with the primary keys are:Rejected row #1:column C1: 1…Rejected row #99:column C1: 99Rejected row #100:column C1: 100Job “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully completed at Tue Dec 5 23:21:56 2017 elapsed 0 00:00:23从上面可以看出，导入顺利完成，并跳过了主键冲突的行，并在impdp的log中显示行的信息。看完了这篇文章，相信你对“数据库中批量插入数据时主键冲突怎么办”有了一定的了解，如果想了解更多相关知识，欢迎关注云技术行业资讯频道，感谢各位的阅读！

相关推荐: MySQL大表优化的方法教程

本篇内容介绍了“MySQL大表优化的方法教程”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！单表优化除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分…