MySQL一对多分页查询-主表关联表条件查询问题

1 摘要

分页查询是后台项目中最常见的一种操作，在一对多(one to many)的关系表中，如何根据主表进行分页是一个较为棘手的问题，尤其是查询条件既包含主表又包含从表/关联表的。一般情况下，不是查询结果条数不正确，就是一对多中的多的一方只能显示一条信息。本文将记录作者自己在项目中针对一对多分页查询主表和关联表都有查询条件的一种解决方案。

关于分页查询，也可参考作者之前的笔记:

mysql多表联合查询分页查询结果条数错误问题

2 情景复现

2.1 数据模型

在先前的分页查询笔记中使用的是用户-用户图像这一简单的一对多模型，在本次示例中使用另一种更加复杂一些的模型，多对多模型: 文章与标签模型。

一篇文章可以对应多个标签，同时一个标签也可以对应多篇文章。不过这里依旧以文章为主表。

文章表:

CREATE TABLE `article` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '文章 id,主键',
  `title` varchar(100) NOT NULL DEFAULT '' COMMENT '文章标题',
  `content` text  NOT NULL COMMENT '文章内容',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='文章表';

标签表:

CREATE TABLE `article_tag` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '文章标签 id,主键',
  `tag_name` varchar(20) NOT NULL DEFAULT '' COMMENT '标签名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='文章标签表';

在多对多关系中，建议使用中间表进行关联

文章与标签的关联表:

CREATE TABLE `article_to_tag` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '文章-标签中间表',
  `article_id` bigint(20) NOT NULL COMMENT '文章 id',
  `tag_id` bigint(20) NOT NULL COMMENT '标签 id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='文章-标签关联表';

2.2 核心代码

由于在前边的笔记中已经介绍过一对多分页查询的方式: 使用子查询，因此这里就直接进入正题，不再演示不合理或者说错误的查询方法。

需求: 根据条件查询文章，该条件既包含主表的字段，也(可能)包含子表的字段，要求使用分页查询

统计查询结果总条数:

SELECT COUNT(DISTINCT(a.id))
FROM `article` a 
LEFT JOIN `article_to_tag` att ON att.article_id = a.id 
LEFT JOIN `article_tag` at ON at.id = att.tag_id
WHERE a.id > 2
    AND at.tag_name LIKE CONCAT(CONCAT('%', '级'),'%')

分页查询结果:

SELECT 
  a.id, a.title, a.content, at.tag_name
FROM (
    SELECT item_a.*
		FROM `article` item_a
		LEFT JOIN `article_to_tag` item_att ON item_att.article_id = item_a.id 
		LEFT JOIN `article_tag` item_at ON item_at.id = item_att.tag_id
		WHERE item_a.id > 2
        AND item_at.tag_name LIKE CONCAT(CONCAT('%', '级'),'%')
		GROUP BY item_a.id
		ORDER BY item_a.id DESC
		LIMIT 0,5
) a 
LEFT JOIN `article_to_tag` att ON att.article_id = a.id 
LEFT JOIN `article_tag` at ON at.id = att.tag_id
GROUP BY a.id, at.id
ORDER BY a.id DESC

这里演示的SQL中查询条件为: 文章的 id > 2,同时文章的标题中包含 「级」

注意事项: 这里的子查询只是将查询总结果按照需要的方式排列，但是实际返回到前端的数据依然需要进行排序和分组，否则，依然会出现查询结果不符合要求的情况。即内层子查询和外层查询都需要进行排序和分组

统计条数的查询结果为:

4

分页查询结果为:

从结果总可以看出 id 为 5,6,7,8 的文章满足以上要求

以上便是分页查询一对多主表子表都有查询条件的解决方案

2.3 测试数据

本示例中演示的测试数据，感兴趣的可以自行实践

文章标签数据:

-- 批量插入文章标签
INSERT INTO `article_tag`(`tag_name`) VALUES
    ('初级'),
		('中级'),
		('高级'),
		('超高级'),
		('spring'),
		('springBoot'),
		('springMVC');

