MarioBoy

评论系统数据库设计及实现
评论系统数据库设计及实现需求分析一般我们浏览网站的时候经常能看到如下图的这种效果(图片来自CSDN)这种评论层层嵌...
扫描右侧二维码阅读全文
14
2019/04

评论系统数据库设计及实现

评论系统数据库设计及实现

需求分析

一般我们浏览网站的时候经常能看到如下图的这种效果(图片来自CSDN)

这种评论层层嵌套,每个评论下面还挂着若干个对评论的回复。

这种结构类似于树状结构,用户看起来一目了然,也是一种非常主流的评论系统设计。

数据库设计

在以评论为主的树形结构中,数据库的设计非常灵活,可以是单表设计,每个评论都有一个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;

最终形成的效果图。

接口返回的数据如下:

{
    "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": []
                }
            ]
        }
    ]
}
Last modification:April 14th, 2019 at 11:48 pm
If you think my article is useful to you, please feel free to appreciate

Leave a Comment