Mysql和Oracle递归查询实现记录

最近遇到一个需求是后台管理系统要求用户管理成树形展示,并且另外一个事项管理里面要求按人员等级查看，比如管理员可以看到所有人员录入的事项，管理员下一级的人员可以看到其子类以及他自己录入的事项，这就要求用递归查询来实现，特此记录。
因为ORACLE对递归可以直接用CONNECT BY PRIOR 来实现很简单，但是mysql就没有此类函数，需要自己手动编写函数方法来实现。

一、实现子递归查询

1.单个子递归查询的实现

oracle中使用示例代码：

SELECT
   U.ID
FROM
   SYS_USER U CONNECT BY PRIOR U.ID = U.PARENTID
   START WITH U.ID = #{id}

mysql中使用示例代码：

SELECT
   ID
FROM 
    SYS_USER U,
    (SELECT @DATAS := getChildId_SYS_USER  (#{id})) x 
WHERE find_in_set (ID, @DATAS)

mysql函数方法的实现：

-- 在navcat中的查询下执行以下命令

drop function if exists getChildId_SYS_USER  ;

DELIMITER //
create function getChildId_SYS_USER  (rootId varchar(20)) 
returns varchar (1000)
BEGIN 
    DECLARE pTemp VARCHAR(1000);            
    DECLARE cTemp VARCHAR(1000);      -- 节点ID(临时变量)

    SET pTemp = '$';  
    SET cTemp =cast(rootId as CHAR);  -- 把rootId 强制转换为字符。

    WHILE cTemp is not null DO  
       SET pTemp = concat(pTemp,',',cTemp);  -- 把所有节点连接成字符串。
       SELECT group_concat(id) INTO cTemp FROM SYS_USER --需要查询的表名
       WHERE FIND_IN_SET(PARENTID,cTemp)>0; --PARENTID 父类字段
    END WHILE;  
    RETURN pTemp;  
END //
DELIMITER ;

2.多个子递归查询的实现

oracle中使用示例代码：

SELECT
   U.ID
FROM
   SYS_USER U CONNECT BY PRIOR U.ID = U.PARENTID
   START WITH U.ID in( #{ids} ) 
					-- 其中ids比如是 ('1', '2') 这种

mysql中使用示例代码：

SELECT
   *
FROM
   SYS_USER U ,
   (select @DATAK := 
       (   
           SELECT
               -- 使用该方法把所有的ID用逗号分割连接成一个字符串
               group_concat(U.ID)
           FROM
               SYS_USER U
           WHERE
          -- NAME LIKE #{orgname} --此处是可以传递进来的参数
          ID LIKE #{ids}
           -- 括号里面查询出来的结果类似与 '1,2' 这种
       )
   ) z,
   (SELECT @DATAS := getChildId_sys_user_tree_more  (@DATAK) ) x 
WHERE find_in_set (U.ID, @DATAS)

-- 注意这里select @DATAK 与 SELECT @DATAS 顺序不能乱，否则会出现数据错误

mysql函数方法的实现：

drop function if exists getChildId_sys_user_tree_more  ;

DELIMITER //
create function getChildId_sys_user_tree_more(nodes varchar(1000)) 
returns varchar (5000)

BEGIN 
       DECLARE pTemp VARCHAR(1000);             
       DECLARE cTemp VARCHAR(5000);  
      
      SET @array_content= nodes; 
       SET @i=1;
       SET @count=CHAR_LENGTH(@array_content) -CHAR_LENGTH(REPLACE(@array_content,',','')) + 1;
      SET pTemp = '$';  

       WHILE @i <= @count DO
          SET cTemp = SUBSTRING_INDEX(SUBSTRING_INDEX(@array_content,',',@i),',',-1);
          WHILE cTemp is not null DO  
            SET pTemp = concat(pTemp,',',cTemp);  
            SELECT group_concat(id) INTO cTemp FROM sys_user   
            WHERE FIND_IN_SET(parentId,cTemp) > 0;
          END WHILE;  
          SET @i = @i + 1; 
      END WHILE; 
      
       RETURN pTemp;  

END //
DELIMITER ;

二、实现父递归查询

1.单个父递归查询的实现

oracle中使用示例代码：

SELECT
   U.ID
FROM
   SYS_USER U CONNECT BY PRIOR U.PARENTID= U.ID
   START WITH U.ID = #{id}

mysql中使用示例代码：

SELECT
   ID
FROM 
   SYS_USER U,
   (SELECT @LIST := getParentId_sys_user_tree(#{id})) x 
WHERE find_in_set (ID, @LIST)

mysql方法的实现：

-- 在navcat中的查询下执行以下命令

drop function if exists getParentId_sys_user_tree;

DELIMITER //
create function getParentId_sys_user_tree(rootId varchar(20)) 
returns varchar (1000)
BEGIN 
   DECLARE fid varchar(100) default ''; 
   DECLARE str varchar(1000) default rootId; -- 节点ID(临时变量)
 
    WHILE rootId is not null  do 
        SET fid =(SELECT parentId FROM sys_user WHERE id = rootId); 
        IF fid is not null THEN 
            SET str = concat(str, ',', fid); 
            SET rootId = fid; 
        ELSE 
            SET rootId = fid; 
        END IF; 
    END WHILE; 
   return str;
END //
DELIMITER ;

2.多个父递归查询的实现

oracle中使用示例代码：

SELECT
   U.ID
FROM
   SYS_USER U CONNECT BY PRIOR U.PARENTID = U.ID
   START WITH U.ID in( #{ids} )   
					-- 其中ids比如是 ('1', '2') 这种

mysql中使用示例代码：

SELECT
   U.*
FROM
   SYS_USER U ,
   (select @DATAK := 
       (
           SELECT
               -- 使用该方法把所有的ID用逗号分割连接成一个字符串
               group_concat(U.ID)
           FROM
               SYS_USER U
           WHERE
           --NAME LIKE #{orgname}
            ID LIKE #{ids}
           -- 括号里面查询出来的结果类似与 '1,2' 这种
       )
   ) zz,
   (SELECT @DATAS := getParentId_sys_user_tree_more (@DATAK) ) x
WHERE find_in_set (U.ID, @DATAS) 

mysql方法的实现：

-- 在navcat中的查询下执行以下命令

drop function if exists getParentId_sys_user_tree_more ;

DELIMITER //
create function getParentId_sys_user_tree_more(nodes varchar(1000)) 
returns varchar (5000)

BEGIN
      DECLARE pTemp VARCHAR(1000);              
       DECLARE cTemp VARCHAR(5000);  
      DECLARE xTemp varchar(100); 

      SET @array_content= nodes; 
       SET @i=1;
       SET @count=CHAR_LENGTH(@array_content) -CHAR_LENGTH(REPLACE(@array_content,',','')) + 1;
      SET pTemp = nodes;  

       WHILE @i <= @count DO
          SET cTemp=SUBSTRING_INDEX(SUBSTRING_INDEX(@array_content,',',@i),',',-1);
          WHILE cTemp is not null DO  
            SET xTemp = (SELECT parentId FROM sys_user WHERE id = cTemp); 
            IF xTemp is not null THEN 
               SET pTemp = concat(pTemp, ',', xTemp); 
               set cTemp = xTemp;
            ELSE 
               set cTemp = xTemp;
            END IF;
          END WHILE;  
          SET @i = @i + 1; 
      END WHILE; 
       RETURN pTemp;  
END //

DELIMITER ;

参考: MYSQL递归树查询的实现