mysql 号分割 拆分多行,MySQL：将逗号分隔的列表分成多行

I have an unnormalized table with a column containing a comma separated list that is a foreign key to another table:

+----------+-------------+ +--------------+-------+

| part_id | material | | material_id | name |

+----------+-------------+ +--------------+-------+

| 339 | 1.2mm;1.6mm | | 1 | 1.2mm |

| 970 | 1.6mm | | 2 | 1.6mm |

+----------+-------------+ +--------------+-------+

I want to read this data into a search engine that offers no procedural language.

So is there a way to either make a join on this column or run a query on this data that inserts appropriate entries into a new table?

The resulting data should look like this:

+---------+-------------+

| part_id | material_id |

+---------+-------------+

| 339 | 1 |

| 339 | 2 |

| 970 | 2 |

+---------+-------------+

I could think of a solution if the DBMS supported functions returning a table but MySQL apparently doesn't.

解决方案

In MySQL this can be achieved as below

SELECT id, length FROM vehicles WHERE id IN ( 117, 148, 126)

+---------------+

| id | length |

+---------------+

| 117 | 25 |

| 126 | 8 |

| 148 | 10 |

+---------------+

SELECT id,vehicle_ids FROM load_plan_configs WHERE load_plan_configs.id =42

+---------------------+

| id | vehicle_ids |

+---------------------+

| 42 | 117, 148, 126 |

+---------------------+

Now to get the length of comma separated vehicle_ids use below query

Output

SELECT length

FROM vehicles, load_plan_configs

WHERE load_plan_configs.id = 42 AND FIND_IN_SET(

vehicles.id, load_plan_configs.vehicle_ids

)

+---------+

| length |

+---------+

| 25 |

| 8 |

| 10 |

+---------+