如何批量插入数据并忽略外键（FK）约束错误？

问题背景 #

现有Parent表（含主键约束）和Child表（可能无主键，但存在指向Parent表的外键约束）。需要批量向Child表插入数据，要求跳过所有触发外键约束错误的行（类似单行insertInto使用onConflictOnConstraint()的效果），正常保留并插入其他行。

尝试使用jOOQ的dslContext.loadInto()搭配onErrorIgnore()选项，但只要有一行触发约束错误，整批数据都无法插入，代码示例如下：

List<Record2<Object, Object>> records = List.of(
        dslContext.newRecord(field("id"), field("value")).values(1, "1"),
        dslContext.newRecord(field("id"), field("value")).values(2, "2"),
        dslContext.newRecord(field("id"), field("value")).values(3, "3"),
        dslContext.newRecord(field("id"), field("value")).values(3, "3"));
dslContext.loadInto(table("child"))
        .batchAll()
        .onErrorIgnore()
        .loadRecords(records)
        .fields(field("id"), field("value"))
        .execute();

问题原因 #

batchAll()会将所有待插入记录打包成一个数据库批量请求发送，一旦某条记录触发外键约束错误，数据库会直接回滚整个批量操作。而onErrorIgnore()仅在jOOQ加载数据阶段生效（比如数据类型转换失败），无法处理数据库执行阶段的批量约束错误。

可行解决方案 #

方案1：前置过滤无效数据 #

在插入前先查询Parent表的所有有效主键，过滤掉Child记录中不存在于Parent的行，再执行批量插入：

// 先获取Parent表的所有有效主键
Set<Object> validParentIds = dslContext.select(field("id"))
        .from(table("parent"))
        .fetchSet(field("id"));

// 过滤待插入记录
List<Record2<Object, Object>> validRecords = records.stream()
        .filter(r -> validParentIds.contains(r.value1()))
        .collect(Collectors.toList());

// 执行批量插入
dslContext.loadInto(table("child"))
        .batchAll()
        .loadRecords(validRecords)
        .fields(field("id"), field("value"))
        .execute();

这种方式从根源上避免了外键错误，性能最优，但需注意Parent数据在过滤和插入之间是否有变更（如果有并发修改，可能仍存在极小概率的外键错误）。

方案2：利用数据库特性忽略单条错误 #

不同数据库有不同语法支持单条错误忽略：

• MySQL：使用INSERT IGNORE，jOOQ可通过useInsertIgnore()开启：

  dslContext.loadInto(table("child"))
          .useInsertIgnore()
          .loadRecords(records)
          .fields(field("id"), field("value"))
          .execute();
  

  注意：INSERT IGNORE会忽略所有类型的错误（包括主键重复、数据类型错误等），需根据业务场景谨慎使用。

• PostgreSQL：无法直接用INSERT IGNORE，可通过INSERT ... SELECT结合WHERE EXISTS过滤无效行：

  // 构造待插入数据的SELECT语句
  Select<?> select = select(val(1), val("1"))
          .unionAll(select(val(2), val("2")))
          .unionAll(select(val(3), val("3")))
          .unionAll(select(val(3), val("3")));
  
  // 插入时过滤掉Parent中不存在的id
  dslContext.insertInto(table("child"), field("id"), field("value"))
          .select(select.whereExists(
              selectOne().from(table("parent"))
                  .where(table("parent").field("id").equal(field("id")))
          ))
          .execute();
  

方案3：拆分批量为小批次（不推荐） #

放弃batchAll()，改用jOOQ默认的小批量插入（默认批量大小为1000），同时捕获批量执行中的错误。但这种方式仍可能存在部分批次失败的情况，需要额外处理重试逻辑，性能不如前两种方案。

内容的提问来源于stack exchange，提问作者mgramin