如何查询可用指定全部配料制作的鸡尾酒（多表关联）

核心思路 #

要找到能用给定配料制作的鸡尾酒，本质是筛选出所有必需配料都包含在用户提供的配料ID数组中的鸡尾酒。下面提供两种可靠的SQL实现方式，以及对应的PHP处理代码。

方法一：分组统计匹配数 #

通过分组统计鸡尾酒的总配料数，以及其中在用户列表里的配料数，当两者相等时，说明该鸡尾酒的所有配料都能被满足。

SELECT c.id, c.value
FROM cocktails c
JOIN correspondences cor ON c.id = cor.`cocktail ids`
GROUP BY c.id, c.value
HAVING COUNT(DISTINCT cor.`ingredient ids`) = 
       COUNT(DISTINCT CASE WHEN cor.`ingredient ids` IN ({ingredient_ids}) THEN cor.`ingredient ids` END)

• COUNT(DISTINCT cor.ingredient ids)：统计当前鸡尾酒需要的总配料种类数
• 第二个COUNT：统计该鸡尾酒的配料中，存在于用户提供的ID数组里的种类数
• 两者相等则表示所有配料都在用户的列表中，符合要求

方法二：用NOT EXISTS排除不满足的鸡尾酒 #

这种方式逻辑更直观：找出不存在"不在用户配料列表中的必需配料"的鸡尾酒。

SELECT c.id, c.value
FROM cocktails c
WHERE NOT EXISTS (
    SELECT 1
    FROM correspondences cor
    WHERE cor.`cocktail ids` = c.id
    AND cor.`ingredient ids` NOT IN ({ingredient_ids})
)

• 子查询会检查当前鸡尾酒是否有任何配料不在用户的列表里
• NOT EXISTS确保只返回完全没有这类配料的鸡尾酒

PHP中安全处理用户输入 #

用户通过$_GET['ingredients']传入配料ID数组，必须先做过滤和防注入处理：

// 过滤并验证传入的配料ID，确保都是有效数字
$ingredientIds = isset($_GET['ingredients']) ? array_filter($_GET['ingredients'], 'is_numeric') : [];

if (empty($ingredientIds)) {
    echo "请选择至少一种配料";
    exit;
}

// 将ID转为整数，避免SQL注入
$safeIds = array_map(fn($id) => (int)$id, $ingredientIds);
$idPlaceholders = implode(',', $safeIds);

// 拼接SQL（以方法一为例，替换{ingredient_ids}为实际的ID字符串）
$sql = "
SELECT c.id, c.value
FROM cocktails c
JOIN correspondences cor ON c.id = cor.`cocktail ids`
GROUP BY c.id, c.value
HAVING COUNT(DISTINCT cor.`ingredient ids`) = 
       COUNT(DISTINCT CASE WHEN cor.`ingredient ids` IN ($idPlaceholders) THEN cor.`ingredient ids` END)
";

// 用PDO执行查询（示例）
$pdo = new PDO('mysql:host=localhost;dbname=your_database', 'username', 'password');
$stmt = $pdo->prepare($sql);
$stmt->execute();
$availableCocktails = $stmt->fetchAll(PDO::FETCH_ASSOC);

• 用array_filter和is_numeric过滤无效输入
• 强制转为整数避免SQL注入风险
• 推荐使用PDO或mysqli预处理语句进一步提升安全性

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