MySQL中实现逗号分隔列与关联表的映射转换方法

表A中有一列存储逗号分隔的数字值，示例数据：

• "1,2,3"
• "1,2"
• "2,1"
• "3,2"

表B存储数字与单词的映射关系，示例记录：

期望将表A的数字串转换为对应的单词串，结果如下：

• "one,two,three"
• "one,two"
• "two,one"
• "three,two"

尝试过以下SQL，但未得到正确结果：

select 
    A.comma_separated_numbers,
    group_concat(distinct B.word separator ',') as comma_separated_words
from A inner join B 
on find_in_set(B.number, A.comma_separated_numbers)>0 
;

你的SQL存在三个核心问题：

1. 未对表A的每一行进行分组，会导致所有行的结果被合并成一条；
2. 使用DISTINCT会过滤掉同一数字对应的多个单词（比如表B中数字1对应one和three，DISTINCT会只保留其中一个）；
3. 默认GROUP_CONCAT的结果顺序不固定，无法匹配原数字串的顺序。

针对这些问题，我们可以通过以下方式修正：

完整SQL（MySQL 8.0+ 版本） #

借助递归CTE拆分数字串并保留原顺序，再关联表B拼接单词：

WITH split_A AS (
    SELECT 
        comma_separated_numbers,
        SUBSTRING_INDEX(SUBSTRING_INDEX(comma_separated_numbers, ',', n), ',', -1) AS number,
        n AS position
    FROM A
    JOIN (
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 
        -- 若表A存在更长的数字串，需增加更多SELECT语句，比如SELECT 4 UNION ALL SELECT 5等
    ) numbers
    ON n <= LENGTH(comma_separated_numbers) - LENGTH(REPLACE(comma_separated_numbers, ',', '')) + 1
)
SELECT 
    s.comma_separated_numbers,
    GROUP_CONCAT(b.word ORDER BY s.position SEPARATOR ',') AS comma_separated_words
FROM split_A s
JOIN B b ON s.number = b.number
GROUP BY s.comma_separated_numbers;

兼容MySQL 5.x 版本的写法 #

如果使用不支持CTE的旧版本MySQL，可直接用数字辅助表实现：

SELECT 
    A.comma_separated_numbers,
    GROUP_CONCAT(B.word ORDER BY numbers.n SEPARATOR ',') AS comma_separated_words
FROM A
JOIN (
    SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3
) numbers ON numbers.n <= LENGTH(A.comma_separated_numbers) - LENGTH(REPLACE(A.comma_separated_numbers, ',', '')) + 1
JOIN B ON B.number = SUBSTRING_INDEX(SUBSTRING_INDEX(A.comma_separated_numbers, ',', numbers.n), ',', -1)
GROUP BY A.comma_separated_numbers;

关键说明 #

• 数字辅助表用来拆分逗号分隔串，同时记录每个数字在原串中的位置，保证最终单词顺序和原数字串一致；
• 移除DISTINCT，保留同一数字对应的所有映射单词；
• 通过GROUP BY确保表A的每一行对应一个独立结果；
• GROUP_CONCAT中添加ORDER BY子句，强制按原数字串的元素顺序拼接单词。

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