SQL Antipattern 树之反模式(评论回复数据库表设计)

最新推荐文章于 2023-12-22 22:32:12 发布

起个昵称总重复

最新推荐文章于 2023-12-22 22:32:12 发布

阅读量526

点赞数

我们通常在SQL中实现数，都使用了邻接表。但是事实上邻接表却有相当多的不足，相信属性SQL的开发者也应该清楚了。例如在设置评论的表我们如如下设计：

CREATE TABLE comments(comment_id INT PRIMARY KEY AUTO_INCREMENT,
              comment_text VARCHAR(300),
              user_id INT,
              reply_comment_id INT,
        FOREIGN KEY(reply_comment_id) REFERENCES comments(comment_id),
        FOREIGN KEY(user_id) REFERENCES users(user_id));

但是事实上这样做也有非常多的不足，例如查询整个树的信息我们就需要触发多条SQL去满足这一需求。如果是设计这样的表比较适合呢？路径枚举和闭包表是常用的解决方案，以下就看看什么叫路径枚举和闭包表。

路径枚举非常简答，就是讲整个路径存储在一个字段当中，然后通过路径字符串查询的方式去获得相应的数数据
我们创建如下表去说明问题：

CREATE TABLE comments(comment_id INT PRIMARY KEY AUTO_INCREMENT,
            comment_text VARCHAR(300),user_id INT,
            comment_path VARCHAR(500),
            FOREIGN KEY(user_id) REFERENCES users(user_id));

我们现在实现一个比较常见的业务，就是像朋友圈那样互相评论，然后我会将一个评论数查出，先插入评论语句：

INSERT INTO comments(comment_text,user_id) VALUES('TONY:发起的第一条评论！',1);
UPDATE comments SET comment_path = concat(last_insert_id(),'/' ) WHERE comment_id = last_insert_id() ;

INSERT INTO comments(comment_text,user_id) VALUES('YAN: 回复TONY发起的第一条评论',2);
SET @comment_path=concat( (SELECT comment_path FROM comments WHERE comment_id = 1 ) , concat(last_insert_id() , '/' )) ;
UPDATE comments 
SET comment_path = @comment_path
WHERE comment_id = last_insert_id();

INSERT INTO comments(comment_text,user_id) VALUES('TONY:回复YAN的评论，即回复ID为2的评论',1);
SET @comment_path=concat( (SELECT comment_path FROM comments WHERE comment_id = 2 ) , concat(last_insert_id() , '/' )) ;
UPDATE comments 
SET comment_path = @comment_path
WHERE comment_id = last_insert_id();


INSERT INTO comments(comment_text,user_id) VALUES('CHAO:评论TONY发起的第一条评论',3);
SET @comment_path=concat( (SELECT comment_path FROM comments WHERE comment_id = 1 ) , concat(last_insert_id() , '/' )) ;
UPDATE comments 
SET comment_path = @comment_path
WHERE comment_id = last_insert_id();

然后我们看看插入数据后的数据库comments表内容：

我们可以看见路径之间的关系，第一条评论是评论数的根节点，然后通过路径字符串的方式把路径存储在表中的字段当中，例如ID:3的评论是回复了ID:2的评论，而ID:2的评论是回复了ID:1，而ID:4回复的是评论ID:1的评论。这样是最简单的层次关系描述，在查询过程当中也非常容易操作，例如我想获得评论ID:1的所有评论：

SELECT * FROM comments WHERE comment_path LIKE '1/%';

例如我想获得ID:3评论的所有父节点：
在程序栈当中使用split函数对coment_path进行转换成数组，然后通过IN语句获得所有的父节点

SELECT * FROM comments WHERE comment_id in (1,2,3);

in中的ID通过程序或者自定义函数截获。
通过这种做法我们如果想删除一条评论，然后将他的子节点评论一同删除变得异常简单：

DELETE FROM comments WHERE comment_path LIKE '%/2/%';

这种删除可能会出现错误，错误原因可能是因为开启了安全更新模式。

但是这种的树处理所出现的问题显然易见，因为我们是通过一个VARCHAR字段去存储这个数路径的，所以这个数节点数量存储，存在相当大的限制。其限制主要是在于字段的长度以及存储的节点ID长度有关。如果我们希望不受到限制，我们可以使用闭包表去实现，但是相对的我们就会提高了整个功能的SQL查询难度。

闭包表
首先我们考虑的是闭包表的表结构应该如何去修改，同时我们会按照之前路径枚举的评论数据模式去演示其原理（评论关系图，字丑将就看）：
评论数据关系