文章表数据:

-- 批量插入文章
INSERT INTO `article` (`title`, `content`) VALUES
    ('好风光', '今天天气好晴朗，处处好风光'),
		('冰雨', '冷冷的冰雨在我脸上胡乱地拍，你就像一个刽子手把我出卖'),
		('学习', '好好学习,天天向上'),
		('静夜思', '窗前明月光，疑是地上霜。举头望明月，低头思故乡。'),
		('冬日里的一把火', '你就像那一把火，熊熊火焰燃烧了我'),
		('演员', '简单点，说话的方式简单点。递进的情绪请省略，你又不是个演员'),
		('小苹果', '你是我的小丫小苹果，怎么爱你都不嫌多'),
		('雨一直下', '雨一直下，气氛不算融洽');

文章与标签关联数据:

-- 批量给文章添加标签
INSERT INTO `article_to_tag` (`article_id`, `tag_id`) VALUES
    (1,1),
    (1,2),
    (1,4),
    (2,3),
    (2,5),
    (3,6),
    (4,7),
    (5,1),
    (5,2),
    (5,3),
    (6,4),
    (6,1),
    (6,5),
    (6,7),
    (7,6),
    (7,1),
    (8,3),
    (8,6),
    (8,7),
    (8,4);

2.4 拓展一点

文章通常有用户进行收藏，而在查询文章时，也可以显示文章的收藏量数据(这个需求是正常需求)

文章的收藏量不同于文章的阅读量，文章的阅读量可以用一个字段就能够实现，有用户点增加值即可，但是用户收藏就不一样，因为用户可以查询到自己收藏的内容，这是一个正常的需求，反过来，某一篇文章被哪些用户收藏，这一功能也是可以实现的(不过这个需求个人感觉有些过分，有泄露隐私的嫌疑)

OK，既然要实现用户收藏文章这一功能，就需要专门创建一个用户文章收藏表。

用户文章收藏表:

CREATE TABLE `article_user_favorite` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户收藏表id',
  `user_id` bigint(20) NOT NULL COMMENT '用户 id',
  `article_id` bigint(20) NOT NULL COMMENT '文章 id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户文章收藏表';

插入一些测试数据：

-- 批量收藏文章
INSERT INTO `article_user_favorite`(`user_id`, `article_id`) VALUES
    (1,1),
		(1,2),
		(1,3),
		(1,4),
		(1,5),
		(1,6),
		(1,7),
		(1,8),
		(2,1),
		(3,1),
		(4,1),
		(2,2),
		(2,6),
		(3,1),
		(4,1),
		(5,1),
		(3,2),
		(3,6),
		(3,7),
		(4,8),
		(4,5),
		(5,2),
		(5,3),
		(5,4),
		(5,5),
		(5,6),
		(5,7),
		(5,8),
		(4,4),
		(3,3);

还是按照上边的查询条件: 文章 id > 2, 文章的标签包含「级」
这里需要显示每一篇文章的收藏量

查询SQL:

SELECT 
  a.id, a.title, a.content, at.tag_name,
	COUNT(auf.id) AS countUserFavorite
FROM (
    SELECT item_a.*
		FROM `article` item_a
		LEFT JOIN `article_to_tag` item_att ON item_att.article_id = item_a.id 
		LEFT JOIN `article_tag` item_at ON item_at.id = item_att.tag_id
		WHERE item_a.id > 2
        AND item_at.tag_name LIKE CONCAT(CONCAT('%', '级'),'%')
		GROUP BY item_a.id
		ORDER BY item_a.id DESC
		LIMIT 0,5
) a 
LEFT JOIN `article_to_tag` att ON att.article_id = a.id 
LEFT JOIN `article_tag` at ON at.id = att.tag_id
LEFT JOIN `article_user_favorite` auf ON auf.article_id = a.id
GROUP BY a.id, at.id
ORDER BY a.id DESC

查询结果:

个人公众号:404Code,记录半个互联网人的技术与思考，感兴趣的可以关注.