MYSQL, 1:n的关系到单行有限制

Question

我有那些MySQL(简化)模式:

CREATE TABLE `library` (
  `id` varchar(32) NOT NULL,
  `app` varchar(32) NOT NULL,
  `filename` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `app` (`app`,`filename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `tags` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `app` varchar(32) DEFAULT NULL,
  `tag` varchar(55) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `app` (`app`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
CREATE TABLE `library_tags` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `file` varchar(32) DEFAULT NULL,
  `tag` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

我的目标是获得每个"库"条目的每个关联"标签"。如果没有与库条目关联的标记，则在标记的字段中返回NULL。

可以这样做:

SELECT SQL_CALC_FOUND_ROWS library.*,
  GROUP_CONCAT( tags.`id` ORDER BY tags.`id` ASC SEPARATOR ',' ) as `tag_id`,
  GROUP_CONCAT( tags.`tag` ORDER BY tags.`id` ASC SEPARATOR ',' ) as `tag_name`
FROM `library` AS library
LEFT JOIN `library_tags` AS libtags
  ON library.`id` = libtags.`file` 
LEFT JOIN `tags` AS tags
  ON libtags.`tag` = tags.`id` 
WHERE library.`app` = "53bd8997ad2ee"
GROUP BY library.`id` 
ORDER BY  library.`created` DESC
LIMIT 99999 OFFSET 0 

我的下一步是添加一个限制，根据标签的id返回库的条目，像这样:

SELECT SQL_CALC_FOUND_ROWS library.*,
  GROUP_CONCAT( tags.`id` ORDER BY tags.`id` ASC SEPARATOR ',' ) as `tag_id`,
  GROUP_CONCAT( tags.`tag` ORDER BY tags.`id` ASC SEPARATOR ',' ) as `tag_name`
FROM `library` AS library
LEFT JOIN `library_tags` AS libtags
  ON library.`id` = libtags.`file` 
LEFT JOIN `tags` AS tags
  ON libtags.`tag` = tags.`id` 
WHERE library.`app` = "53bd8997ad2ee"
  AND  tags.`id` IN (9,14) 
GROUP BY library.`id` 
ORDER BY  library.`created` DESC
LIMIT 99999 OFFSET 0 

这也可以，但是GROUP_CONCAT受到限制的影响，并且每个条目只返回选定的标记，而不是每个条目的完整标记列表。

我的问题是"如何保持这个单一的查询与每个条目标签的完整列表?"

附加的问题是:"这样做会影响MySQL的性能吗?"

谢谢你的帮助

Answer 1

将库标记限制放在LEFT JOIN条件中…

SELECT SQL_CALC_FOUND_ROWS library.*,
  GROUP_CONCAT( tags.`id` ORDER BY tags.`id` ASC SEPARATOR ',' ) as `tag_id`,
  GROUP_CONCAT( tags.`tag` ORDER BY tags.`id` ASC SEPARATOR ',' ) as `tag_name`
FROM `library` AS library
LEFT JOIN `library_tags` AS libtags
ON library.`id` = libtags.`file` 
LEFT JOIN `tags` AS tags
ON libtags.`tag` = tags.`id` 
AND tags.`id` IN (9,14) 
WHERE library.`app` = "53bd8997ad2ee"
GROUP BY library.`id` 
ORDER BY  library.`created` DESC
LIMIT 99999 OFFSET 0 

过滤附加的标记，而不是在LEFT JOIN完成后返回的行。它的运行速度应该与您的第一个查询相同，如果不是更快的话。