mysql 实现树形的遍历
前言：关于多级别菜单栏或者权限系统中部门上下级的树形遍历，oracle中有connect by来实现，mysql没有这样的便捷途径，所以MySQL遍历数据表是我们经常会遇到的头痛问题，下面通过存储过程来实现。
1、建立测试表和数据：
DROP TABLE IF EXISTS test.channel;
CREATE TABLE test.channel (
id
INT(11) NOT NULL AUTO_INCREMENT,
cname
VARCHAR(200) DEFAULT NULL,
parent_id
INT(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE
=INNODB DEFAULT CHARSET=utf8;
INSERTINTO channel(id,cname,parent_id)
VALUES (13,'首页',-1),
(
14,'TV580',-1),
(
15,'生活580',-1),
(
16,'左上幻灯片',13),
(
17,'帮忙',14),
(
18,'栏目简介',17);
2、用临时表和递归过程实现树的遍历（mysql的UDF不能递归调用）：
2.1、递归过程输出某节点id路径，类似Oracle SYS_CONNECT_BY_PATH的功能
-- 递归输出某节点id路径
DELIMITER //
DROP PROCEDURE IF EXISTS pro_cre_pathlist;
CREATE PROCEDURE pro_cre_pathlist(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;
-- 下面这行表示若没有数据返回，程序继续，并将变量done设为1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- mysql中可以利用系统参数 max_sp_recursion_depth 来控制递归调用的层数上限。
SET max_sp_recursion_depth=12;
OPEN cur1;
-- 游标向下走一步
FETCH cur1 INTO parentid,pathstr;
WHILE done=0 DO
CALL pro_cre_pathlist(parentid,delimit,pathstr);
-- 游标向下走一步
FETCH cur1 INTO parentid,pathstr;
END WHILE;
CLOSE cur1;
END //
DELIMITER ;
测试：
SET @str='16';
CALL pro_cre_pathlist(
16,'/',@str);
SELECT @str;
测试结果：
2.2、递归过程输出某节点name路径
-- 递归输出某节点name路径
DELIMITER //
DROP PROCEDURE IF EXISTS pro_cre_pnlist;
CREATE PROCEDURE pro_cre_pnlist(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;
-- 下面这行表示若没有数据返回，程序继续，并将变量done设为1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- mysql中可以利用系统参数 max_sp_recursion_depth 来控制递归调用的层数上限。
SET max_sp_recursion_depth=12;
OPEN cur1;
-- 游标向下走一步
FETCH cur1 INTO parentid,pathstr;
WHILE done=0 DO
CALL pro_cre_pnlist(parentid,delimit,pathstr);
-- 游标向下走一步
FETCH cur1 INTO parentid,pathstr;
END WHILE;
CLOSE cur1;
END //
DELIMITER ;
测试：
SET @str='';
CALL pro_cre_pnlist(
16,'/',@str);
SELECT @str;
测试结果：
2.3、调用函数输出id路径
-- 调用函数输出id路径
DELIMITER //
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 pro_cre_pathlist(nid,delimit,pathid);
RETURN pathid;
END //
DELIMITER ;
测试：
SELECT fn_tree_path(16,'/') AS>
测试结果：
2.4、调用函数输出name路径
-- 调用函数输出name路径
DELIMITER //
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 pro_cre_pnlist(nid,delimit,pathid);
RETURN pathid;
END //
DELIMITER ;
测试：
SELECT fn_tree_pathname(16,'/') AS name;
测试结果：
2.5、调用过程输出子节点
-- 调用过程输出子节点
DELIMITER //
DROP PROCEDURE IF EXISTS pro_show_childlist;
CREATE PROCEDURE pro_show_childlist(IN rootId INT)
BEGIN
DROP TEMPORARY TABLE IF EXISTS tmpList;
CREATE TEMPORARY TABLE IF NOT EXISTS tmpList(
sno INT PRIMARY KEY AUTO_INCREMENT,
id INT,
depth INT);
CALL pro_cre_childlist(rootId,0);
SELECT channel.id,CONCAT(SPACE(tmpList.depth*2),'--',channel.cname)NAME,
channel.parent_id,tmpList.depth,fn_tree_path(channel.id,'/')path,
fn_tree_pathname(channel.id,'/')pathname FROM tmpList,channel
WHERE tmpList.id=channel.id ORDER BY tmpList.sno;
END //
DELIMITER ;
2.6、从某节点向下遍历子节点，递归生成临时表数据
DELIMITER //
DROP PROCEDURE IF EXISTS pro_cre_childlist;
CREATE PROCEDURE pro_cre_childlist(IN rootId INT,IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT>
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET max_sp_recursion_depth=12;
INSERT INTO tmpList VALUES(NULL,rootId,nDepth);
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL pro_cre_childlist(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE
CLOSE cur1;
END //
DELIMITER ;
2.7、调用过程输出父节点
-- 调用过程输出父节点
DELIMITER //
DROP PROCEDURE IF EXISTS pro_show_parentlist;
CREATE PROCEDURE pro_show_parentlist(IN rootId INT)
BEGIN
DROP TEMPORARY TABLE IF EXISTS tmpList;
CREATE TEMPORARY TABLE IF NOT EXISTS tmpList(
sno INT PRIMARY KEY AUTO_INCREMENT,
id INT,
depth INT);
CALL pro_cre_parentlist(rootId,0);
SELECT channel.id,CONCAT(SPACE(tmpList.depth*2),'--',channel.cname)NAME,
channel.parent_id,tmpList.depth,fn_tree_path(channel.id,'/')path,
fn_tree_pathname(channel.id,'/')pathname FROM tmpList,channel
WHERE tmpList.id=channel.id ORDER BY tmpList.sno;
END //
DELIMITER ;
2.8、从某节点向上追溯根节点，递归生成临时表数据
DELIMITER //
DROP PROCEDURE IF EXISTS pro_cre_parentlist;
CREATE PROCEDURE pro_cre_parentlist(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>
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET max_sp_recursion_depth=12;
INSERT INTO tmpList VALUES(NULL,rootId,nDepth);
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL pro_cre_parentlist(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END //
DELIMITER ;
3、开始测试
3.1、从根节点开始显示，显示子节点集合：
CALL pro_show_childlist(-1);
测试结果：
3.2、显示首页下面的子节点
CALL pro_show_childlist(13);
测试结果：
3.3、显示TV580下面的所有子节点
CALL pro_show_childlist(14);
测试结果：
3.4、“帮忙”节点有一个子节点，显示出来：
CALL pro_show_childlist(17);
测试结果：
3.5、“栏目简介”没有子节点，所以只显示最终节点：
3.6、显示“首页”的父节点
CALL pro_show_parentlist(13);
测试结果：
3.7、显示“TV580”的父节点，parent_id为-1
CALL pro_show_parentlist(14);
测试结果：
3.8、显示“帮忙”节点的父节点
CALL pro_show_parentlist(17);
测试结果：
3.9、显示最低层节点“栏目简介”的父节点
CALL pro_show_parentlist(18);
测试结果：
页:
[1]