讨论mysql两种树状结构递归查询的优劣

由于 mysql 本身并没有递归方法，并不像oracle那么方便，所以出现这个需求的时候，很是头疼了一久。

 

网上查到的有两种办法：

一、 使用 @变量 进行迭代

二、 使用 存储过程，进行遍历。

 

//*************************************************************************************************************************************

先说第一种办法吧，第一种办法，乍一看很酷啊，一般像我这种初次接触 @变量的人，一下子可能都看不懂。

但是怎么说呢？ 看似很美好。先看一下本体吧：

初始条件：sc_pubcompany 保存有每个机构的详细信息及上级机构代码

目标：       获取当前机构及所有上级机构

            SELECT DISTINCT
                @pv AS _deptCode,
                (
                    SELECT
                        @pv := upperDeptCode
                    FROM
                        sc_pubcompany
                    WHERE
                        deptCode = _deptCode
                )
            FROM
                sc_pubcompany b,
                (SELECT @pv := '2000000115') initialisation

对于这段代码，我是这样理解的：

1、  from 后的 @pv 作为初始化参数，因为要知道 @变量，在每次会话时，不会被自动重置，也就是说，如果你在一个session中多次使用 @变量，那么后几次的执行起点，是上一次的执行结果。

2、  from后的 @pv 除了初始化的作用外，另一个作用是在你原始数据表后，附加一列，初始机构

3、 迭代部分发生在查询部分，需要注意的是，向上迭代， 迭代要写在 select 后， 如果是向下迭代的话，迭代就要放在 where 之后了。 这个为什么，我也没想明白。

4、 大家可以用 explain 看一下，这个SQL虽然外表很酷，但是实际上，执行它需要巨大的开销，主要初始化的时候，@pv这个，会导致生成一张临时表，之后的所有查询都是基于临时表的。

5、 1000条数据，查询所有机构的上级，总共耗时 78 s。

6、  供有兴趣的同学看一下，递归下级的查询：

SELECT 

  deptCode,

  deptCName,

  upperDeptCode 

FROM

temp,

  (SELECT 

    @pv := '2000000000') initialisation 

WHERE (

    FIND_IN_SET(upperDeptCode, @pv) > 0 

    AND @pv := CONCAT(@pv, ',', deptCode)

  ) 

 

//*************************************************************************************************************************************

第二个办法，就是存过啦： 自己写的，很糙，但效果很好

BEGIN

DECLARE recursiveDept VARCHAR(10);

set recursiveDept = in_deptCode;

WHILE recursiveDept != '2000000000' DO

SELECT in_deptCode as initDept,
       upperDeptCode as recursiveDept,
       sysdate() as updatedDate
 from sc_pubcompany where deptCode = recursiveDept;

select upperDeptCode into recursiveDept from sc_pubcompany where deptCode = recursiveDept;

END WHILE;
END

结果是： 1000条递归查询，只花了 7 s。

 

建议使用存过或者函数， 不建议使用 @迭代变量。