| 如何只用一句SQL语句查询评论和回复并分页 | 您所在的位置：网站首页 › 回复语句 › 如何只用一句SQL语句查询评论和回复并分页 |
如何只用一句SQL语句查询评论和回复并分页
|
1.需求
需要实现类似于百度贴吧的评论系统, 每层楼下可以有若干楼中楼(回复), 要注意楼中楼也是做了分页的, 通常点进一个帖子需要加载若干楼, 同时也要连同每楼的前N条回复一起显示, 也就是回复也要分页 如何在分页查询每层楼的同时对楼中楼分页呢, 首先这样的功能,单纯用SQL语句有几种实现方法(以下用评论表示每层楼, 回复表示楼中楼): 根据每层楼去查询相关的楼中楼, 这种方法查询压力很大, 而且效率较低, 首先排除;只用一张表, 根据父评论ID判断是一级评论还是子回复, 比如父评论ID为空则是一级评论, 否则就视为子回复, 这样有个好处就是查询相对简单, 评论和回复一一对应, 但也有问题: 分页比较困难, 猜测分页可能出现回复不能显示全的问题, 而且对前端不友好; 参考: 蚊子博客用两张表, 一张评论表, 一张回复表, 评论id作为回复表的外键, 需要嵌套一个子查询, 分页查出评论id, 根据评论id左连接回复表, 问题在这, MySQL在高版本增加了ONLY_FULL_GROUP_BY限制, 只能查询除聚合函数外group by后面的字段, 这么做据说是因为防止查询到定义不明确的字段, 存在即合理, 这里不取消这个模式, 自然有其他方法规避, 另外MySQL高版本不允许在子查询里使用limit, 其实只要再嵌套一个查询就可以 3.实现这里使用的是mybatis, 方便对结果集映射 数据库表设计: 评论表: 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
回复表: mybatis结果集映射: 查询 public List getCommentWithReplyListByPage(String belongId, Integer page, Integer size, Integer replyListSize); 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解释一下这个句子: 其实就是评论表左连接回复表后, 根据子查询(某个页面将要展示的评论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": [] } ] }前端页面效果: |
建表语句
其实还是蛮快的, 可能是数据量不大的缘故 
【本文地址】
公司简介
联系我们