MySQL 5.7中实现管道分隔ID与关联表名称的匹配及格式转换

因为MySQL 5.7没有内置的字符串拆分函数（比如STRING_SPLIT），也不支持递归CTE，我们可以通过临时数字表+字符串截取的方式来实现需求，以下是完整的SQL方案：

SELECT 
    ow.ID,
    CONCAT(ow.ID, ' - ', GROUP_CONCAT(o.name ORDER BY o.ID SEPARATOR ', ')) AS Result
FROM 
    OperatorsWork ow
-- 生成临时数字表，覆盖IDOperators中最多的ID数量，可按需扩展
JOIN 
    (
        SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    ) nums ON nums.n <= LENGTH(ow.IDOperators) - LENGTH(REPLACE(ow.IDOperators, '|', '')) + 1
-- 拆分IDOperators中的单个ID，并关联Operators表获取名称
JOIN 
    Operators o ON o.ID = SUBSTRING_INDEX(SUBSTRING_INDEX(ow.IDOperators, '|', nums.n), '|', -1)
GROUP BY 
    ow.ID
ORDER BY 
    ow.ID;

代码解释： #

• 临时数字表：通过UNION ALL生成连续数字（这里是1-5），用来遍历IDOperators中的每个分隔项。如果你的数据中IDOperators的ID数量更多，只需在这个子查询中添加更多SELECT n即可。
• 计算ID数量：LENGTH(ow.IDOperators) - LENGTH(REPLACE(ow.IDOperators, '|', '')) + 1用于计算当前IDOperators字符串中包含多少个ID（比如1|2|3会得到3），确保我们只遍历实际存在的ID项。
• 拆分字符串：SUBSTRING_INDEX(SUBSTRING_INDEX(ow.IDOperators, '|', nums.n), '|', -1)是MySQL 5.7中常用的字符串拆分技巧，它会依次提取第n个|分隔的ID值。
• 聚合名称：通过GROUP_CONCAT按OperatorsWork的ID分组，将匹配到的名称拼接成逗号分隔的字符串，ORDER BY o.ID保证名称顺序和原ID顺序一致。

预期输出： #

运行上述SQL后，会得到你需要的结果：

• 1 - Name1, Name2, Name3
• 2 - Name2, Name3
• 3 - Name1, Name3

额外说明： #

如果IDOperators中存在空值或多余的分隔符（比如1||3），可以添加过滤条件避免匹配到无效ID：

AND TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(ow.IDOperators, '|', nums.n), '|', -1)) != ''

内容的提问来源于stack exchange，提问作者g-pane