jOOQ使用update.set()通过子查询设置多列时为何嵌套多余select？如何解决？

• MySQL原生限制：MySQL默认不允许UPDATE语句的子查询直接引用被更新的同一张表，否则会抛出You can't specify target table 'xxx' for update in FROM clause错误。jOOQ针对MySQL方言会自动给同表引用的子查询加一层嵌套派生表，以此绕开该限制，这就是你看到额外嵌套select * from (...)的核心原因。
• 类型不匹配导致方法重载错误：你的dateAdd方法返回类型是Field<Date>，但a.START、a.END是LocalDateTime类型的字段，你强转为Row2后，子查询返回的字段类型和行表达式的字段类型不匹配，jOOQ没有匹配到正确的行值更新重载方法，也会导致生成的SQL不符合预期。

方案1：简化写法（无关联查询场景优先） #

如果你的更新逻辑不需要关联同表的其他行做计算，完全可以去掉子查询，直接给两个字段赋值，从根源上避免嵌套问题：

public static void moveEventStart(final LocalDate previousEventStart, final LocalDate newEventStart) {
    final Days changeInterval = Days.daysBetween(previousEventStart, newEventStart);
    ActivityTbl a = ACTIVITY.as("a");

    jooq()
        .update(a)
        .set(a.START, models.Jooq.dateAdd(a.START, changeInterval.getDays(), DatePart.DAY))
        .set(a.END, models.Jooq.dateAdd(a.END, changeInterval.getDays(), DatePart.DAY))
        .where(...)
        .execute();
}

方案2：修复类型匹配+用JOIN替代子查询（需要关联同表场景） #

如果确实需要关联同表的其他行，优先用UPDATE JOIN写法，性能更好且不会触发子查询嵌套：
首先修复dateAdd方法的返回类型，和表字段类型对齐：

// 把返回类型从Field<Date>改成Field<LocalDateTime>，SQLDataType也对应修改
public static Field<LocalDateTime> dateAdd(TableField<?, LocalDateTime> field, int interval, DatePart datePart) {
    String unit;
    switch (datePart) {
      case YEAR:
        unit = "YEAR";
        break;
      case MONTH:
        unit = "MONTH";
        break;
      case DAY:
        unit = "DAY";
        break;
      case HOUR:
        unit = "HOUR";
        break;
      case MINUTE:
        unit = "MINUTE";
        break;
      case SECOND:
        unit = "SECOND";
        break;
      default:
        unit = "";
    }
    return DSL.field(
        "DATE_ADD({0}, INTERVAL {1} " + unit + ")", SQLDataType.LOCALDATETIME, field, DSL.inline(interval));
  }

然后修改更新逻辑为JOIN写法：

public static void moveEventStart(final LocalDate previousEventStart, final LocalDate newEventStart) {
    final Days changeInterval = Days.daysBetween(previousEventStart, newEventStart);
    ActivityTbl a = ACTIVITY.as("a");
    ActivityTbl a2 = ACTIVITY.as("a2")

    jooq()
        .update(a)
        .join(a2).on(/* 你的a和a2的关联条件 */)
        .set(a.START, models.Jooq.dateAdd(a2.START, changeInterval.getDays(), DatePart.DAY))
        .set(a.END, models.Jooq.dateAdd(a2.END, changeInterval.getDays(), DatePart.DAY))
        .where(...)
        .execute();
}

方案3：保留行子查询写法 #

如果一定要用行子查询的写法，首先修复dateAdd的类型匹配问题，然后可以通过配置jOOQ的Settings关闭自动嵌套派生表的特性：

// 配置jOOQ时添加该设置，关闭同表更新的自动派生表嵌套
Settings settings = new Settings()
    .withRenderSelfJoinOnSubquerySelfReference(false);

调整配置后原有代码即可正常生成无额外嵌套的SQL。

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