將標題重寫為：將MySQL語法中的WITH `cte` AS (VALUES ROW(1), ROW(2), ROW(n))轉換為jOOQ DSL的方法

Question

我有一個用於MySQL的SQL查詢：

WITH `cte` AS (
    ( SELECT 1431655747 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 1431655733 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 715827794 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 715827865 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 1073741809 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 1073741759 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 715827800 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 1431655693 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 715827789 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 715827838 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 715827823 AS `n` FROM DUAL ) UNION ALL
( SELECT 858993391 AS `n` FROM DUAL )) SELECT
`cte`.`n`,
`maxmind_country`.`country`.`name_en` 
FROM
    `cte`
    JOIN `maxmind_country`.`ipv4` ON `cte`.`n` BETWEEN `maxmind_country`.`ipv4`.`start_int` 
    AND `maxmind_country`.`ipv4`.`last_int`
    JOIN `maxmind_country`.`country` ON `maxmind_country`.`country`.`geoname_id` = `maxmind_country`.`ipv4`.`v_geoname_id`;

轉換為jOOQ DSL後：

String ipAlias = "n";
SelectSelectStep<Record1<UInteger>> unionIps = ips
    .stream()
    .distinct()
    .map(value -> DSL.select(DSL.val(value).as(ipAlias)))
    .reduce((r1, r2) -> (SelectSelectStep<Record1<UInteger>>) r1.unionAll(r2))
    .orElse(null);

if(unionIps == null)
    return null;

CommonTableExpression<Record1<UInteger>> cte = DSL.name("cte").as(unionIps);
Field<UInteger> ipField = cte.field(ipAlias, UInteger.class);
return dslContext
    .with(cte)
    .select(ipField, COUNTRY.NAME_EN)
    .from(cte)
    .join(IPV4).on(ipField.between(IPV4.START_INT, IPV4.LAST_INT))
    .join(COUNTRY).on(COUNTRY.GEONAME_ID.eq(IPV4.V_GEONAME_ID))
    .fetchMap(ipField, Country.class);

結果為：

+------------+----------------+
| n          | name_en        |
+------------+----------------+
| 1431655747 | Spain          |
| 1431655733 | Spain          |
|  715827794 | China          |
|  715827865 | China          |
| 1073741809 | United States  |
| 1073741759 | United States  |
|  715827800 | China          |
| 1431655693 | Spain          |
|  715827789 | China          |
|  715827838 | China          |
|  715827823 | China          |
|  858993391 | United Kingdom |
+------------+----------------+
12 rows in set (0.16 sec)

但是解釋顯示有多餘的UNION：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1   PRIMARY <derived2>      ALL                 12  100,00  
1   PRIMARY ipv4        ALL ipv4_start_int_UNIQUE,ipv4_last_int_UNIQUE,ipv4_v_geoname_id_fk_idx             325934  11,11   Range checked for each record (index map: 0x7)
1   PRIMARY country     eq_ref  PRIMARY PRIMARY 4   maxmind_country.ipv4.v_geoname_id   1   100,00  
2   DERIVED                                     No tables used
3   UNION                                       No tables used
4   UNION                                       No tables used
5   UNION                                       No tables used
6   UNION                                       No tables used
7   UNION                                       No tables used
8   UNION                                       No tables used
9   UNION                                       No tables used
10  UNION                                       No tables used
11  UNION                                       No tables used
12  UNION                                       No tables used
13  UNION                                       No tables used

我用另一種MySQL語法重寫了一個SQL - VALUES ROW(1), ROW(2), ROW(n)：

WITH `cte` AS (
VALUES ROW(1431655747),
    ROW(1431655733),
    ROW(715827794),
    ROW(715827865),
    ROW(1073741809),
    ROW(1073741759),
    ROW(715827800),
    ROW(1431655693),
    ROW(715827789),
    ROW(715827838),
    ROW(715827823),
    ROW(858993391)) SELECT
`cte`.`column_0`,
`maxmind_country`.`country`.`name_en` 
FROM
    `cte`
    JOIN `maxmind_country`.`ipv4` ON `cte`.`column_0` BETWEEN `maxmind_country`.`ipv4`.`start_int` 
    AND `maxmind_country`.`ipv4`.`last_int`
    JOIN `maxmind_country`.`country` ON `maxmind_country`.`country`.`geoname_id` = `maxmind_country`.`ipv4`.`v_geoname_id`;

結果相同：

+------------+----------------+
| column_0   | name_en        |
+------------+----------------+
| 1431655747 | Spain          |
| 1431655733 | Spain          |
|  715827794 | China          |
|  715827865 | China          |
| 1073741809 | United States  |
| 1073741759 | United States  |
|  715827800 | China          |
| 1431655693 | Spain          |
|  715827789 | China          |
|  715827838 | China          |
|  715827823 | China          |
|  858993391 | United Kingdom |
+------------+----------------+
12 rows in set (0.16 sec)

但是查詢計劃器不使用UNION：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1   PRIMARY <derived2>      ALL                 12  100,00  
1   PRIMARY ipv4        ALL ipv4_start_int_UNIQUE,ipv4_last_int_UNIQUE,ipv4_v_geoname_id_fk_idx             325934  11,11   Range checked for each record (index map: 0x7)
1   PRIMARY country     eq_ref  PRIMARY PRIMARY 4   maxmind_country.ipv4.v_geoname_id   1   100,00  
2   DERIVED                                     No tables used

問題：如何將最後的腳本轉換為jOOQ DSL？我找不到任何方法來實現

WITH `cte` AS (VALUES ROW(1), ROW(2), ROW(n)) ... ?

Answer 1

截至jOOQ 3.15版本，還沒有辦法將標準SQL VALUES建構器表示為##org.jooq.Select##。它只能表示為org.jooq.Table，請參閱：https://github.com/jOOQ/jOOQ/issues/5871。

因此，您還不能使用jOOQ DSL來表示完全符合要求的語法，但是您可以在

FROM子句中使用VALUES：

selectFrom(values(...))

這應該會產生與您所期望的幾乎相同的計劃。另一種解決方法是使用

純SQL模板。