如何验证playlist_slides关联标签是否全部存在于screens关联标签中

需求 #

验证分配给playlist_slides表的所有标签，是否全部存在于其关联screens表所分配的标签集合中。

数据库结构（简化版）及示例数据 #

CREATE TABLE `screens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `playlist_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ;

CREATE TABLE `playlist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ;

CREATE TABLE `playlist_slides` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `playlist_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ;

CREATE TABLE `resource_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ;

CREATE TABLE `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ;

-- 示例数据
INSERT INTO `screens` VALUES (1, 2);
INSERT INTO `playlist` VALUES (2);
INSERT INTO `playlist_slides` VALUES (3, 2);

-- screen 1的标签：1、2、3
INSERT INTO `resource_tags` VALUES (1, 1, 1);
INSERT INTO `resource_tags` VALUES (2, 2, 1);
INSERT INTO `resource_tags` VALUES (3, 3, 1);

-- playlist_slide 3的标签：1、3
INSERT INTO `resource_tags` VALUES (4, 1, 3);
INSERT INTO `resource_tags` VALUES (5, 3, 3);

INSERT INTO `tags` VALUES (1);
INSERT INTO `tags` VALUES (2);
INSERT INTO `tags` VALUES (3);

当前尝试的SQL语句 #

SELECT
  `media_content_array`.*,
  `current_screen_tags`.`screen_tags`
FROM `screens`
RIGHT JOIN (
  SELECT 
    `playlist_slides`.`playlist_id`,
    GROUP_CONCAT(
      playlist_content_tags.tag_id
      ORDER BY playlist_content_tags.tag_id
    ) AS tag_ids
  FROM `playlist_slides`
  LEFT JOIN `playlist_content_tags`
  ON `playlist_slides`.`id` = `playlist_content_tags`.`item_id`
  GROUP BY `playlist_slides`.`playlist_id`
) AS `media_content_array`
ON `media_content_array`.`playlist_id` = `screens`.`playlist_id`
RIGHT JOIN (
  SELECT
    `screens`.`id`,
    GROUP_CONCAT(
      resource_tags.tag_id
      ORDER BY resource_tags.tag_id
    ) AS screen_tags 
  FROM `screens`
  LEFT JOIN `resource_tags`
  ON `screens`.`id` = `resource_tags`.`item_id`
  WHERE `screens`.`id` = ?
  GROUP BY `screens`.`id`
) AS `current_screen_tags`
ON `screens`.`id` = `current_screen_tags`.`id`
WHERE `screens`.`id` = ?
AND `media_content_array`.`tag_ids` LIKE CONCAT('%', `current_screen_tags`.`screen_tags`, '%');

遇到的问题 #

使用media_content_array.tag_ids LIKE CONCAT('%', current_screen_tags.screen_tags, '%')进行匹配时，仅当两个标签字符串的顺序完全一致才会生效，无法处理标签顺序不同但集合完全包含的场景（比如screen标签是1,2,3，playlist_slide标签是3,1，此时应该判定为全部存在，但LIKE匹配会失败）。此外，这种字符串匹配方式还可能出现误判（比如screen标签是1,2，playlist_slide标签是11,2，LIKE会错误匹配）。

解决方案 #

方法1：使用NOT EXISTS验证不存在缺失标签 #

通过子查询检查每个playlist_slide的标签是否全部存在于对应screen的标签集合中，逻辑直接且可靠：

-- 查询指定screen下，所有标签全部匹配的playlist_slides
SELECT 
    ps.id AS playlist_slide_id,
    ps.playlist_id
FROM playlist_slides ps
JOIN screens s ON ps.playlist_id = s.playlist_id
WHERE s.id = ? -- 传入目标screen的ID
AND NOT EXISTS (
    -- 检查当前playlist_slide是否存在不在screen标签中的标签
    SELECT 1
    FROM resource_tags rt_slide
    WHERE rt_slide.item_id = ps.id
    AND rt_slide.tag_id NOT IN (
        SELECT rt_screen.tag_id
        FROM resource_tags rt_screen
        WHERE rt_screen.item_id = ? -- 传入同一个screen的ID
    )
);

方法2：通过COUNT对比验证标签完全包含 #

统计每个playlist_slide的总标签数，以及其中存在于screen标签中的数量，若两者相等则说明全部标签都匹配：

SELECT 
    ps.id AS playlist_slide_id,
    ps.playlist_id
FROM playlist_slides ps
JOIN screens s ON ps.playlist_id = s.playlist_id
LEFT JOIN resource_tags rt_slide ON ps.id = rt_slide.item_id
-- 关联当前screen的标签集合
LEFT JOIN (
    SELECT tag_id FROM resource_tags WHERE item_id = ?
) rt_screen ON rt_slide.tag_id = rt_screen.tag_id
WHERE s.id = ? -- 传入目标screen的ID
GROUP BY ps.id, ps.playlist_id
-- 总标签数等于匹配到的标签数，说明无缺失
HAVING COUNT(rt_slide.tag_id) = COUNT(rt_screen.tag_id);

说明 #

两种方法均不依赖标签的顺序，而是基于集合的包含关系进行验证，彻底解决原方法的局限性。其中方法1的性能通常更优，尤其是在标签数据量较大的场景下。

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