如何只用一句SQL语句查询评论和回复并分页

最新推荐文章于 2024-03-22 12:10:46 发布
最新推荐文章于 2024-03-22 12:10:46 发布
阅读量3.2k 收藏 31
点赞数 3
CC 4.0 BY-SA版权
分类专栏： 笔记 文章标签： mysql java 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Rhaegal_0411/article/details/119888941
本文介绍了如何设计一个类似百度贴吧的评论系统，包括评论和楼中楼的分页查询。通过使用两个表（评论表和回复表）来存储数据，并利用MyBatis进行结果集映射。查询策略上，通过嵌套查询解决MySQL的ONLY_FULL_GROUP_BY限制，以及在分页时获取每层楼下的前N条楼中楼回复。在实际测试中，查询性能表现良好。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

1.需求

需要实现类似于百度贴吧的评论系统, 每层楼下可以有若干楼中楼(回复), 要注意楼中楼也是做了分页的, 通常点进一个帖子需要加载若干楼, 同时也要连同每楼的前N条回复一起显示, 也就是回复也要分页
在这里插入图片描述

2.思路

如何在分页查询每层楼的同时对楼中楼分页呢, 首先这样的功能,单纯用SQL语句有几种实现方法(以下用评论表示每层楼, 回复表示楼中楼):

  1. 根据每层楼去查询相关的楼中楼, 这种方法查询压力很大, 而且效率较低, 首先排除;
  2. 只用一张表, 根据父评论ID判断是一级评论还是子回复, 比如父评论ID为空则是一级评论, 否则就视为子回复, 这样有个好处就是查询相对简单, 评论和回复一一对应, 但也有问题: 分页比较困难, 猜测分页可能出现回复不能显示全的问题, 而且对前端不友好; 参考: 蚊子博客
  3. 用两张表, 一张评论表, 一张回复表, 评论id作为回复表的外键, 需要嵌套一个子查询, 分页查出评论id, 根据评论id左连接回复表, 问题在这, MySQL在高版本增加了ONLY_FULL_GROUP_BY限制, 只能查询除聚合函数外group by后面的字段, 这么做据说是因为防止查询到定义不明确的字段, 存在即合理, 这里不取消这个模式, 自然有其他方法规避, 另外MySQL高版本不允许在子查询里使用limit, 其实只要再嵌套一个查询就可以

3.实现

这里使用的是mybatis, 方便对结果集映射

  1. 数据库表设计:
    评论表: reply_count是为了记录评论下的回复数, 方便对回复做分页
    在这里插入图片描述
    建表语句
