如何用MySQL实现多食材ID匹配的精准菜谱查询？

针对你开发菜谱搜索功能时的精准匹配需求（返回同时包含所有指定食材的菜谱），我给你分享两种高效的实现方案，都是单条SQL就能完成，不需要拆分多查询：

方案一：分组统计 + HAVING 子句（推荐，适配任意数量食材） #

思路 #

1. 先从recipeIngredientList表中筛选出所有包含目标ingredientID的记录
2. 按recipeID分组，统计每个菜谱匹配到的食材数量
3. 只保留统计数量等于目标食材总数的菜谱（说明该菜谱同时包含所有指定食材）
4. 最后关联recipes表获取菜谱名称

示例SQL（针对食材ID=1和2的场景） #

SELECT r.recipeID, r.recipeName
FROM recipeIngredientList ril
JOIN recipes r ON ril.recipeID = r.recipeID
WHERE ril.ingredientID IN (1, 2)
GROUP BY ril.recipeID, r.recipeName
HAVING COUNT(DISTINCT ril.ingredientID) = 2;

注：这里用COUNT(DISTINCT)是为了避免同一个菜谱重复关联同一个食材的情况（比如某些菜谱可能多次添加同一种食材），如果你的业务里不会出现这种情况，也可以直接用COUNT(*)。

如果后续用户选择更多食材，只需要修改IN里的ID列表，以及HAVING后的数字即可，灵活性拉满。

方案二：自连接（适合固定数量食材的场景） #

思路 #

针对每个指定的食材，对recipeIngredientList表做一次自连接，确保每个连接都能找到对应食材的记录，这样最终筛选出的菜谱必然同时包含所有食材。

示例SQL（针对食材ID=1和2的场景） #

SELECT r.recipeID, r.recipeName
FROM recipeIngredientList ril1
JOIN recipeIngredientList ril2 ON ril1.recipeID = ril2.recipeID
JOIN recipes r ON ril1.recipeID = r.recipeID
WHERE ril1.ingredientID = 1 
  AND ril2.ingredientID = 2;

这种写法逻辑直观，适合食材数量固定的场景，但如果用户可能选择3种、4种甚至更多食材，需要不断增加自连接的表，灵活性不如第一种方案。

性能优化建议 #

不管用哪种方案，都建议给recipeIngredientList表的recipeID和ingredientID字段建立联合索引：

CREATE INDEX idx_recipe_ingredient ON recipeIngredientList(recipeID, ingredientID);

这样可以大幅提升查询效率，尤其是当数据量较大时。

两种方案执行后，都会返回符合需求的两条记录：

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