Description:
MySQL really needs a simple "EXPLODE(delimiter, string)" command to return rows of values to enable efficient use of "IN".
Functions can't use PREPARE, and FIND_IN_SET does not efficiently use indexed fields, so if you have lets say a comma list of values, the current ways to do it are:
set_ids = "1,2,3,4,5,6";
where FIND_IN_SET(`ID`, ids)
OR USING THE INDEXES MORE EFFICIENTLY USING IN:
set ids = "[1,2,3,4,5,6]";
...
where `ID` in (select `id` from JSON_TABLE(`ids`, "$[*]" COLUMNS(`id` BIGINT PATH "$"))`jt`)
The second option is clunky and it would be far better and easier to have something like:
set ids = "1,2,3,4,5,6";
...
where `ID` in (explode(",", ids));
Using the explode method enables the "IN" to be used, which is far more efficient than FIND_IN_SET or creating values from a JSON_TABLE.
How to repeat:
set ids = "1,2,3,4,5,6";
select ids;
-> "1,2,3,4,5,6"
select explode(",", ids);
-> 1
-> 2
-> 3
-> 4
-> 5
-> 6
Suggested fix:
Add an "EXPLODE" command to break a string list into values (rows).
Description: MySQL really needs a simple "EXPLODE(delimiter, string)" command to return rows of values to enable efficient use of "IN". Functions can't use PREPARE, and FIND_IN_SET does not efficiently use indexed fields, so if you have lets say a comma list of values, the current ways to do it are: set_ids = "1,2,3,4,5,6"; where FIND_IN_SET(`ID`, ids) OR USING THE INDEXES MORE EFFICIENTLY USING IN: set ids = "[1,2,3,4,5,6]"; ... where `ID` in (select `id` from JSON_TABLE(`ids`, "$[*]" COLUMNS(`id` BIGINT PATH "$"))`jt`) The second option is clunky and it would be far better and easier to have something like: set ids = "1,2,3,4,5,6"; ... where `ID` in (explode(",", ids)); Using the explode method enables the "IN" to be used, which is far more efficient than FIND_IN_SET or creating values from a JSON_TABLE. How to repeat: set ids = "1,2,3,4,5,6"; select ids; -> "1,2,3,4,5,6" select explode(",", ids); -> 1 -> 2 -> 3 -> 4 -> 5 -> 6 Suggested fix: Add an "EXPLODE" command to break a string list into values (rows).