CREATE TABLE `comment` (
  `id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '评论id',
  `user_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '评论者id',
  `content` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '评论内容',
  `belong_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '所属id(比如文章id)',
  `reply_count` bigint DEFAULT NULL COMMENT '回复数',
  `date` datetime DEFAULT NULL COMMENT '评论id',
  `status` tinyint DEFAULT NULL COMMENT '评论状态',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

回复表:
在这里插入图片描述
建表语句

CREATE TABLE `reply` (
  `id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '回复id',
  `comment_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '评论(父回复)id',
  `from_user_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '回复者id',
  `to_user_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '被回复者id',
  `content` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '回复内容',
  `date` datetime DEFAULT NULL COMMENT '回复日期',
  `status` tinyint DEFAULT NULL COMMENT '回复状态',
  PRIMARY KEY (`id`),
  KEY `comment_id` (`comment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mybatis结果集映射:

<resultMap id="commentMap" type="com.rhaegal.Vo.CommentVo">
        <id column="commentId" property="id"/>
        <result column="commentContent" property="content"/>
        <result column="commentDate" property="date"/>
        <result column="replyCount" property="replyCount"/>
        <result column="commentUserId" property="userId"/>
        <result column="commentUserNickname" property="nickname"/>
        <result column="commentUserAvatar" property="avatar"/>
        <collection column="reply" property="replies" resultMap="replyMap">
            <id column="replyId" property="id"/>
        </collection>
    </resultMap>

    <resultMap id="replyMap" type="com.rhaegal.Vo.ReplyVo">
        <id column="replyId" property="id"/>
        <result column="replyContent" property="content"/>
        <result column="replyDate" property="date"/>
        <result column="fromUserId" property="fromUserId"/>
        <result column="fromUserNickname" property="fromUserNickname"/>
        <result column="fromUserAvatar" property="fromUserAvatar"/>
        <result column="toUserId" property="toUserId"/>
        <result column="toUserNickname" property="toUserNickname"/>
        <result column="toUserAvatar" property="toUserAvatar"/>
    </resultMap>
  1. 查询
public List<Comment> getCommentWithReplyListByPage(String belongId, Integer page, Integer size, Integer replyListSize);

<select id="getCommentWithReplyListByPage" resultMap="commentMap">
        select comment.id           as commentId,
               comment.content      as commentContent,
               comment.date         as commentDate,
               comment.reply_count  as replyCount,
               commentUser.id       as commentUserId,
               commentUser.nickname as commentUserNickname,
               commentUser.avatar   as commentUserAvatar,
               reply.id             as replyId,
               reply.content        as replyContent,
               reply.date           as replyDate,
               fromUser.id          as fromUserId,
               fromUser.nickname    as fromUserNickname,
               fromUser.avatar      as fromUserAvatar,
               toUser.id            as toUserId,
               toUser.nickname      as toUserNickname,
               toUser.avatar        as toUserAvatar
        from comment
                 left join user commentUser on comment.user_id = commentUser.id
                 /* 注意如果是根据回复状态(status=1表示正常)查询, 要在这里加左表的判断条件 */
                 left join reply on comment.id = reply.comment_id and reply.status = 1
                 left join user fromUser on reply.from_user_id = fromUser.id
                 left join user toUser on reply.to_user_id = toUser.id
                 /* 这里又嵌套了一次是为了规避mysql对子查询使用limit的限制 */
        where comment.id in (select id
                             from (select id
                                   from comment
                                   /* 评论的状态判断要在这里做 */
                                   where belong_id = #{param1} and comment.status = 1
                                   order by date asc limit #{param2}, #{param3}) as comment_id_list)
          /* 这里相当于对comment_id分组后根据某些条件排序(这里根据id, 也可以根据date判断), 然后取每组前N个 */
          and #{param4} > (select count(*)
                   from reply r
                   where r.comment_id = reply.comment_id and r.status = 1 and r.id > reply.id)
        order by comment.date asc, reply.date desc
    </select>

解释一下这个句子: 其实就是评论表左连接回复表后, 根据子查询(某个页面将要展示的评论ID列表) 分组后筛选出符合条件的前N个, 其实完全可以分两次查询: 把子查询分离出来, 不知道两次嵌套还走不走索引, 可能两次查询反而快, 回复的分页就比较简单了, 根据comment_id做分页查就行了, 这里就不再赘述了

结果

用postman简单测试一下

@RequestMapping(value = "/list/{belongId}/{page}/{size}", method = RequestMethod.GET)
    public Result getCommentWithReplyListByPage(@PathVariable String belongId, @PathVariable Integer page, @PathVariable Integer size){
        return Result.success("查找评论列表成功", commentService.getCommentWithReplyListByPage(belongId, page, size));
    }

在这里插入图片描述
其实还是蛮快的, 可能是数据量不大的缘故
在这里插入图片描述

查询结果(把头像链接去掉了): 还是比较层次分明的, toUser的信息为null表示对楼层的直接回复

{
    "flag": true,
    "code": 20000,
    "message": "查找评论列表成功",
    "data": [
        {
            "id": "1430049583047720960",
            "content": "测试评论1",
            "date": "2021-08-24 14:09:24",
            "replyCount": 3,
            "userId": "1420663680214781952",
            "nickname": "测试昵称",
            "avatar": "",
            "replies": [
                {
                    "id": "1430050292354859008",
                    "content": "测试楼中楼回复1",
                    "date": "2021-08-24 14:12:13",
                    "fromUserId": "1420664104837730304",
                    "fromUserNickname": "测试昵称2",
                    "fromUserAvatar": "",
                    "toUserId": "1420663680214781952",
                    "toUserNickname": "测试昵称",
                    "toUserAvatar": ""
                },
                {
                    "id": "1430049889659731968",
                    "content": "评论1回复2",
                    "date": "2021-08-24 14:10:37",
                    "fromUserId": "1420663680214781952",
                    "fromUserNickname": "测试昵称",
                    "fromUserAvatar": "",
                    "toUserId": null,
                    "toUserNickname": null,
                    "toUserAvatar": null
                },
                {
                    "id": "1430049864829452288",
                    "content": "评论1回复1",
                    "date": "2021-08-24 14:10:31",
                    "fromUserId": "1420663680214781952",
                    "fromUserNickname": "测试昵称",
                    "fromUserAvatar": "",
                    "toUserId": null,
                    "toUserNickname": null,
                    "toUserAvatar": null
                }
            ]
        },
        {
            "id": "1430049607609565184",
            "content": "测试评论2",
            "date": "2021-08-24 14:09:30",
            "replyCount": 8,
            "userId": "1420663680214781952",
            "nickname": "测试昵称",
            "avatar": "",
            "replies": [
                {
                    "id": "1430050346071310336",
                    "content": "评论2测试楼中楼1",
                    "date": "2021-08-24 14:12:26",
                    "fromUserId": "1420664104837730304",
                    "fromUserNickname": "测试昵称2",
                    "fromUserAvatar": "",
                    "toUserId": "1420663680214781952",
                    "toUserNickname": "测试昵称",
                    "toUserAvatar": ""
                },
                {
                    "id": "1430049826644508672",
                    "content": "评论2回复7",
                    "date": "2021-08-24 14:10:22",
                    "fromUserId": "1420663680214781952",
                    "fromUserNickname": "测试昵称",
                    "fromUserAvatar": "",
                    "toUserId": null,
                    "toUserNickname": null,
                    "toUserAvatar": null
                },
                {
                    "id": "1430049804234342400",
                    "content": "评论2回复6",
                    "date": "2021-08-24 14:10:17",
                    "fromUserId": "1420663680214781952",
                    "fromUserNickname": "测试昵称",
                    "fromUserAvatar": "",
                    "toUserId": null,
                    "toUserNickname": null,
                    "toUserAvatar": null
                },
                {
                    "id": "1430049782524624896",
                    "content": "评论2回复5",
                    "date": "2021-08-24 14:10:12",
                    "fromUserId": "1420663680214781952",
                    "fromUserNickname": "测试昵称",
                    "fromUserAvatar": "",
                    "toUserId": null,
                    "toUserNickname": null,
                    "toUserAvatar": null
                },
                {
                    "id": "1430049769534865408",
                    "content": "评论2回复4",
                    "date": "2021-08-24 14:10:09",
                    "fromUserId": "1420663680214781952",
                    "fromUserNickname": "测试昵称",
                    "fromUserAvatar": "",
                    "toUserId": null,
                    "toUserNickname": null,
                    "toUserAvatar": null
                }
            ]
        },
        {
            "id": "1430049617877221376",
            "content": "测试评论3",
            "date": "2021-08-24 14:09:33",
            "replyCount": 0,
            "userId": "1420663680214781952",
            "nickname": "测试昵称",
            "avatar": "",
            "replies": []
        },
        {
            "id": "1430049636625760256",
            "content": "测试评论4",
            "date": "2021-08-24 14:09:37",
            "replyCount": 0,
            "userId": "1420663680214781952",
            "nickname": "测试昵称",
            "avatar": "",
            "replies": []
        },
        {
            "id": "1430049654212476928",
            "content": "测试评论5",
            "date": "2021-08-24 14:09:41",
            "replyCount": 0,
            "userId": "1420663680214781952",
            "nickname": "测试昵称",
            "avatar": "",
            "replies": []
        }
    ]
}

前端页面效果:
在这里插入图片描述

Springboot评论回复功能:利用mybatis-plus 中的page分页已有的list
weixin_43894382的博客
03-31 870
2.用list去封装评论信息，然后循环遍历集合，在用list封装该改评论下所有回复数据，最后在将回复list封装到评论list中，这样我就得到了包含所有回复数据的评论集合。问题：在做文章评论回复模块时，需要将文章下的所有评论以及评论下的回复数据都查出来，把对应的回复数据都显示在对应的评论下方，然后对评论数据进行分页。1.根据文章id去评论表中查询该文章下的所有评论，然后再根据每个评论id去回复表中查询该评论下的所有回复内容，查询的数据均按照更新时间进行排序。PageListUtility 工具类：（重要）
【JSqlParser】Java使用JSqlParser解析SQL语句总结
最新发布
m0_74823364的博客
01-01 1356
上面的代码虽然不少，但实际上真正需要熟悉的只有一个，就是直接调用CCJSqlParserUtil.parse(sql);去获得Statement，然后通过Statement去操作和获取解析后的SQL中的内容，非常简单方便实际应用场景说了那么多JSQLPARSER的使用，或许很多朋友并不能联想到有哪些具体可以用到它的地方，实际上想要开发一个优秀的软件产品，那么细节是少不了的，SQL是BS软件的本质之一，那么针对SQL，我们能做的还有很多，以下列举几个常见的场景SQL审计和分析。
优化评论分页查询
weixin_34148340的博客
05-05 387
possible_keys、key、key_len都为null，可见在表上是没有可用索引的计算区分度，越接近1区分度越好，应该放到联合索引的左侧建好联合索引之后的explain：翻页越多，速度越慢，进一步优化：优化的前提：comment_id是商品评论表的主键，且有覆盖索引原理：利用覆盖索引，取出主键comment_id，再进行排序，取出所需数据，之后再同评论...
redis实现直播间评论分页查询
成辉的博客
06-22 503
redis分页代码，通俗易懂
回复评论的sql
qq_45914437的博客
06-28 386
草稿
商品评论分页查询
Speical的博客
08-30 826
商品评论
JSF分页实现与建表SQL语句教程
建表语句通常是指在数据库中创建表的SQL语句。在本例中，建表语句用于配合JSF分页实现。一般在分页功能中，我们会预先创建一个或多个数据库表来存储需要展示的数据。例如： ```sql CREATE TABLE `example_table` ( ...
一句话轻松搞定asp.net分页
10-21
本文将深入探讨如何使用一句简单代码实现ASP.NET分页，并结合数据库操作和JSON数据传输，以实现高效的数据呈现。 首先，分页的核心在于正确地构造SQL查询语句。在给出的例子中，我们使用了SQL Server的`TOP`关键字...
SQL语句优化（姊妹篇2）
右见小栈
03-22 1052
工作中你一定能用到的sql语句优化，看完背下来吧
mysql评论表如何查询,如何在MYSQL中发表评论回复查询？
weixin_28937805的博客
01-18 324
I am having comment reply (only till one level) functionality. All comments can have as many as replies but no replies can have their further replies.So my database table structure is like belowId ...
mysql评论回复_如何在MYSQL中进行评论回复查询？
weixin_42110070的博客
01-19 997
我正在评论回复(仅限一个级别)功能.所有评论都可以有多少回复,但没有回复可以进一步回复.所以我的数据库表结构如下所示Id ParentId Comment1 0 this is come sample comment text2 0 this is come sample comment text3 0 ...
评论的操作以及分页查询
weixin_61946177的博客
04-01 234
添加评论 <% //接收新闻编号 String id = request.getParameter("nid"); int nid = Integer.valueOf(id); String ntitle = ""; String nzz = ""; String ndate = ""; String nnr = ""; //连接数据库 ...
评论设计实现，完全模范bilibili实现评论功能，表的设计、sql如何查找，以及前端的一些设计
热门推荐
to_study的博客
07-25 2万+
如图，bilibili的评论主要分为根评论（顶级评论）和子评论（回复评论），名称我乱说的哈哈，这里的顶级评论就是最顶级的意思，他不是任何评论的回复评论，bilibili前端显示也没有那种层层递进，但我相信表的结构设计肯定是有所体现的 红色为顶级评论，蓝色为子评论 1.表结构设计 废话不多说，首先是表结构的设计，一般评论应该有的属性字段我就不说了，这里主要考虑说明的是根评论与子评论（回复评论）是一个表还是两个表的问题，考虑到如果是两张表话，那就必须得为子评论再加一张子评论点赞表以及子评论又有子评论呢，
第三章-电商项目-优化评论分页查询
炫的博客
09-25 603
1优化评论分页查询 建立索引 1111。如何删除重复数据 222备份表 33删除同一订单的重复评论 4检查是否有相同评论 5如何进行分区间统计 6捕获有问题的SQL 启用mysql慢查询的日志
mysql sql语句 查询文章表，评论表（根据评论表文章评论次数排序查询出文章count（），group，left join）
qq_39889272的博客
06-07 5247
需求：表1，文章表，表2，评论表，第一种实现方式：        SELECT a.id,a.title,a.content,a.type_id,count(b.id) as d  from message as a LEFT JOIN  comment as b on a.id=b.id GROUP BY b.id ORDER BY d  desc第二种实现方式：    SELECT a.id,...
MySQL 排序、分页查询、聚合查询
Michael是个半路程序员
06-01 3072
文章目录1. 排序2. 分页查询3. 聚合查询3.1 分组聚合 GROUP BY练习 LeetCode 182. 查找重复的电子邮箱 学习自 廖雪峰的官方网站 1. 排序 SELECT查询时，是根据主键排序 根据其他条件排序，可以加上ORDER BY子句（默认升序 ASC，可省略） SELECT id, name, gender, score FROM students ORDER BY score; 降序DESC ORDER BY <列名> DESC # 降序排列 SELECT
千峰商城-springboot项目搭建-69-商品评论分页查询接口实现
LYly_B的博客
07-21 205
一、流程分析 二、接口开发：改造商品评论列表接口(分页查询) 1.定义PageHelper： @Data @NoArgsConstructor @AllArgsConstructor public class PageHelper<T> { //总记录数 private int count; //总页数 private int page...
用户、留言、评论表的查询以及展示
小菜鸟的HelloWorld
04-27 7273
一、用户表SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `...
评论 19
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值