MySQL将逗号分隔的字符串拆分为临时表

CREATE TABLE my_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    csv_values VARCHAR(255)
);

INSERT INTO my_table (csv_values) VALUES ('apple,banana,cherry'), ('orange,grape');

-- 创建一个临时表来存储拆分后的值
CREATE TEMPORARY TABLE IF NOT EXISTS temp_split_values (
    value VARCHAR(255)
);

-- 清空临时表以确保它是空的
TRUNCATE TABLE temp_split_values;

-- 使用UNION ALL结合子查询将逗号分隔的字符串拆分为临时表
INSERT INTO temp_split_values (value)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(csv_values, ',', n.n), ',', -1) AS value
FROM my_table, 
(SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) AS n
WHERE n.n <= 1 + (LENGTH(csv_values) - LENGTH(REPLACE(csv_values, ',', '')))
ORDER BY id, n.n;

-- 查询临时表以查看拆分结果
SELECT * FROM temp_split_values;