Schreiben Sie den Titel um: So konvertieren Sie WITH `cte` AS (VALUES ROW(1), ROW(2), ROW(n)) in MySQL-Syntax in jOOQ DSL
Ich habe eine SQL-Abfrage für MySQL:
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`;
Nach der Umstellung auf 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);
Das Ergebnis ist:
+------------+----------------+ | 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)
Aber die Erklärung zeigt eine überflüssige 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
Ich habe eine SQL mit einer anderen MySQL-Syntax umgeschrieben – 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`;
Gleiches Ergebnis:
+------------+----------------+ | 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)
Aber der Abfrageplaner verwendet UNION nicht:
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
Frage: Wie konvertiert man das endgültige Skript in jOOQ DSL? Ich kann keine Möglichkeit finden, es zu tun
WITH `cte` AS (VALUES ROW(1), ROW(2), ROW(n)) ... ?
