多级树形结构和sql查询实现

https://www.jianshu.com/p/f99665266bb1

文/michaelgbw

多级树形结构是一个应用很广泛的数据结构，程序设计过程中，我们常常用树形结构来表征某些数据的关联关系，如企业上下级部门、栏目结构、商品分类等等，通常而言，这些树状结构需要借助于数据库完成持久化。然而目前的各种基于关系的数据库，都是以二维表的形式记录存储数据信息，因此是不能直接将树形结构存入DBMS，设计合适的Schema及其对应的CRUD算法是实现关系型数据库中存储树形结构的关键。

我想在生活中树形结构应用广泛，我们先看几个实际的例子。

 

多级评论

 

多层隶属关系

那么问题来了，我们应该怎么设计和实现呢，首先显示DB端的结构，当然可以采用非关系数据库（nosql）如mogodb，这里我们还是只讨论传统意义上的关系型数据库mysql为例，由于关系型数据库的特点。一张表完全可以实现，在三范式的基础上多表采用关联即可。

CREATE TABLE nodelist(  
 id INT PRIMARY KEY,      
 nodecontent VARCHAR(100), 
 pid INT
); 

我们插入几个测试数据进去

 

 

接下来难点来了，我们怎么一次取出和指定节点相关的所有节点呢？递归？循环？原理还是可以理解，但我们这次可是在用sql来进行操作。

开始百度，发现有何东西很神奇Start with...Connect By这是什么，好像从来没见过，再一看，这个是人家Oracle的支持的，mysql并不支持啊~

好吧，自己写吧，

delimiter /
DROP FUNCTION IF EXISTS `test`.`getChild` /
CREATE FUNCTION `getChild`(rootId INT) 
RETURNS VARCHAR(1000)
BEGIN 
DECLARE ptemp varchar(1000);
DECLARE ctemp varchar(1000);
       SET ptemp = '#';
       SET ctemp =cast(rootId as CHAR);
       WHILE ctemp is not null DO
             SET ptemp = concat(ptemp,',',ctemp);
            SELECT group_concat(id) INTO ctemp FROM nodelist   
            WHERE FIND_IN_SET(pid,ctemp)>0; 
       END WHILE;  
       RETURN ptemp;  
     END 

这段sql我们稍作解释下：

首先是DECLARE（定义一个临时变量）

• DECLARE语句必须用在BEGIN…END语句块中，并且必须出现在DEGIN…END语句块的最前面，即出现在其他语句之前。
• DECLARE定义的变量的作用范围仅限于DECLARE语句所在的BEGIN…END块内及嵌套在该块内的其他BEGIN…END块。即是一个形参。

FIND_IN_SET函数
函数定义FIND_IN_SET(str,strlist)，其实和in差不多，但这里不能用in，因为要靠这个where来判断循环结束的条件呢。

cast
.CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标类型。

说了一堆就是sql自定义编程,这个领域经常被我们忽视，因为我们总会把逻辑计算层放在PHP，JAVA等“第三方语言上”,

SELECT * FROM nodelist WHERE FIND_IN_SET(id, getChild(3))

还有一点及时如果后面的查询条件是常量（在mysql中不需要运算获得），则可以直接用IN， 否则要用find_in_set()函数。

 

查询结果

这就轻松的从数据库中取出来了，其实就是把实现的逻辑放在了sql层，这样查询效率自然不用说了（就进行了一次mysql IO操作）之后根据需求显示，渲染即可，就这样~

作者：michaelgbw
链接：https://www.jianshu.com/p/f99665266bb1
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。