-
一般评论设计类型如下
邻接表适合做有限层数评论,且在递归查询的帮助下,使得查询更加高效。
枚举路径不能轻易删除子叶节点,容易产生冗余数据。
嵌套集删除子叶节点也可以保证树的连续性,但对树的操作移动非常困难
闭包表可无限层评论,可任意删除节点(后代自动顶替),可移动节点,但需要额外关系表,属于空间换时间的方案。
- 这里直接记录闭包表设计
CREATE TABLE `comments` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '评论ID',
`article_id` bigint NOT NULL COMMENT '文章ID',
`body` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '评论内容',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='评论表';
CREATE TABLE `comment_tree` (
`ancestor` bigint NOT NULL COMMENT '祖先评论ID',
`descendant` bigint NOT NULL COMMENT '后代评论ID',
UNIQUE KEY `_pk` (`ancestor`,`descendant`) USING BTREE COMMENT '复合主键'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='评论-祖先与后代关系表,特别注意:自己的后代必然存在自己';
- 直接上SQL语句
获取文章一级评论
select c.*,t.* FROM comment_tree AS t INNER JOIN comments AS c on c.id = t.descendant GROUP BY t.descendant HAVING COUNT(t.descendant)=1
根据一级评论ID获取子评论(查子树)
SELECT c.*,t.* FROM comments AS c INNER JOIN comment_tree AS t ON c.id = t.descendant WHERE t.ancestor = 1
根据子评论ID获取所有祖先(查祖先树)
SELECT c.*,t.* FROM comments AS c INNER JOIN comment_tree t ON c.id = t.ancestor WHERE t.descendant = 6
插入评论(例如ID为8)
INSERT INTO `dream`.`comments` (`id`,`article_id`, `body`) VALUES ('8','1', '怎么解决的的讲讲呗!')
插入关系(假如父节点ID为5,则需要跟5和所有5的祖先建立关系,并建立自我关系)
INSERT INTO comment_tree (ancestor, descendant) SELECT t.ancestor,8 FROM comment_tree AS t WHERE t.descendant = 5 UNION ALL SELECT 8,8
删除叶子节点
DELETE FROM comment_tree WHERE descendant = 7
删除子树
DELETE FROM comment_tree WHERE descendant IN(SELECT descendant FROM comment_tree WHERE ancestor = 4)
断开与祖先的关系(例如断开评论ID为8的祖先关系)
DELETE FROM comment_tree WHERE descendant =8
重新插入关系(例如父节点为4,记得建立自我关系)
INSERT INTO comment_tree (ancestor, descendant) SELECT t.ancestor,8 FROM comment_tree AS t WHERE t.descendant = 4 UNION ALL SELECT 8,8
- 欢迎探讨优化~