以下是闭包表的评论表结构，这里我们需要两张表：

CREATE TABLE comments(comment_id INT PRIMARY KEY AUTO_INCREMENT
            ,comment_text VARCHAR(300),user_id INT
            ,FOREIGN KEY(user_id) REFERENCES users(user_id));

CREATE TABLE comment_tree_paths(ancestor INT  NOT NULL
                ,descendant INT  NOT NULL
                ,PRIMARY KEY(ancestor,descendant)
                ,FOREIGN KEY(ancestor) REFERENCES comments(comment_id)
                ,FOREIGN KEY(descendant) REFERENCES comments(comment_id));

可以看到我们多出了一张名为comment_tree_paths 的表，里面我们有两个主要的字段分别是 ancestor(祖先) 和 descendant (后代)。那我们应该如何评论路径进入这张表呢？看下图，虽然比较丑但是基本上演示了表中的数据关系按照：
comment_tree_paths数据关系

按照这种结构我们先插入评论数据：

INSERT INTO comments(comment_text,user_id) VALUES('TONY:发起的第一条评论！',1);
SET @NEW_COMMENT_ID = last_insert_id();
INSERT INTO comment_tree_paths(ancestor,descendant) VALUES(@NEW_COMMENT_ID,@NEW_COMMENT_ID);


INSERT INTO comments(comment_text,user_id) VALUES('YAN: 回复TONY发起的第一条评论',2);
SET @NEW_COMMENT_ID = last_insert_id();
INSERT INTO comment_tree_paths(ancestor,descendant) 
SELECT t.ancestor,@NEW_COMMENT_ID FROM comment_tree_paths as t WHERE t.descendant = 1
UNION ALL SELECT @NEW_COMMENT_ID,@NEW_COMMENT_ID;


INSERT INTO comments(comment_text,user_id) VALUES('TONY:回复YAN的评论，即回复ID为2的评论',1);
SET @NEW_COMMENT_ID = last_insert_id();
INSERT INTO comment_tree_paths(ancestor,descendant) 
SELECT t.ancestor,@NEW_COMMENT_ID FROM comment_tree_paths as t WHERE t.descendant = 2
UNION ALL SELECT @NEW_COMMENT_ID,@NEW_COMMENT_ID;


INSERT INTO comments(comment_text,user_id) VALUES('CHAO:评论TONY发起的第一条评论',3);
SET @NEW_COMMENT_ID = last_insert_id();
INSERT INTO comment_tree_paths(ancestor,descendant) 
SELECT t.ancestor,@NEW_COMMENT_ID FROM comment_tree_paths as t WHERE t.descendant = 1
UNION ALL SELECT @NEW_COMMENT_ID,@NEW_COMMENT_ID;

其实也没有想象中复杂，关键是要把结构关系搞懂，目前还没有明白不要紧，在查询的时候就明白了，下面是插入后的comment_treee_paths表的内容：

如果我想查询获得评论ID:2之后的回复评论：

SELECT * FROM  comments c WHERE c.comment_id IN (SELECT c.descendant FROM comment_tree_paths c where c.ancestor = 2);
#或者
SELECT c.* FROM comments c LEFT JOIN comment_tree_paths p on c.comment_id = p.descendant WHERE p.ancestor = 2;

事实上我们也能够非常好地获得评论树的上游节点（获得评论ID:3的所有上游节点）：

SELECT * FROM comments c WHERE c.comment_id IN (SELECT p.ancestor FROM comment_tree_paths p WHERE p.descendant = 3 );
#或者
SELECT c.* FROM comments c LEFT JOIN comment_tree_paths p on c.comment_id = p.ancestor WHERE p.descendant = 3;

如果我们想删除子节点，并且将旗下的子节点一同删除：

DELETE FROM comment_tree_paths WHERE descendant IN (SELECT descendant FROM comment_tree_paths WHERE ancestor = 2);

但是需要注意的是，如果我们删除了paths中的关联记录就会失去其关联数据，导致无法删除comments表中的数据，所以我们需要在删除paths的关联数据之前先保存关联信息到程序当中，然后在删除paths中的关联信息。

SELECT descendant FROM comment_tree_paths WHERE ancestor = 2;

然后我们会发现一个问题就是我们无法去定位我们的直系上游节点或者下游节点，我们可以采用以下的优化手段，添加一个字段存储层级关系：

ALTER TABLE comment_tree_paths ADD path_length INT;

