How Can I Efficiently Split Comma-Separated Lists into Multiple Rows in MySQL?

MySQL: Split Comma-Separated List into Multiple Rows

Unnormalized tables present a challenge when dealing with foreign keys that are stored as comma-separated lists. MySQL doesn't provide a direct solution to this problem using a function that returns a table. However, a workaround can be achieved through a combination of techniques.

Using a Join Operation:

To join on a comma-separated list, use the FIND_IN_SET() function to determine if one value is present in the other. This allows you to create a join between your tables based on specific elements within the lists. For example:

SELECT part_id,
       vechicle_id
FROM vehicles
JOIN load_plan_configs ON FIND_IN_SET(vechicle_id, load_plan_configs.vehicle_ids)
WHERE load_plan_configs.id = 42;

Creating a New Table with INSERT:

Another approach is to create a new table that will store the split values. You can use a query that iterates through the comma-separated values and inserts them into the new table. For instance:

INSERT INTO new_table (part_id, material_id)
SELECT part_id,
       SUBSTRING(material, INSTR(material, ',') + 1) AS material_id
FROM table_with_comma_separated_values;

This query will create a new table with the split values where each part_id has a separate entry for each material_id in the comma-separated list.

By utilizing these techniques, you can effectively split comma-separated lists into multiple rows in MySQL, allowing you to work with the data in a more structured and accessible manner.

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