As you know, in MySQL/MariaDB there is no native function for splitting string column by comma or by some regex into a table. In PostgreSQL we a great function regexp_split_to_table for this, but in mysql compatible world it was not possible (till some time ago) to do this without complicated workarounds. Fortunately with introduction of RECURSIVE CTEs support in MySQL 8 and MariaDB 10.2, there is a native a relatively easy way to do this.
Here is sample code that will parse out numbers using regex out of given string column (i.e. “8001,8002 8003, 8004”) and generate a row with incrementing port_id for each of these numbers (1 => 8001, 2 => 8002, 3 => 8003, 4 => 8004).
CREATE OR REPLACE VIEW service_port AS WITH RECURSIVE ha_port AS ( SELECT 1 AS port_id, -- get first match as our PORT regexp_substr(src.haproxy_ports, '[[:digit:]]+') AS client_port, -- generate remainder for recursive subqueries substr(src.haproxy_ports, regexp_instr(src.haproxy_ports, '[[:digit:]]+', 1, 2)) AS client_ports FROM service ms WHERE regexp_substr(src.haproxy_ports, '[[:digit:]]+') IS NOT NULL UNION ALL SELECT port_id + 1 AS port_id, regexp_substr(haproxy_ports, '[[:digit:]]+') AS client_port, substr(haproxy_ports, regexp_instr(haproxy_ports, '[[:digit:]]+', 1, 2)) AS haproxy_ports FROM ha_port p WHERE length(haproxy_ports) > 0 ) SELECT DISTINCT -- XXX: DISTINCT here, is CTE optimization barrier, because mysql 8 dies without it, while merging tables on join * FROM ha_port;
Note that, this is a not tested sample query, it should only give you an idea how it works. I could not put the exact query I’m using here, because its a bit more complicated…
If you need help with this, or similar SQL related problems, don’t hesitate to contact me.