mysql无限极多级分销分类评论的四种设计方案

我们在编程开发的过程中可能会遇到这样的需求，多级分类，多级留言评价，多级分销系统，这些系统需求很简单，但是存在无限多的父子关系，是一个典型的迭代关系设计。

对于这个问题，以下给出几个解决方案供大家参考。

一、邻接表设计

我们以留言评价这样的需求为例来设计一个数据结构，在mysql中执行以下sql语句：

CREATE TABLE `Comment` (
`id`  int NOT NULL ,
`pid`  int NULL ,
`words`  varchar(255) NULL ,
PRIMARY KEY (`id`),
CONSTRAINT `pidkey` FOREIGN KEY (`pid`) REFERENCES `Comment` (`id`) 
);

1通过上面的sql我们可以发现，pid这个字段指向了本表的id字段，类似于链表的结构，
我们填充一些数据

insert into Comment(id,pid,words) values(1,null,"hello world") ;
insert into Comment(id,pid,words) values(2,1,"hello ") ;
insert into Comment(id,pid,words) values(3,1,"are you ok ") ;
insert into Comment(id,pid,words) values(4,null,"this is good") ;
insert into Comment(id,pid,words) values(5,2,"what is your name?") ;

好了，现在的数据结构是这样的

pid为null的表示顶级评论

那么怎么一次性迭代查询出多级的数据呢，比如查询父级pid=1的所有子树数据，代码如下：

WITH recursive comment_cte as(
select a.id,a.pid,a.words, 1 as tlevel FROM comment as a WHERE a.pid = 1
UNION ALL
SELECT c.id,c.pid,c.words, ce.tlevel+1 as tlevel FROM comment AS c
INNER JOIN comment_cte AS ce 
ON c.pid = ce.id
)

SELECT * FROM comment_cte

查询结果如下：

tlevel为第几层的数据

修改删除也很方便。

二、路径枚举

路径枚举的设计是指通过将所有祖先的信息联合成一个字符串，并保存为每个节点的一个属性。

路径枚举是一个由连续的直接层级关系组成的完整路径。如"/home/account/login",其中home是account的直接父亲，这也就意味着home是login的祖先。

还是有刚才评论的例子，我们用路径枚举的方式来代替邻接表的设计：

CREATE TABLE `Comment2` (
 `id`  int NOT NULL ,
 `path`   varchar(255) NULL ,
 `words`  varchar(255) NULL ,
  PRIMARY KEY (`id`)
);

先插入一些数据

insert into Comment2(id,path,words) values(1,"1","hello world") ;
insert into Comment2(id,path,words) values(2,"1/2","hello ") ;
insert into Comment2(id,path,words) values(3,"1/3","hello ") ;
insert into Comment2(id,path,words) values(4,"1/2/4","hi ") ;

现在表中数据是这样的

假设我们要查询某个节点的全部后代，假设为1的后代：

SELECT * FROM Comment2 AS c WHERE c.path LIKE '1/%';

查询结果如下：

路径枚举的优点：

一旦我们可以很简单地获取一个子树或者从子孙节点到祖先节点的路径，就可以很简单地实现更多查询，比如计算一个字数所有节点的数量(COUNT聚合函数)。

插入一个节点也可以像和使用邻接表一样地简单。

可以插入一个叶子节点而不用修改任何其他的行。你所需要做的只是复制一份要插入节点的逻辑上的父亲节点路径，并将这个新节点的Id追加到路径末尾就可以了。如果这个Id是插入时由数据库生成的，你可能需要先插入这条记录，然后获取这条记录的Id，并更新它的路径。

路径枚举的缺点:

1、数据库不能确保路径的格式总是正确或者路径中的节点确实存在(中间节点被删除的情况，没外键约束)。

2、要依赖高级程序来维护路径中的字符串，并且验证字符串的正确性的开销很大。

3、VARCHAR的长度很难确定。无论VA...

点击查看剩余70%