How to Split Comma-Separated Lists in MySQL for Data Normalization?

Splitting Comma-Separated Lists in MySQL

Working with unnormalized tables containing comma-separated foreign key values can be challenging when migrating data to systems that lack procedural languages. To address this issue, MySQL provides a method to convert these lists into normalized rows.

Using the FIND_IN_SET() Function

To split a comma-separated list into multiple rows, MySQL offers the FIND_IN_SET() function. This function returns the position of a specific value within the list. Using this function, you can iterate through each value in the list and insert a new row into a temporary table.

The following query demonstrates how to split the "material" column from the given table into normalized rows:

CREATE TEMPORARY TABLE split_materials (
  part_id INT,
  material_id INT
);

INSERT INTO split_materials (part_id, material_id)
SELECT
  part_id,
  CAST(SUBSTRING_INDEX(material, ';', 1) AS UNSIGNED)
FROM
  my_table;

INSERT INTO split_materials (part_id, material_id)
SELECT
  part_id,
  CAST(SUBSTRING_INDEX(material, ';', -1) AS UNSIGNED)
FROM
  my_table
WHERE
  material LIKE '%,%';

Alternative Solutions

If the FIND_IN_SET() function is not appropriate, you can also explore using:

• Regular expressions to extract the values from the list.
• JOIN operations to correlate the comma-separated list to the foreign key table.

Conclusion

Splitting comma-separated lists into multiple rows in MySQL can be achieved using the FIND_IN_SET() function, regular expressions, or JOINs. This allows you to normalize unnormalized data and prepare it for ingestion into systems that do not support procedural languages.

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