查询含全部指定标签的剧目：SQL语句错误排查

问题场景 #

现有三张数据表：

• 剧目表tbl_plays：存储剧目ID和标题
• 标签表tbl_tags：存储标签ID和名称
• 标签关联表tbl_tagged：关联剧目和标签，一个剧目可对应多个标签

需求是：仅返回包含所有选中标签的剧目。例如：

• 选中Children's Theatre（tag_ID=1）时，返回《Three Blind Mice》和《Narnia, a Musical Play》
• 同时选中Children's Theatre和Musical Theatre（tag_ID=1、2）时，仅返回《Narnia, a Musical Play》

当前查询的问题 #

你提供的SQL存在两个关键问题：

1. SELECT列与GROUP BY不匹配：查询中返回了tbl_tagged.tag_ID，但GROUP BY仅按play_ID分组，在MySQL开启ONLY_FULL_GROUP_BY模式时会直接报错，且分组后单个tag_ID无实际意义。
2. 示例数据与需求描述不一致：你描述中《Narnia, a Musical Play》应关联标签1和2，但提供的tbl_tagged数据里它关联的是1和3，这会导致测试时无法得到预期结果。

修正后的SQL查询 #

SELECT 
    p.play_ID,
    p.play_title
FROM
    tbl_plays p
INNER JOIN tbl_tagged tg ON p.play_ID = tg.play_ID
WHERE
    tg.tag_ID IN (1, 2) -- 替换为你选中的标签ID列表
GROUP BY
    p.play_ID, p.play_title -- 按所有非聚合列分组，符合SQL标准
HAVING
    COUNT(DISTINCT tg.tag_ID) = 2 -- 数字需与IN中的标签数量一致

关键说明 #

• 使用COUNT(DISTINCT tg.tag_ID)：避免同一剧目重复关联同一标签的情况（即使关联表数据唯一，该写法也更健壮）。
• GROUP BY包含play_ID和play_title：符合SQL标准，避免分组逻辑错误。
• 仅返回剧目核心信息：不需要返回标签ID，因为分组后单个标签ID无法代表剧目所有关联标签。

PHP MySQLi 动态实现（避免SQL注入） #

如果需要在PHP中动态传入标签列表，必须使用预处理语句防止注入：

// 示例：选中的标签ID数组
$tagIds = [1, 2];
$tagCount = count($tagIds);
// 生成占位符
$placeholders = implode(',', array_fill(0, $tagCount, '?'));

// 预处理查询
$stmt = $mysqli->prepare("
    SELECT p.play_ID, p.play_title
    FROM tbl_plays p
    INNER JOIN tbl_tagged tg ON p.play_ID = tg.play_ID
    WHERE tg.tag_ID IN ($placeholders)
    GROUP BY p.play_ID, p.play_title
    HAVING COUNT(DISTINCT tg.tag_ID) = ?
");

// 绑定参数：先绑定标签ID，再绑定标签数量
$types = str_repeat('i', $tagCount) . 'i';
$params = array_merge($tagIds, [$tagCount]);
$stmt->bind_param($types, ...$params);

// 执行并获取结果
$stmt->execute();
$result = $stmt->get_result();
$matchedPlays = $result->fetch_all(MYSQLI_ASSOC);

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