MySQL 查询树表某个根节点的子树

select * from dept where find_in_set(id,getDeptChildList(1))

getDeptChildList 是函数，内容如下(navicat)：
参数：rootId VARCHAR(1000)

BEGIN
DECLARE sChildList mediumtext;
DECLARE sChildTemp mediumtext;
SET sChildTemp =CAST(rootId AS CHAR);
WHILE sChildTemp IS NOT NULL DO
IF (sChildList IS NOT NULL) THEN
SET sChildList = CONCAT(sChildList,',',sChildTemp);
ELSE
SET sChildList = CONCAT(sChildTemp);
END IF;
SELECT GROUP_CONCAT(id) INTO sChildTemp FROMdeptWHERE FIND_IN_SET(parent_id,sChildTemp)>0 and state='T';
END WHILE;
RETURN sChildList;
END

由于 MySQL 函数是不能返回结果集的，所以这里写了个函数查找 id=1 根节点其下的子树 id 字符串(用，分割)，然后使用mysql的find_in_set函数进行查询。getDeptChildList函数直接运行的速度并不算慢，试运行几次，查询时间在50~60ms。

运行情况：
[SQL]select * from dept where FIND_IN_SET(id,getDeptChildList(1))
受影响的行: 0
时间: 18.690s

关键在于find_in_set函数，查询过慢—–总数据约700条，耗时15~18s。这函数其实就是in操作符，当in操作数据太多时，效率是十分低下的。于是这里我弃用了find_in_set这种方法，使用了临时表来查询子树。

临时表将在你连接MySQL期间存在。当你断开时，MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。重写了一个mysql存储过程来获取子树并储存在临时表：getDListTable
（注：GROUP_CONCAT函数一开始坑了一下，“在MySQL配置文件(my.ini)中默认无该配置项，使用默认值时，值为1024，可在客户端修改/修改方法”，因为我的表使用uuid，字段很长，1024不够用。）

参数是：rootId VARCHAR(1000)

BEGIN   
      DECLARE sChildTemp MEDIUMTEXT;
      /*存在同名临时表就删除*/
      DROP TEMPORARY TABLE IF EXISTS table_tmp;
      /*创建临时表*/
      create temporary table table_tmp (id varchar(100),name varchar(100),parent_id varchar(100),layer varchar(10),type varchar(10),state varchar(2));
      SET sChildTemp = rootId ;
      /*存在子树则继续循环*/
      WHILE sChildTemp IS NOT NULL DO
    /*将子树记录插入临时表*/
        insert into table_tmp (id,name,parent_id,layer,type,state) select d.id,d.name,d.parent_id,d.layer,d.type,d.state from dept d WHERE FIND_IN_SET(d.id,sChildTemp)>0 and d.state!='F'; 
    /*查询子id*/           
        SELECT GROUP_CONCAT(id) INTO sChildTemp FROM `dept` WHERE FIND_IN_SET(parent_id,sChildTemp)>0 and state!='F';

      END WHILE;
END

sql查询语句则是：

call getDListTable(1);
select * from table_tmp;

查询时间为100ms~180ms，可见查询是大大被优化的。
运行情况：查询时间 0.172s
[SQL]CALL getDListTable(1);
受影响的行: 1
时间: 0.083s

[SQL]SELECT * from table_tmp
受影响的行: 0
时间: 0.089s
—————————————————————————————————
存储过程中的循环可替换成递归，我也测试过然而似乎（0.0，有可能递归的姿势不对，有待研究）查询速度比直接循环慢一些
pro_cre_childlist：参数是：rootId VARCHAR(1000)：

BEGIN
    DECLARE done INT DEFAULT 0;   
    DECLARE sChildTemp MEDIUMTEXT;   
    DECLARE cur1 CURSOR FOR SELECT id FROM dept WHERE parent_id=rootId and state!='F';   
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;   
    SET max_sp_recursion_depth=12;   
    insert into table_tmp(id, name,parent_id,layer,type,state) select d.id,d.name,d.parent_id,d.layer,d.type,d.state from dept d WHERE d.id=rootId;
      OPEN cur1;       
      FETCH cur1 INTO sChildTemp;   
      WHILE done=0 DO   
              CALL pro_cre_childlist(sChildTemp);   
              FETCH cur1 INTO sChildTemp;   
      END WHILE;   
      CLOSE cur1;   
END

sql 语句：

DROP TEMPORARY TABLE IF EXISTS table_tmp;

create temporary table table_tmp 
(id varchar(100),name varchar(100),parent_id varchar(100),layer varchar(10),type varchar(10),state varchar(2));

CALL pro_cre_childlist(1);

SELECT * from table_tmp;

运行情况： 查询总时间1.047s
[SQL]DROP TEMPORARY TABLE IF EXISTS table_tmp;
受影响的行: 0
时间: 0.043s

[SQL]create temporary table table_tmp (id varchar(100),name varchar(100),parent_id varchar(100),layer varchar(10),type varchar(10),state varchar(2));
受影响的行: 0
时间: 0.028s

[SQL]CALL pro_cre_childlist(1);
受影响的行: 0
时间: 0.887s

[SQL]SELECT * from table_tmp
受影响的行: 0
时间: 0.089s