如何在MySQL中将逗号分隔值拆分至新表并保留键值关系

1. 先创建目标表Table2 #

先建好存储拆分后数据的表，结构与需求一致：

CREATE TABLE Table2 (
    `key` VARCHAR(50),
    `value` VARCHAR(50)
);

2. 方法一：MySQL 8.0+ 用递归CTE（推荐） #

如果你的MySQL版本是8.0及以上，递归CTE是最简洁的实现方式：

WITH RECURSIVE split_data AS (
    -- 初始化：提取每个key的第一个值和剩余未拆分的字符串
    SELECT
        `key`,
        SUBSTRING_INDEX(`values`, ',', 1) AS `value`,
        SUBSTRING(`values`, LOCATE(',', `values`) + 1) AS remaining_values
    FROM Table1
    WHERE `values` IS NOT NULL AND `values` != ''
    UNION ALL
    -- 递归处理剩余字符串，直到没有内容可拆分
    SELECT
        `key`,
        SUBSTRING_INDEX(remaining_values, ',', 1) AS `value`,
        SUBSTRING(remaining_values, LOCATE(',', remaining_values) + 1) AS remaining_values
    FROM split_data
    WHERE remaining_values IS NOT NULL AND remaining_values != ''
)
-- 将拆分结果批量插入Table2
INSERT INTO Table2 (`key`, `value`)
SELECT `key`, `value` FROM split_data;

3. 方法二：兼容MySQL 5.x 版本（用数字辅助表） #

如果使用不支持CTE的老版本MySQL，需要借助数字表实现拆分：

第一步：创建临时数字表 #

生成足够多的数字（数量要大于你的values列中最多的元素个数）：

CREATE TEMPORARY TABLE numbers (n INT);
-- 这里插入1到100的数字，可按需调整数量
INSERT INTO numbers VALUES 
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),
(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),
(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),
(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),
(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),
(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),
(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),
(91),(92),(93),(94),(95),(96),(97),(98),(99),(100);

第二步：拆分并插入数据 #

INSERT INTO Table2 (`key`, `value`)
SELECT
    t1.`key`,
    SUBSTRING_INDEX(SUBSTRING_INDEX(t1.`values`, ',', n.n), ',', -1) AS `value`
FROM Table1 t1
JOIN numbers n 
  ON n.n <= LENGTH(t1.`values`) - LENGTH(REPLACE(t1.`values`, ',', '')) + 1
WHERE t1.`values` IS NOT NULL AND t1.`values` != '';

原理：通过计算逗号数量得到每个values的元素个数，再用数字表匹配每个元素的位置，提取对应值。

4. 验证结果 #

执行查询确认拆分是否正确：

SELECT * FROM Table2;

内容的提问来源于stack exchange，提问作者Dusty