MySQL多ID列关联查询：如何显示对应工厂名称？

问题场景 #

现有user表和factory表，user表的factory_ids字段存储逗号分隔的多个工厂ID，需要查询所有用户数据，并将对应的工厂ID替换为工厂名称，以逗号分隔形式展示。

表结构 #

user表 #

factory表 #

解决方案 #

不同数据库的字符串处理函数不同，以下是主流数据库的实现方式：

1. MySQL（5.7及以上版本） #

SELECT
    u.user_id,
    u.fname AS user_name,
    GROUP_CONCAT(DISTINCT f.fname ORDER BY f.fname SEPARATOR ',') AS factory_names
FROM
    user u
LEFT JOIN factory f
    ON FIND_IN_SET(f.factory_id, u.factory_ids) > 0
GROUP BY
    u.user_id, u.fname;

• FIND_IN_SET用于判断工厂ID是否存在于逗号分隔的字符串中
• GROUP_CONCAT将匹配到的工厂名称合并为逗号分隔字符串，DISTINCT处理重复ID的情况

2. PostgreSQL #

SELECT
    u.user_id,
    u.fname AS user_name,
    STRING_AGG(DISTINCT f.fname, ',' ORDER BY f.fname) AS factory_names
FROM
    "user" u
LEFT JOIN factory f
    ON f.factory_id = ANY(STRING_TO_ARRAY(u.factory_ids, ',')::INT[])
GROUP BY
    u.user_id, u.fname;

• STRING_TO_ARRAY把逗号分隔的ID转为数组，再转换为整数类型
• ANY判断工厂ID是否属于该数组
• STRING_AGG实现字符串聚合并去重排序

3. SQL Server（2017及以上版本） #

SELECT
    u.user_id,
    u.fname AS user_name,
    STRING_AGG(DISTINCT f.fname, ',') WITHIN GROUP (ORDER BY f.fname) AS factory_names
FROM
    [user] u
LEFT JOIN factory f
    ON ',' + u.factory_ids + ',' LIKE '%,' + CAST(f.factory_id AS VARCHAR) + ',%'
GROUP BY
    u.user_id, u.fname;

• 通过拼接首尾逗号，避免出现部分匹配的错误（比如ID 12被误匹配为ID 1）
• STRING_AGG用于聚合字符串并排序

注意事项 #

• 逗号分隔存储多值的设计不符合数据库范式，长期来看建议优化为中间表（如user_factory，存储user_id与factory_id的一一关联关系），这样查询效率更高，也更便于维护
• 数据量较大时，FIND_IN_SET或LIKE的查询方式性能会明显下降，范式化是更优选择

内容的提问来源于stack exchange，提问作者Ratu Batu Hamlo Koto