MySQL中关联含多ID数组的字段获取对应详情的方法

原SQL的问题点 #

• 字段名拼写错误：req_id、req_name应为reg_id、reg_name
• 关联逻辑错误：直接用=无法匹配逗号分隔的多值字段，需要判断单个id是否存在于分隔字符串中
• 缺少分组操作：使用GROUP_CONCAT必须对主表的唯一标识字段分组
• 关联类型错误：JOIN会过滤掉reg_colour为空的记录，应改用LEFT JOIN保留所有主表数据

正确SQL语句 #

SELECT 
    t1.reg_id,
    t1.reg_name,
    GROUP_CONCAT(t2.colour SEPARATOR ',') AS name_colour
FROM table_1 t1
LEFT JOIN table_2 t2 
    ON FIND_IN_SET(t2.id_colour, t1.reg_colour) > 0
GROUP BY t1.reg_id, t1.reg_name;

代码说明 #

• LEFT JOIN：确保reg_colour为空的记录（比如Jane的条目）也能保留在结果中
• FIND_IN_SET(t2.id_colour, t1.reg_colour)：判断id_colour是否存在于reg_colour的逗号分隔列表中，返回值>0表示匹配成功
• GROUP BY t1.reg_id, t1.reg_name：按用户的唯一标识分组，保证每个用户只返回一条合并后的结果
• GROUP_CONCAT(t2.colour SEPARATOR ',')：将匹配到的颜色名称用逗号连接，符合预期结果的格式

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