评论模块数据库表设计

社交网站、内容平台上的评论模块。

一级评论

用户对帖子进行评论，最简单的就是一级评论。

1. 一个用户创建了一个帖子。
2. 一位用户对一篇帖子发表评论。

表设计如下：

二级评论

用户想要回复帖子上的其他评论，为了简单，我们仅支持二级评论。

1. 一个用户创建了一个帖子。
2. 一位用户对一篇帖子发表评论。
3. 另一个用户对评论发表评论（子评论）。

表设计如下：

举例：查询帖子id=1111的所有评论。

SELECT 
    post_id,
    parent.comment as level1_comment,
    child.comment as level2_comment
FROM
    Comment parent , Comment child
WHERE
    post_id = '1111' and 
    child.parent_comment_id = parent.comment_id

N级评论

如果支持N级评论，那么就会变成树了。

举例：查询帖子id=1111的所有评论。

方法一：重复自连接

重复自连接会变得很复杂，执行效率也最低，性能下降。

SELECT
    post_id,
    p1.comment as level1_comment,
    p2.comment as level2_comment,
    p3.comment as level3_comment,
    p4.comment as level4_comment,
    p5.comment as level5_comment,
    p6.comment as level6_comment
FROM        
      Comment p1
LEFT JOIN   
      Comment p2 on p2.parent_comment_id = p1.comment_id 
LEFT JOIN   
      Comment p3 on p3.parent_comment_id = p2.comment_id 
LEFT JOIN   
      Comment p4 on p4.parent_comment_id = p3.comment_id  
LEFT JOIN   
      Comment p5 on p5.parent_comment_id = p4.comment_id  
LEFT JOIN   
      Comment p6 on p6.parent_comment_id = p5.comment_id
WHERE
    post_id = '1111' and

方法二：递归 CTE

MySQL、PostgreSQL 支持递归 CTE (Common Table Expression) ，递归 CTE 可以用来创建 Path-style Identifiers ， 从而实现对数据库的查询。但这仅仅简化了查询语法和数据表示，并没有降低底层查询引擎的复杂性。

WITH RECURSIVE cte AS 
    ( SELECT 
             comment,
             comment_id AS path,
             user_id,
             post_id
      FROM
             Comment
      WHERE parent_comment_id IS NULL 
      UNION ALL
      SELECT 
         comment,
         CONCAT(parent.path,'/',child.name)comment_id AS comment_id,
         user_id,
         post_id
      FROM
         Comment parent , Comment child
      WHERE 
         child.parent_comment_id = parent.comment_id )
SELECT * FROM cte;

方法三：路径式标识符

在 Comment 增加 path 字段。

每个节点有一个路径字符串，代表从根节点到该节点的所有祖先的标识符，通常用分隔符（如点号“.”或斜杠“/”）连接。

举例：

1. 插入评论 1，path 为 1。
2. 插入评论 2，评论 2 是评论 1 的回复，path 为 1.2。
3. 插入评论 3，评论 3 是评论 2 的回复，path 为 1.2.3。
4. 查找所有评论 1 的子孙。

SELECT * FROM Comments WHERE path LIKE '1%';

方法四：闭包表

存储的关系信息，祖先节点和后代节点，不仅仅是那些具有直接父子关系的路径。

例如查询评论 comment_id=3 的所有子评论。

SELECT *
FROM
   Comment AS c
JOIN parent_child_comment p ON c.comment_id = p.child_comment_id
WHERE 
   p.parent_comment_id = 3;

参考

• Data Modeling — Designing Facebook style “comments” with SQL