投稿
  1. 云图网首页
  2. 技术专区
  3. 数据库

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;    
INSERT  INTO 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;

测试结果：

mysql 实现树形的遍历详解数据库

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;

测试结果：

mysql 实现树形的遍历详解数据库

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 id;

测试结果：
mysql 实现树形的遍历详解数据库

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;

测试结果：
mysql 实现树形的遍历详解数据库

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 id FROM channel WHERE parent_id=rootId; 
    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 id=rootId; 
    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);

测试结果：

mysql 实现树形的遍历详解数据库

3.2、显示首页下面的子节点

CALL pro_show_childlist(13);

测试结果：

mysql 实现树形的遍历详解数据库

3.3、显示TV580下面的所有子节点

CALL pro_show_childlist(14);

测试结果：

mysql 实现树形的遍历详解数据库

3.4、“帮忙”节点有一个子节点，显示出来：

CALL pro_show_childlist(17);

测试结果：

mysql 实现树形的遍历详解数据库

3.5、“栏目简介”没有子节点，所以只显示最终节点：

mysql 实现树形的遍历详解数据库

3.6、显示“首页”的父节点

CALL pro_show_parentlist(13);

测试结果：

mysql 实现树形的遍历详解数据库

3.7、显示“TV580”的父节点，parent_id为-1

CALL pro_show_parentlist(14);

测试结果：

mysql 实现树形的遍历详解数据库

3.8、显示“帮忙”节点的父节点

CALL pro_show_parentlist(17);

测试结果：

mysql 实现树形的遍历详解数据库

3.9、显示最低层节点“栏目简介”的父节点

CALL pro_show_parentlist(18);

测试结果：

mysql 实现树形的遍历详解数据库

原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/5730.html

赞 (0)
0 0
上一篇 2021年7月17日
下一篇 2021年7月17日

相关推荐

发表回复

登录后才能评论