mysql树查询、递归查询

关键词：mysql树查询，mysql递归查询

转自：http://www.cnblogs.com/c-h-y/p/9420726.html

之前一直用的是Oracle，对于树形查询可以使用start with ... connect by 

select * from menu start with id='130000' connect by id = prior parent_id;

 没错，这是Oracle所支持的

现在公司用的是mysql，对于这种查询方式只能通过sql语句实现了

语言都是相通的，何况sql呢

mysql随没有自带的语法支持，不过可以通过创建函数来实现递归查询。

  

直接上sql语句

测试数据

create table `nodelist` (
    `id` int (11),
    `nodecontent` varchar (300),
    `pid` int (11)
); 
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('1','a',NULL);
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('2','b','1');
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('3','c','1');
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('4','d','2');
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('5','e','3');
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('6','f','3');
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('7','g','5');
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('8','h','7');
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('9','i','8');
insert into `nodelist` (`id`, `nodecontent`, `pid`) values('10','j','8');

 

递归函数

（1）根据父节点求出所有儿子节点

--根据父节点求出所有儿子节点
delimiter $$
DROP FUNCTION IF EXISTS `getChild`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `getChild`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8
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;  
END$$ 
DELIMITER ;

如果报错：
　　错误：This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de
　　解决：set global log_bin_trust_function_creators=TRUE;
这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句

其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。


在MySQL中创建函数时出现这种错误的解决方法：
set global log_bin_trust_function_creators=TRUE;

-- 测试使用

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

（2）根据该节点获取所有父节点

-- 根据该节点获取所有父节点
delimiter $$
DROP FUNCTION IF EXISTS `getParentId`$$
CREATE DEFINER=`test1`@`localhost` FUNCTION `getParentId`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8
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(pid) INTO ctemp FROM nodelist   
                WHERE FIND_IN_SET(id,ctemp)>0; 
               END WHILE;  
                            return ptemp;
END$$ 
DELIMITER ;

-- 测试实用
SELECT * FROM nodelist WHERE FIND_IN_SET(id, getParentId(3));

　　　　

 

（3）获取该节点相关的所有父节点子节点

-- 获取该节点相关的所有父节点子节点
delimiter $$
DROP FUNCTION IF EXISTS `getAllNode`$$
CREATE DEFINER=`test1`@`localhost` FUNCTION `getAllNode`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8
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;  
                    

               SET ctemp =CAST(rootId AS CHAR);
               WHILE ctemp IS NOT NULL DO
                 SET ptemp = CONCAT(ptemp,',',ctemp);
                SELECT GROUP_CONCAT(pid) INTO ctemp FROM nodelist   
                WHERE FIND_IN_SET(id,ctemp)>0; 
               END WHILE;  
                            return ptemp;

END$$ 
DELIMITER ;

-- 测试验证
SELECT * FROM nodelist WHERE FIND_IN_SET(id, getAllNode(3))

　　　　

自连接查询解决简单固定层级

上面难度相对比较大，再补充一个简单的自连接查询

SELECT t1.id,t1.nodecontent,t2.nodecontent parentnodecontent 
FROM nodelist t1
LEFT JOIN nodelist t2
ON t1.pid = t2.id

 

　　

可以了。

　　其他相关参考：

　　　　

 

顺序排列的数据变量方法递归

 本例根据上述代码实现（但仅限非常正序的情况，否则会出问题）

--顺序查找所有子节点
select * from (
select *,if(find_in_set(pid,@p)>0,@p:=concat(@p,',',id),0) as childId from 
(select * from nodelist order by id) t1 ,(select @p:=3) t2
) t
where  t.childId!=0;
　　
--倒序查找所有父节点
select * from (
select *,if(find_in_set(id,@p1)>0,@p1:=concat(@p1,',',pid),0) as childId,@p1 from 
(select * from nodelist order by id desc ) t1 ,(select @p1:=3) t2
) t
where t.childId!=0;
　　

　　

 

 

　　 

 

 

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

里面用到的内置函数 https://baijiahao.baidu.com/s?id=1595349117525189591&wfr=spider&for=pc

你只要能想到的，都有对应的解决方式，幸运的是你该踩得一些坑别人实现给你填好了。