MySQL版的树型查询

NND，捣鼓了好长时间才捣鼓出来的MySQL树型查询。效率可能有点低。查询时间比较长。


第一种：
特点：效率有点慢

DROP PROCEDURE P_QUERY_EQUIPMENT_LIST;

DROP PROCEDURE P_GET_EQUIPMENT_CHILDER;

create PROCEDURE P_QUERY_EQUIPMENT_LIST(IN rootId INT)
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tempList(sid int);
CALL P_GET_EQUIPMENT_CHILDER(rootId);
select sid from tempList where sid in (select id from equipment where node_level = 5);
drop temporary table if exists tempList;
END


CREATE PROCEDURE P_GET_EQUIPMENT_CHILDER(IN rootId INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE curl CURSOR FOR SELECT id FROM equipment where parent_id = rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
INSERT into tempList VALUES(rootId);
SET @@max_sp_recursion_depth = 10;
OPEN curl;
FETCH curl INTO b;
WHILE done = 0 DO
CALL P_GET_EQUIPMENT_CHILDER(b);
FETCH curl INTO b;
END WHILE;
CLOSE curl;

END;


第二种：
特点:当节点多时，会有遗漏，导致查询不全面。可能是FIND_IN_SET()函数有条件限制。

CREATE FUNCTION F_GET_EQUIPMENT_CHILD_LIST(rootId INT)
RETURNS varchar(21845)
BEGIN
DECLARE sChildList VARCHAR(21845);
DECLARE sChildTemp VARCHAR(21845);
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 FROM equipment where FIND_IN_SET(parent_id,sChildTemp)>0;
END WHILE;
RETURN sChildList;
END;