各位用户为了找寻关于MySQL 实现树的遍历详解及简单实现示例的资料费劲了很多周折。这里教程网为您整理了关于MySQL 实现树的遍历详解及简单实现示例的相关资料，仅供查阅，以下为您介绍关于MySQL 实现树的遍历详解及简单实现示例的详细内容
MySQL 实现树的遍历
经常在一个表中有父子关系的两个字段，比如empno与manager，这种结构中需要用到树的遍历。在Oracle 中可以使用connect by简单解决问题，但MySQL 5.1中还不支持（据说已纳入to do中），要自己写过程或函数来实现。
一、建立测试表和数据：
? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18DROP TABLE IF EXISTS `channel`;
CREATE TABLE `channel` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(200) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
/*Data for the table `channel` */
insert into `channel`(`id`,`cname`,`parent_id`)
values (13,'首页',-1),
(14,'TV580',-1),
(15,'生活580',-1),
(16,'左上幻灯片',13),
(17,'帮忙',14),
(18,'栏目简介',17);
二、利用临时表和递归过程实现树的遍历（MySQL的UDF不能递归调用）：
? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170DELIMITER $$
USE `db1`$$
-- 从某节点向下遍历子节点
-- 递归生成临时表数据
DROP PROCEDURE IF EXISTS `createChildLst`$$
CREATE PROCEDURE `createChildLst`(IN rootId INT,IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT id FROM channel WHERE parent_id=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET max_sp_recursion_depth=12;
INSERT INTO tmpLst VALUES (NULL,rootId,nDepth);
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL createChildLst(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END$$
-- 从某节点向上追溯根节点
-- 递归生成临时表数据
DROP PROCEDURE IF EXISTS `createParentLst`$$
CREATE PROCEDURE `createParentLst`(IN rootId INT,IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT parent_id FROM channel WHERE id=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET max_sp_recursion_depth=12;
INSERT INTO tmpLst VALUES (NULL,rootId,nDepth);
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL createParentLst(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END$$
-- 实现类似Oracle SYS_CONNECT_BY_PATH的功能
-- 递归过程输出某节点id路径
DROP PROCEDURE IF EXISTS `createPathLst`$$
CREATE PROCEDURE `createPathLst`(IN nid INT,IN delimit VARCHAR(10),INOUT pathstr VARCHAR(1000))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE parentid INT DEFAULT 0;
DECLARE cur1 CURSOR FOR
SELECT t.parent_id,CONCAT(CAST(t.parent_id AS CHAR),delimit,pathstr)
FROM channel AS t WHERE t.id = nid;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET max_sp_recursion_depth=12;
OPEN cur1;
FETCH cur1 INTO parentid,pathstr;
WHILE done=0 DO
CALL createPathLst(parentid,delimit,pathstr);
FETCH cur1 INTO parentid,pathstr;
END WHILE;
CLOSE cur1;
END$$
-- 递归过程输出某节点name路径
DROP PROCEDURE IF EXISTS `createPathnameLst`$$
CREATE PROCEDURE `createPathnameLst`(IN nid INT,IN delimit VARCHAR(10),INOUT pathstr VARCHAR(1000))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE parentid INT DEFAULT 0;
DECLARE cur1 CURSOR FOR
SELECT t.parent_id,CONCAT(t.cname,delimit,pathstr)
FROM channel AS t WHERE t.id = nid;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET max_sp_recursion_depth=12;
OPEN cur1;
FETCH cur1 INTO parentid,pathstr;
WHILE done=0 DO
CALL createPathnameLst(parentid,delimit,pathstr);
FETCH cur1 INTO parentid,pathstr;
END WHILE;
CLOSE cur1;
END$$
-- 调用函数输出id路径
DROP FUNCTION IF EXISTS `fn_tree_path`$$
CREATE FUNCTION `fn_tree_path`(nid INT,delimit VARCHAR(10)) RETURNS VARCHAR(2000) CHARSET utf8
BEGIN
DECLARE pathid VARCHAR(1000);
SET @pathid=CAST(nid AS CHAR);
CALL createPathLst(nid,delimit,@pathid);
RETURN @pathid;
END$$
-- 调用函数输出name路径
DROP FUNCTION IF EXISTS `fn_tree_pathname`$$
CREATE FUNCTION `fn_tree_pathname`(nid INT,delimit VARCHAR(10)) RETURNS VARCHAR(2000) CHARSET utf8
BEGIN
DECLARE pathid VARCHAR(1000);
SET @pathid='';
CALL createPathnameLst(nid,delimit,@pathid);
RETURN @pathid;
END$$
-- 调用过程输出子节点
DROP PROCEDURE IF EXISTS `showChildLst`$$
CREATE PROCEDURE `showChildLst`(IN rootId INT)
BEGIN
DROP TEMPORARY TABLE IF EXISTS tmpLst;
CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst
(sno INT PRIMARY KEY AUTO_INCREMENT,id INT,depth INT);
CALL createChildLst(rootId,0);
SELECT channel.id,CONCAT(SPACE(tmpLst.depth*2),'--',channel.cname) NAME,channel.parent_id,tmpLst.depth,fn_tree_path(channel.id,'/') path,fn_tree_pathname(channel.id,'/') pathname
FROM tmpLst,channel WHERE tmpLst.id=channel.id ORDER BY tmpLst.sno;
END$$
-- 调用过程输出父节点
DROP PROCEDURE IF EXISTS `showParentLst`$$
CREATE PROCEDURE `showParentLst`(IN rootId INT)
BEGIN
DROP TEMPORARY TABLE IF EXISTS tmpLst;
CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst
(sno INT PRIMARY KEY AUTO_INCREMENT,id INT,depth INT);
CALL createParentLst(rootId,0);
SELECT channel.id,CONCAT(SPACE(tmpLst.depth*2),'--',channel.cname) NAME,channel.parent_id,tmpLst.depth,fn_tree_path(channel.id,'/') path,fn_tree_pathname(channel.id,'/') pathname
FROM tmpLst,channel WHERE tmpLst.id=channel.id ORDER BY tmpLst.sno;
END$$
DELIMITER ;
三、测试
? 1 2 3 4 5 6 7 8 9 10 11CALL showChildLst(-1);
CALL showChildLst(13);
CALL showChildLst(14);
CALL showChildLst(17);
CALL showChildLst(18);
CALL showParentLst(-1);
CALL showParentLst(13);
CALL showParentLst(14);
CALL showParentLst(17);
CALL showParentLst(18);
四、遗留问题
1. 因为mysql对动态游标的支持不够，所以要想做成通用的过程或函数比较困难，可以利用两个临时表来转换（同时去掉了递归调用）是个相对通用的实现。
2. 目前来看无论哪种实现，效率都不太好，希望mysql自己能实现Oracle 的connect by 功能，应该会比较优化。
感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！
原文链接：http://blog.csdn.net/wzy0623/article/details/53924307