MySQL 递归查找所有子节点

mysql 没有层级查询方法 而 oracle通过 connect by start with 语法可以实现层级查询

使用递归

SQL 语句:

select id from (
              select t1.id,
              if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild
              from (
                   select id,parent_id from 表名 t order by parent_id, id
                  ) t1,
                  (select @pids := 父节点id) t2
             ) t3 where ischild != 0

注意:

此方法对主键的排序有要求,不能使用 UUID 作为主键。

使用变量循环赋值方式

SELECT
id,
pid
FROM (
    SELECT
     id,
     pid
     @le:= IF (pid = -1 ,0,IF( LOCATE( CONCAT('|',pid,':'),@pathlevel) > 0,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',pid,':'),-1),'|',1) +1,@le+1) ) levels,
     @pathlevel:= CONCAT(@pathlevel,'|',id,':', @le ,'|') pathlevel,
     @pathnodes:= IF( pid =-1,',root', CONCAT_WS(',',IF( LOCATE( CONCAT('|',pid,':'),@pathall) > 0 ,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',pid,':'),-1),'|',1),@pathnodes ) ,pid ) )paths,
     @pathall:=CONCAT(@pathall,'|',id,':', @pathnodes ,'|') pathall 
    FROM 表名, 
    (
     SELECT 
     @le:=0,
     @pathlevel:='', 
     @pathall:='',
     @pathnodes:=''
    )  vv
    ORDER BY pid,id
) src
WHERE instr(paths,'父节点id')>0
ORDER BY pid

使用存储过程

  • group_concat 不适用数据较多的时候
  • 要彻底修改,需在MySQL配置文件中加上group_concat_max_len=-1 (-1为最大值或填入你要的最大长度)
  • 并重启MySQL服务,在客户端查询修改好的值:
  • show variables like "group_concat_max_len";

1、根据传入id查找所有子节点:

CREATE FUNCTION `getChildList`(rootId INT)
RETURNS varchar(1000) 

BEGIN
    DECLARE sTemp VARCHAR(1000);
    DECLARE sTempChd VARCHAR(1000);

    SET sTemp = '$';
    SET sTempChd =cast(rootId as CHAR);

    WHILE sTempChd is not null DO
        SET sTemp = concat(sTemp,',',sTempChd);
        SELECT group_concat(id) INTO sTempChd FROM  treeNodes where FIND_IN_SET(pid,sTempChd)>0;
    END WHILE;
    
RETURN sTemp; 
END

调用方式:select * from tree where FIND_IN_SET(id,getChildList(7));

2、根据传入id查找所有父节点:

CREATE FUNCTION `getParList`(rootId INT)
RETURNS varchar(1000) 

BEGIN
    DECLARE sTemp VARCHAR(1000);
    DECLARE sTempPar VARCHAR(1000); 
    
    SET sTemp = ''; 
    SET sTempPar =rootId; 

    #循环递归
    WHILE sTempPar is not null DO 
        #判断是否是第一个,不加的话第一个会为空
        IF sTemp != '' THEN
            SET sTemp = concat(sTemp,',',sTempPar);
        ELSE
            SET sTemp = sTempPar;
        END IF;
        SET sTemp = concat(sTemp,',',sTempPar); 
        SELECT group_concat(pid) INTO sTempPar FROM treenodes where pid<>id and FIND_IN_SET(id,sTempPar)>0; 
    END WHILE; 

RETURN sTemp; 
END

调用方式:select * from treenodes where FIND_IN_SET(id,getParList(15));


参考

添加新评论

还没有评论哟 ~