Mysql split comma separated results into multiple rows

Question

I have a table where two columns are comma separated id's. For example

+--------+-------------+------------+
| name   | country_ids | region_ids |
+--------+-------------+------------+
| item 1 | 1,2,3       | 2,4        |
+--------+-------------+------------+
| item 2 | 2,3         | 1,4        |
+--------+-------------+------------+

I would like to run a mysql query so the comma separated values in both columns are split into multiple rows. So in the above example, the results would be

name     | country_id | region_id |
+--------+------------+-----------+
item 1   |  1         |  2        |
item 1   |  1         |  4        |
item 1   |  2         |  2        |
item 1   |  2         |  4        |
item 1   |  3         |  2        |
item 1   |  3         |  4        |
item 2   |  2         |  1        |
item 2   |  2         |  4        |
item 2   |  3         |  1        |
item 2   |  3         |  4        |

Is this possible in a mysql statement - even if I have to create a stored procedure on the fly?

THanks

That post answers the scenario where there is only one column with separated comma values. My question relates to two columns, which I am having trouble achieving — Typhoon101, Jan 10 '19 at 14:26
Just take an answer from the duplicate link, and split one column bringing alone the still CSV values from the other column. Then, split the second column. — Tim Biegeleisen, Jan 10 '19 at 14:28
Are you able to provide an example. I understand the logic, but as I said, i am having trouble physically achieving it — Typhoon101, Jan 10 '19 at 14:30
This is as close as I got before things started breaking. https://www.db-fiddle.com/f/kKT8uwPXZSHbLYUG4rskwv/0 — Typhoon101, Jan 10 '19 at 15:27
Here a [db-fiddle](https://www.db-fiddle.com/f/vJMerGTLX7qk4QysNkXhJN/0) with an option that can give you some ideas, adjust as you need. — wchiquito, Jan 10 '19 at 18:46

Mysql split comma separated results into multiple rows

0 Answers0