How to Split Comma-Separated Lists into Multiple Rows in MySQL?

Splitting Comma-Separated Lists into Multiple Rows in MySQL

Problem:

A table contains a column with comma-separated values that serve as foreign keys to another table. How can this data be processed, either through a join or a query, to create multiple entries in a new table, effectively "un-normalizing" the data?

Solution:

MySQL offers a solution for splitting these comma-separated lists using the FIND_IN_SET() function.

SELECT material_id
FROM materials
WHERE FIND_IN_SET(name, part.material) > 0;

This query retrieves the material_id for each name in the materials table that is present in the comma-separated list in the part table's material column. The result is a flattened table with pairs of part_id and material_id, as desired.

Example:

Consider the following data:

+---------+-------------+
| part_id | material    |
+---------+-------------+
|     339 | 1.2mm;1.6mm |
|     970 | 1.6mm       |
+---------+-------------+

The following query would split the material column into separate rows:

SELECT part_id, material_id
FROM materials
WHERE FIND_IN_SET(name, part.material) > 0;

The result would be:

+---------+-------------+
| part_id | material_id |
+---------+-------------+
|     339 |           1 |
|     339 |           2 |
|     970 |           2 |
+---------+-------------+

The above is the detailed content of How to Split Comma-Separated Lists into Multiple Rows in MySQL?. For more information, please follow other related articles on the PHP Chinese website!