然而插入的时候也需要进行修改：

INSERT INTO comments(comment_text,user_id) VALUES('TONY:发起的第一条评论！',1);
SET @NEW_COMMENT_ID = last_insert_id();
INSERT INTO comment_tree_paths(ancestor,descendant,path_length) VALUES(@NEW_COMMENT_ID,@NEW_COMMENT_ID,0);


INSERT INTO comments(comment_text,user_id) VALUES('YAN: 回复TONY发起的第一条评论',2);
SET @NEW_COMMENT_ID = last_insert_id();
SET @LENGTH_COUNT = 0;
INSERT INTO comment_tree_paths(ancestor,descendant,path_length) 
SELECT * FROM (SELECT t.ancestor,@NEW_COMMENT_ID,@LENGTH_COUNT:=@LENGTH_COUNT+1
FROM comment_tree_paths as t WHERE t.descendant = 1 order by t.path_length asc) as temp_sort_table
UNION ALL SELECT @NEW_COMMENT_ID,@NEW_COMMENT_ID,0 ;


INSERT INTO comments(comment_text,user_id) VALUES('TONY:回复YAN的评论，即回复ID为2的评论',1);
SET @NEW_COMMENT_ID = last_insert_id();
SET @LENGTH_COUNT = 0;
INSERT INTO comment_tree_paths(ancestor,descendant,path_length) 
SELECT * FROM (SELECT t.ancestor,@NEW_COMMENT_ID,@LENGTH_COUNT:=@LENGTH_COUNT+1
FROM comment_tree_paths as t WHERE t.descendant = 2 order by t.path_length asc) as temp_sort_table
UNION ALL SELECT @NEW_COMMENT_ID,@NEW_COMMENT_ID,0 ;


INSERT INTO comments(comment_text,user_id) VALUES('CHAO:评论TONY发起的第一条评论',3);
SET @NEW_COMMENT_ID = last_insert_id();
SET @LENGTH_COUNT = 0;
INSERT INTO comment_tree_paths(ancestor,descendant,path_length) 
SELECT * FROM (SELECT t.ancestor,@NEW_COMMENT_ID,@LENGTH_COUNT:=@LENGTH_COUNT+1
FROM comment_tree_paths as t WHERE t.descendant = 1 order by t.path_length asc) as temp_sort_table
UNION ALL SELECT @NEW_COMMENT_ID,@NEW_COMMENT_ID,0 ;

当然这样是比较复杂的，需要注意的是，由于UNION ALL 语句对order by 的某些限制所以我在外面再套了一层SELECT 否则排序会出现问题，导致你的LENGTH_COUNT计算的顺序出错，以下是插入结果：
comment_tree_paths表数据

查询还是一样的不过我们可以使用order by 对path_length 进行层级排序，从而可以获得直接的上游节点。

SELECT c.* FROM comments  c JOIN comment_tree_paths p on p.ancestor = c.comment_id WHERE p.descendant = 3 order by p.path_length;

获得上游节点的结果

我们可以使用通过这种方式获得其直接子节点

SELECT c.*,p.path_length FROM comments  c JOIN comment_tree_paths p on p.descendant = c.comment_id WHERE p.ancestor = 1 order by p.path_length;

获得下游节点的结果
但是如果出现分叉我们就需要在进一步的语句进行数的分支关联了，这里就不再解说了。

总结：其实无论邻接表也好，路径枚举或者是闭表表也好。我们归根到底也是需要选择适合我们业务的方法。如果我们限制了数的节点数，我们当然可以使用路径枚举，这样方便。但是如果我们不限制而且需要获得上游或者下游的间接或者直接的节点，我们就应该用闭包表去实现。如果我们根本没有必要去知道所有的间接上游或者下游节点，我们可以使用邻接表。其实SQL的设计是一样非常需要技巧的东西，在我们的工作当中不停的会出现这种反模式，其实这个方式我是在SQL Antipattern一书中获得的技巧，如果有对SQL设计有兴趣的同学也可以读读，相信获益匪浅。

起个昵称总重复

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
SQL Antipattern 树之反模式(评论回复数据库表设计)

我们通常在SQL中实现数，都使用了邻接表。但是事实上邻接表却有相当多的不足，相信属性SQL的开发者也应该清楚了。例如在设置评论的表我们如如下设计：CREATE TABLE comments(comment_id INT PRIMARY KEY AUTO_INCREMENT, comment_text VARCHAR(300...
复制链接

扫一扫