how to split one row to many based on a comma separate column

here is my data in mysql table:

name    childid
city    11,12,13
maintain    21,22,29
pool    33,39,100

I want to get data as the below format. So I can use it to join another tables.

11    city
12    city
13    city
21    maintain
22    maintain
29    maintain
33    pool
39    pool
100   pool

Answer

This is a very bad format, as no doubt comments and other answers will tell you. You want a junction table — and this is perhaps why you want this query.

The simplest way is if you have a table of all valid child ids. Then you can do:

select c.id, d.name
from mydata d join
     children c
     on find_in_set(c.id, childid) > 0;