MySQL使用FIND_IN_SET关联两表查询数据缺失问题排查

原查询错误原因 #

• 字段名错误：Normal表的项目字段是project不是project_id
• 语法错误：JOIN的ON条件中缺少AND连接两个匹配规则，同时存在别名混用问题（声明了Normal别名为n，但后续又直接使用Normal表名）
• 多余无效过滤条件：Normal is NULL会直接过滤掉匹配到Normal表id=2（role_id=1、project=80）的记录，这是你查询结果缺失value_col=60的核心原因

正确查询语句 #

你可以选择以下任意一种方式实现需求：

方式1：INNER JOIN实现 #

SELECT DISTINCT r.value_col
FROM Roles r
INNER JOIN Normal n 
  ON r.project = n.project 
  AND FIND_IN_SET(r.id, n.role_id) > 0
WHERE r.project = 80;

方式2：EXISTS子查询实现（性能更优，避免生成重复数据） #

SELECT r.value_col
FROM Roles r
WHERE r.project = 80
AND EXISTS (
  SELECT 1 FROM Normal n
  WHERE n.project = 80
  AND FIND_IN_SET(r.id, n.role_id) > 0
);

执行逻辑说明 #

• 首先过滤出Roles表中project=80的所有记录，对应id为1、2、3
• 逐个匹配Normal表中project=80的记录：
  • Roles.id=1匹配到Normal表id=2的role_id=1，返回value_col=60
  • Roles.id=2匹配到Normal表id=3的role_id='2,3'，返回value_col=40
  • Roles.id=3匹配到Normal表id=3的role_id='2,3'，返回value_col=100
• 最终返回结果完全符合预期的60、40、100

内容的提问来源于stack exchange，提问作者B L Praveen