评论系统数据库设计及实现
需求分析
一般我们浏览网站的时候经常能看到如下图的这种效果（图片来自CSDN）
image
这种评论层层嵌套，每个评论下面还挂着若干个对评论的回复。
这种结构类似于树状结构，用户看起来一目了然，也是一种非常主流的评论系统设计。
数据库设计
在以评论为主的树形结构中，数据库的设计非常灵活，可以是单表设计，每个评论都有一个parent_id指向父评论。还可以分开为两个表，评论一张表，对评论的回复是另一张表。
这里我使用的是单表设计。
数据表设计如下。由于我开发的是一个新闻系统，所以我就直接以项目举例。
| 表字段 | 字段说明 |
|---|---|
| commentId | 评论的id，自增值，每个评论都对应一个唯一的commentId |
| newsId | 评论所对应的新闻的id |
| content | 评论的内容 |
| userId | 发出该评论用户的id |
| parentId | 指向父评论的id,如果不是对评论的回复,那么该值为null |
| date | 评论产生日期 |
SQL语句：
评论表：
create table if not exists comments
(
commentId bigint auto_increment primary key,
newsId bigint not null,
parentId bigint,
content text not null,
userId bigint not null,
date timestamp default current_timestamp(),
foreign key (parentID) references comments (commentId),
foreign key (userID) references users (userId),
foreign key (newsID) references news (newsId)
) charset = utf8mb4;
实现
- 查询语句：
SELECT a.commentId,a.newsId,a.parentId,a.newsId,b.nickname,b.avatar,a.content,a.date
FROM comments AS a,users AS b WHERE a.newsId=#{newsId} AND a.userId=b.userId
为了减少数据库查询次数，直接一次将一个新闻下的所有评论都查询了出来，然后通过程序来编排评论的显示结构。通过适当的冗余来提高性能也是常用的优化手段之一
- 评论的实体类
import lombok.Data;
import java.util.Date;
import java.util.List;
@Data
public class Comment {
Long commentId;
Long newsId;
Long parentId;
Long userId;
String nickname;
String avatar;
String content;
Date date;
List<Comment> child;
}
这里给出一段通过程序来组织所有评论的代码（为了文章的精简，只写逻辑相关的代码）
public List<Comment> getComments(Long newsId) {
List<Comment> allComments = commentMapper.getComments(newsId);
if (allComments == null || allComments.size() == 0) {
return new ArrayList<>();
}
List<Comment> comments = new ArrayList<>();
List<Comment> parents = new ArrayList<>();
for (Comment comment : allComments) {
if (comment.getParentId()==null) {
comments.add(comment);
parents.add(comment);
} else {
boolean foundParent=false;
for (Comment parent : parents) {
if (comment.getParentId() == parent.getCommentId()) {
if (parent.getChild() == null) {
parent.setChild(new ArrayList<>());
}
parent.getChild().add(comment);
parents.add(comment);
foundParent=true;
//如果对list迭代过程中同时修改list，会报java.util.ConcurrentModificationException 的异常，所以我们需要break,当然break也可以提高算法效率
break;
}
}
if (!foundParent) {
throw new RuntimeException("can not find the parent comment");
}
}
}
return comments;
最终形成的效果图。
image
接口返回的数据如下：
{
"code": "success",
"message": "获取评论成功",
"status": "200",
"data": [
{
"id": "236051",
"author_name": "Jianbo",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/Qib5jkFMntPJnT8b2nyzKicoYSuXLeyl07ia1dianxx1fWcic9hJL4UOEuIJvoWWbx7IFia3olUGqiabZvTe0dmeFBicHQ/132",
"date": "6小时前",
"content": "tt",
"userid": "24",
"child": []
},
{
"id": "236028",
"author_name": "起航",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/7Aq39lKL2jxoWSMgbiaYkQzOR0mOMTm2TLjVhRicYaFXAzg20I8gpcqySYYYQMWG60p8r5kibG3ibiav3CC8Bzibjblw/132",
"date": "2019-04-11",
"content": "很朴实的文字，又让人感动唏嘘",
"formId": null,
"userid": "9676",
"child": [
{
"id": "236032",
"author_name": "Jianbo",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/Qib5jkFMntPJnT8b2nyzKicoYSuXLeyl07ia1dianxx1fWcic9hJL4UOEuIJvoWWbx7IFia3olUGqiabZvTe0dmeFBicHQ/132",
"date": "2天前",
"content": ":-)",
"userid": "24",
"child": [
{
"id": "236040",
"author_name": "God loves me",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/QTU6iasloiaun5OX6ZcZB964vhHLAc5RuIf8kMR3nwIXvy0HibYOe9RJ9o8escDOIj7MB1vica5ibZ2XSDXIibfQMsJA/132",
"date": "1天前",
"content": "为什么有人会选择安乐死呢，活着难道比不上痛苦吗",
"userid": "9663",
"child": [
{
"id": "236042",
"author_name": "Jianbo",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/Qib5jkFMntPJnT8b2nyzKicoYSuXLeyl07ia1dianxx1fWcic9hJL4UOEuIJvoWWbx7IFia3olUGqiabZvTe0dmeFBicHQ/132",
"date": "1天前",
"content": "如果无法有尊严的活着，就难受",
"child": []
}
]
}
]
}
]
},
{
"id": "236024",
"author_name": "倡萌",
"author_url": "../../images/gravatar.png",
"date": "2019-04-11",
"content": "每个人都有自己难以忘怀的过往，昨天今天明天，努力过好每一天！",
"userid": "0",
"child": [
{
"id": "236041",
"author_name": "Jianbo",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/Qib5jkFMntPJnT8b2nyzKicoYSuXLeyl07ia1dianxx1fWcic9hJL4UOEuIJvoWWbx7IFia3olUGqiabZvTe0dmeFBicHQ/132",
"date": "1天前",
"content": "过好今天，很重要"，
"userid": "24",
"child": []
}
]
},
{
"id": "236018",
"author_name": "Jielinfan",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/Q0j4TwGTfTJBXIvvpMo5nXdlk6Mxwia9chS9E8VHGEQbDmyEAx8opRibztDzmpGHpbC3lR5vh8l4fsScZWoyEWyQ/132",
"date": "2019-04-08",
"content": "祝福老哥。",
"userid": "280",
"child": [
{
"id": "236019",
"author_name": "Jianbo",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/Qib5jkFMntPJnT8b2nyzKicoYSuXLeyl07ia1dianxx1fWcic9hJL4UOEuIJvoWWbx7IFia3olUGqiabZvTe0dmeFBicHQ/132",
"date": "2019-04-09",
"content": ":-)",
"userid": "24",
"child": []
}
]
},
{
"id": "236017",
"author_name": "增大网",
"author_url": "../../images/gravatar.png",
"date": "2019-04-08",
"content": "送你一片大海，让你一帆风顺；送你一个太阳，让你热情奔放；送你一份真诚，祝你开心快乐；送你一份祝福，让你快乐天天！",
"formId": null,
"userid": "0",
"child": []
},
{
"id": "236011",
"author_name": "今日新闻",
"author_url": "../../images/gravatar.png",
"date": "2019-04-07",
"content": "文章不错非常喜欢",
"userid": "0",
"child": [
{
"id": "236052",
"author_name": "Jianbo",
"author_url": "https://wx.qlogo.cn/mmopen/vi_32/Qib5jkFMntPJnT8b2nyzKicoYSuXLeyl07ia1dianxx1fWcic9hJL4UOEuIJvoWWbx7IFia3olUGqiabZvTe0dmeFBicHQ/132",
"date": "6小时前",
"content": "谢谢",
"userid": "24",
"child": []
}
]
}
]
}
