MySQL多表关联使用IN匹配逗号分隔字段无法返回全部结果怎么办

问题根因 #

你当前的实现存在两个核心问题：

• courses表的inst_id字段存储的是逗号拼接的字符串，直接用IN匹配数字类型的institutes.id时，只会把整个字符串当成单个值处理，仅能匹配到第一个机构，无法返回全部匹配的机构列表。
• 你写的SQL中institutes表没有指定JOIN关联条件，直接在WHERE子句过滤，相当于先做笛卡尔积再过滤，执行效率低且逻辑不严谨。

临时解决方案（无需修改表结构） #

使用MySQL内置的FIND_IN_SET函数做关联匹配，搭配GROUP_CONCAT拼接多个机构名称，修改后的代码如下：

$stmt = $db->prepare("SELECT courses.*, teachers.name, GROUP_CONCAT(i.name SEPARATOR '、') as institute 
FROM courses 
JOIN teachers ON teachers.id = courses.teacher_id 
JOIN institutes i ON FIND_IN_SET(i.id, courses.inst_id) > 0
WHERE courses.id = :id
GROUP BY courses.id");
$stmt->execute([':id' => $id]);
return $stmt->fetch(PDO::FETCH_ASSOC);

代码说明 #

• FIND_IN_SET(i.id, courses.inst_id) 会判断机构ID是否存在于逗号分隔的inst_id字符串中，可以匹配到所有符合条件的机构记录
• GROUP_CONCAT(i.name SEPARATOR '、') 会把匹配到的多个机构名称用顿号拼接为单个字符串返回，查询courses.id=1时institute字段的值为A、B，符合你的预期
• 你之前代码里提前提取$match的步骤可以省略，直接通过表字段关联即可
• 如果你的MySQL服务开启了ONLY_FULL_GROUP_BY模式，可将SELECT子句中除聚合函数外的所有字段都添加到GROUP BY后，或用ANY_VALUE()包裹非分组字段即可正常执行。

长期优化建议 #

关系型数据库中使用逗号分隔字符串存储关联ID属于不良设计，会导致索引失效、查询性能低、后续维护成本高等问题。建议新增课程机构中间关联表course_institute_rel，结构参考：
| course_id | institute_id |
| --------- | -------------- |
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |

后续直接通过中间表关联查询，性能和可维护性都会大幅提升。

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