DROP PROCEDURE IF EXISTS query_node_deep;
CREATE PROCEDURE query_node_deep(IN rootId int(11))
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmptable(
sno INT PRIMARY KEY auto_increment,
nid INT(11),
deep int(11)
);
DELETE FROM tmptable;
CALL create_node_deep(rootId, 0, 1);
select tmptable.*,api_node.* from tmptable,api_node where tmptable.nid=api_node.id order by tmptable.sno;
END;
DROP PROCEDURE IF EXISTS create_node_deep;
CREATE PROCEDURE create_node_deep(IN rootId int(11),IN nDepth INT, IN max_n INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b VARCHAR(20);
DECLARE cur1 CURSOR FOR SELECT id FROM api_node where parent_id=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
IF (max_n > 0) THEN
insert into tmptable values (null,rootId,nDepth);
SET @@max_sp_recursion_depth = 7;
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL create_node_deep(b,nDepth+1, max_n-1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END IF;
END;
call query_node_deep(17);
MySQL树形结构查询
最新推荐文章于 2024-07-06 09:22:27 发布