《mysql递归调用获取树节点(子树)》由会员分享，可在线阅读，更多相关《mysql递归调用获取树节点(子树)（3页珍藏版）》请在金锄头文库上搜索。
1、创建树形数据存储表，及其数据：SET FOREIGN_KEY_CHECKS=0;- - Table structure for treenodes- -DROP TABLE IF EXISTS treenodes;CREATE TABLE treenodes (id int(11) NOT NULL,nodename varchar(20) DEFAULT NULL,pid int(11) DEFAULT NULL,PRIMARY KEY (id) ENGINE=InnoDB DEFAULT CHARSET=utf8;- - Records of treenodes- -INSERT INTO
2、 treenodes VALUES (1, A, 0);INSERT INTO treenodes VALUES (2, B, 1);INSERT INTO treenodes VALUES (3, C, 1);INSERT INTO treenodes VALUES (4, D, 2);INSERT INTO treenodes VALUES (5, E, 2);INSERT INTO treenodes VALUES (6, F, 3);INSERT INTO treenodes VALUES (7, G, 6);INSERT INTO treenodes VALUES (8, H, 0)
3、;INSERT INTO treenodes VALUES (9, I, 8);INSERT INTO treenodes VALUES (10, J, 8);INSERT INTO treenodes VALUES (11, K, 8);INSERT INTO treenodes VALUES (12, L, 9);INSERT INTO treenodes VALUES (13, M, 0);INSERT INTO treenodes VALUES (14, N, 12);INSERT INTO treenodes VALUES (15, O, 12);INSERT INTO treeno
4、des VALUES (16, P, 15);INSERT INTO treenodes VALUES (17, Q, 15);存储过程：查询参数节点下的所有子节点入口：CREATE DEFINER=rootlocalhost PROCEDURE showChildLst(IN rootId int)BEGIN#Routine body goes here.set max_sp_recursion_depth=225; CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst (sno int primary key auto_increment,id int,d
5、epth int);DELETE FROM tmpLst;CALL createChildLst(rootId,0);select tmpLst.*,treeNodes.* from tmpLst,treeNodes where tmpLst.id=treeNodes.id order by tmpLst.sno;END向临时表插入数据CREATE DEFINER=rootlocalhost PROCEDURE createChildLst(IN rootId int,IN nDepth int)BEGIN#Routine body goes here.DECLARE done INT DEF
6、AULT 0;DECLARE b INT;DECLARE cur1 CURSOR FOR SELECT id FROM treeNodes where pid=rootId;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;insert into tmpLst values (null,rootId,nDepth);OPEN cur1;FETCH cur1 INTO b;WHILE done=0 DOCALL createChildLst(b,nDepth+1);FETCH cur1 INTO b;END WHILE;CLOSE cur1;
7、END查询参数节点下的所有父节点入口相同，只是插入数据过程不同CREATE DEFINER=rootlocalhost PROCEDURE createChildLst(IN rootId int,IN nDepth int)BEGIN#Routine body goes here.DECLARE done INT DEFAULT 0;DECLARE b INT;DECLARE cur1 CURSOR FOR SELECT pid FROM treeNodes where id=rootId;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;insert into tmpLst values (null,rootId,nDepth); OPEN cur1;FETCH cur1 INTO b;WHILE done=0 DOCALL createChildLst(b,nDepth+1);FETCH cur1 INTO b;END WHILE;CLOSE cur1;END
