How to Split Comma-Separated Lists in MySQL Efficiently?

Splitting Comma-Separated Lists in MySQL

To handle unnormalized data with comma-separated lists, there are two approaches: joining or inserting into a new table.

Join Approach:

1. Create a temporary table containing the unique values from the comma-separated list:

CREATE TEMP TABLE UNIQUE_MATERIALS AS
SELECT DISTINCT TRIM(MATERIAL) AS MATERIAL FROM PART_TABLE;

2. Join the original table with the temporary table on the MATERIAL column to expand the list into rows:

SELECT p.part_id, m.material_id
FROM PART_TABLE p
JOIN UNIQUE_MATERIALS m ON p.MATERIAL LIKE CONCAT('%', m.MATERIAL, '%');

Insert Approach:

1. Use the REPLACE command to insert new rows into a new table (SPLIT_MATERIALS):

CREATE TABLE SPLIT_MATERIALS (part_id INT, material_id INT);
INSERT INTO SPLIT_MATERIALS (part_id, material_id)
VALUES
  (339, 1),
  (339, 2),
  (970, 2);

2. Populate the new table by iterating over each comma-separated value and inserting a row:

INSERT INTO SPLIT_MATERIALS (part_id, material_id)
SELECT part_id, STR_TO_INT(TRIM(SUBSTR(MATERIAL, pos - 1, INSTR(MATERIAL, ',', pos) - pos + 1)))
FROM PART_TABLE,
  (SELECT @pos := 1) AS vars,
  (SELECT INSTR(MATERIAL, ',')) AS tmp
WHERE STR_TO_INT(SUBSTR(MATERIAL, @pos - 1, INSTR(MATERIAL, ',', @pos) - @pos + 1)) IS NOT NULL
  AND INSTR(MATERIAL, ',', @pos) > 0
GROUP BY part_id, STR_TO_INT(SUBSTR(MATERIAL, @pos - 1, INSTR(MATERIAL, ',', @pos) - @pos + 1))
ORDER BY part_id, SUBSTR(MATERIAL, @pos - 1, INSTR(MATERIAL, ',', @pos) - @pos + 1);

Once the new table is populated, it can be joined with the original table for queries that require the separated values.

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