mysql树形查询三级_[MySQL] 实现树形的遍历（关于多级菜单栏以及多级上下部门的查询问题）...

最新推荐文章于 2022-05-21 17:25:21 发布
最新推荐文章于 2022-05-21 17:25:21 发布
阅读量644 收藏
点赞数
文章标签： mysql树形查询三级
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39841610/article/details/113205593
版权

前言：

关于多级别菜单栏或者权限系统中部门上下级的树形遍历，oracle中有connect by来实现，mysql没有这样的便捷途径，所以MySQL遍历数据表是我们经常会遇到的头痛问题，下面通过存储过程来实现。

1，建立测试表和数据：

DROP TABLE IF EXISTS csdn.channel;

CREATE TABLE csdn.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);

DROP TABLE IF EXISTS channel;

2，利用临时表和递归过程实现树的遍历(mysql的UDF不能递归调用)：

2.1，从某节点向下遍历子节点，递归生成临时表数据

-- pro_cre_childlist

DELIMITER $$

DROP PROCEDURE IF EXISTS csdn.pro_cre_childlist$$

CREATE PROCEDURE csdn.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 tmpLst 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$$

2.2，从某节点向上追溯根节点，递归生成临时表数据

-- pro_cre_parentlist

DELIMITER $$

DROP PROCEDURE IF EXISTS csdn.pro_cre_parentlist$$

CREATE PROCEDURE csdn.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 tmpLst 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$$

2.3，实现类似Oracle SYS_CONNECT_BY_PATH的功能，递归过程输出某节点id路径

-- pro_cre_pathlist

DELIMITER $$

USE csdn$$

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;

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 pro_cre_pathlist(parentid,delimit,pathstr);

FETCH cur1 INTO parentid,pathstr;

END WHILE;

CLOSE cur1;

END$$

DELIMITER ;

2.4，递归过程输出某节点name路径

-- pro_cre_pnlist

DELIMITER $$

USE csdn$$

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;

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 pro_cre_pnlist(parentid,delimit,pathstr);

FETCH cur1 INTO parentid,pathstr;

END WHILE;

CLOSE cur1;

END$$

DELIMITER ;

2.5，调用函数输出id路径

-- fn_tree_path

DELIMITER $$

DROP FUNCTION IF EXISTS csdn.fn_tree_path$$

CREATE FUNCTION csdn.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$$

2.6，调用函数输出name路径

-- fn_tree_pathname

-- 调用函数输出name路径

DELIMITER $$

DROP FUNCTION IF EXISTS csdn.fn_tree_pathname$$

CREATE FUNCTION csdn.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 ;

2.7，调用过程输出子节点

-- pro_show_childLst

DELIMITER $$

-- 调用过程输出子节点

DROP PROCEDURE IF EXISTS pro_show_childLst$$

CREATE PROCEDURE pro_show_childLst(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 pro_cre_childlist(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$$

2.8，调用过程输出父节点

-- pro_show_parentLst

DELIMITER $$

-- 调用过程输出父节点

DROP PROCEDURE IF EXISTS `pro_show_parentLst`$$

CREATE PROCEDURE `pro_show_parentLst`(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 pro_cre_parentlist(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$$

3，开始测试：

3.1，从根节点开始显示，显示子节点集合：

mysql> CALL pro_show_childLst(-1);

+----+-----------------------+-----------+-------+-------------+----------------------------+

| id | NAME                  | parent_id | depth | path        | pathname                   |

+----+-----------------------+-----------+-------+-------------+----------------------------+

| 13 |   --首页              |        -1 |     1 | -1/13       | 首页/                      |

| 16 |     --左上幻灯片      |        13 |     2 | -1/13/16    | 首页/左上幻灯片/           |

| 14 |   --TV580             |        -1 |     1 | -1/14       | TV580/                     |

| 17 |     --帮忙            |        14 |     2 | -1/14/17    | TV580/帮忙/                |

| 18 |       --栏目简介      |        17 |     3 | -1/14/17/18 | TV580/帮忙/栏目简介/       |

| 15 |   --生活580           |        -1 |     1 | -1/15       | 生活580/                   |

+----+-----------------------+-----------+-------+-------------+----------------------------+

6 rows in set (0.05 sec)

Query OK, 0 rows affected (0.05 sec)

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

CALL pro_show_childLst(13);

mysql> CALL pro_show_childLst(13);

+----+---------------------+-----------+-------+----------+-------------------------+

| id | NAME                | parent_id | depth | path     | pathname                |

+----+---------------------+-----------+-------+----------+-------------------------+

| 13 | --首页              |        -1 |     0 | -1/13    | 首页/                   |

| 16 |   --左上幻灯片      |        13 |     1 | -1/13/16 | 首页/左上幻灯片/        |

+----+---------------------+-----------+-------+----------+-------------------------+

2 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

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

CALL pro_show_childLst(14);

mysql> CALL pro_show_childLst(14);

+----+--------------------+-----------+-------+-------------+----------------------------+

| id | NAME               | parent_id | depth | path        | pathname                   |

+----+--------------------+-----------+-------+-------------+----------------------------+

| 14 | --TV580            |        -1 |     0 | -1/14       | TV580/                     |

| 17 |   --帮忙           |        14 |     1 | -1/14/17    | TV580/帮忙/                |

| 18 |     --栏目简介     |        17 |     2 | -1/14/17/18 | TV580/帮忙/栏目简介/       |

+----+--------------------+-----------+-------+-------------+----------------------------+

3 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

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

CALL pro_show_childLst(17);

mysql> CALL pro_show_childLst(17);

+----+------------------+-----------+-------+-------------+----------------------------+

| id | NAME             | parent_id | depth | path        | pathname                   |

+----+------------------+-----------+-------+-------------+----------------------------+

| 17 | --帮忙           |        14 |     0 | -1/14/17    | TV580/帮忙/                |

| 18 |   --栏目简介     |        17 |     1 | -1/14/17/18 | TV580/帮忙/栏目简介/       |

+----+------------------+-----------+-------+-------------+----------------------------+

2 rows in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql>

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

mysql> CALL pro_show_childLst(18);

+----+----------------+-----------+-------+-------------+----------------------------+

| id | NAME           | parent_id | depth | path        | pathname                   |

+----+----------------+-----------+-------+-------------+----------------------------+

| 18 | --栏目简介     |        17 |     0 | -1/14/17/18 | TV580/帮忙/栏目简介/       |

+----+----------------+-----------+-------+-------------+----------------------------+

1 row in set (0.36 sec)

Query OK, 0 rows affected (0.36 sec)

mysql>

3.6，显示根节点的父节点

CALL pro_show_parentLst(-1);

mysql> CALL pro_show_parentLst(-1);

Empty set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

3.7，显示“首页”的父节点

CALL pro_show_parentLst(13);

mysql> CALL pro_show_parentLst(13);

+----+----------+-----------+-------+-------+----------+

| id | NAME     | parent_id | depth | path  | pathname |

+----+----------+-----------+-------+-------+----------+

| 13 | --首页   |        -1 |     0 | -1/13 | 首页/    |

+----+----------+-----------+-------+-------+----------+

1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

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

CALL pro_show_parentLst(14);

mysql> CALL pro_show_parentLst(14);

+----+---------+-----------+-------+-------+----------+

| id | NAME    | parent_id | depth | path  | pathname |

+----+---------+-----------+-------+-------+----------+

| 14 | --TV580 |        -1 |     0 | -1/14 | TV580/   |

+----+---------+-----------+-------+-------+----------+

1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

3.9，显示“帮忙”节点的父节点

mysql>

CALL pro_show_parentLst(17);

mysql> CALL pro_show_parentLst(17);

+----+-----------+-----------+-------+----------+---------------+

| id | NAME      | parent_id | depth | path     | pathname      |

+----+-----------+-----------+-------+----------+---------------+

| 17 | --帮忙    |        14 |     0 | -1/14/17 | TV580/帮忙/   |

| 14 |   --TV580 |        -1 |     1 | -1/14    | TV580/        |

+----+-----------+-----------+-------+----------+---------------+

2 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

3.10，显示最低层节点“栏目简介”的父节点

CALL pro_show_parentLst(18);

mysql> CALL pro_show_parentLst(18);

+----+----------------+-----------+-------+-------------+----------------------------+

| id | NAME           | parent_id | depth | path        | pathname                   |

+----+----------------+-----------+-------+-------------+----------------------------+

| 18 | --栏目简介     |        17 |     0 | -1/14/17/18 | TV580/帮忙/栏目简介/       |

| 17 |   --帮忙       |        14 |     1 | -1/14/17    | TV580/帮忙/                |

| 14 |     --TV580    |        -1 |     2 | -1/14       | TV580/                     |

+----+----------------+-----------+-------+-------------+----------------------------+

3 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

参考网址：

http://jan.kneschke.de/projects/mysql/sp/sp_tree.sql

http://blog.csdn.net/ylqmf/article/details/5172901

weixin_39841610
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySql树形结构（多级菜单）查询设计方案
Java_LingFeng的博客
01-11 3089
多级查询、三级查询本质就是树形结构的遍历，推荐使用多级查询的方式，相比三级查询多级查询的方式抓住了树形结构遍历的本质，方便扩展和维护。技术只是工具，多级查询的方式不是固定的，查询方式合理既可，但通常都需要加关系表辅助设计。
在Mysql数据库里通过存储过程实现树形的遍历
09-09
关于多级别菜单栏或者权限系统中部门上下级的树形遍历，oracle中有connect by来实现，mysql没有这样的便捷途径，所以MySQL遍历数据表是我们经常会遇到的头痛问题，下面给大家介绍在Mysql数据库里通过存储过程实现树...
mysql 递归查找菜单节点的所有子节点的方法
01-21
背景 项目中遇到一个需求，要求查处菜单节点的所有节点，在网上查了一下，大多数的方法用到了存储过程，由于线上环境不能随便添加存储过程， 因此在这里采用类似递归的方法对菜单的所有子节点进行查询。 准备 创建menu表： CREATE TABLE `menu` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单id', `parent_id` int(11) DEFAULT NULL COMMENT '父节点id', `menu_name` varchar(128) DEFAULT NULL COMMENT '菜单名称', `menu_ur
python 提取固定列名数据_python获取数据库列名
weixin_39806288的博客
12-01 6064
{"moduleinfo":{"card_count":[{"count_phone":1,"count":1}],"search_count":[{"count_phone":4,"count":4}]},"card":[{"des":"阿里云数据库专家保驾护航，为用户的数据库应用系统进行性能和风险评估，参与配合进行数据压测演练，提供数据库优化方面专业建议，在业务高峰期与用户共同保障数据库系统平...
mysql 父子维,三级联动的省市区(将有父子关系的一维数组转换成树形结构(多维)数据)...
weixin_31495637的博客
03-17 313
function translateDataToTre2(data) {let parents = data.filter(value => value.pid === 'undefined' || value.pid === null || value.pid === '')let children = data.filter(value => value.pid !== 'unde...
二叉树的遍历算法（三级）
Leon_Jinhai_Sun的博客
04-28 575
知道二叉树在计算机里面是怎么存储的,有顺序存储结构,采用数组存储,也有链式存储结构,采用二叉链表,或者采用三叉链表,那采用 链式存储结构更多一点,下面我们会讲二叉树在计算机里的实现,就是链式存储结构的二叉链表为例来进行讲解的,讲这个具体实现之前呢, 我们要知道一下二叉树的遍历策略, 什么叫遍历? 大家想一下,如果是一个线性表的话,他的遍历是很简单的,怎么简单呢,比如说...
MySQL实现树形的遍历
12-14
　关于多级别菜单栏或者权限系统中部门上下级的树形遍历，oracle中有connect by来实现，mysql没有这样的便捷途径，所以MySQL遍历数据表是我们经常会遇到的头痛问题，下面通过存储过程来实现。 　1，建立测试表和...
mySql实现树形查询的函数存储过程例子
06-08
mySql实现树形查询的函数存储过程例子
Mysql树形递归查询的实现方法
09-09
主要给大家介绍了关于Mysql树形递归查询的实现方法，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧
[Sql]树结构表，查询所有末级节点
weixin_34255055的博客
03-07 827
目标：显示所有末级节点思路：根据父节点count数，判断是否有子节点源码sqlselectt."CLASS_CODE",t."CLASS_NAME",t."FATHER_CLASS_CODE",t."CLASS_STATE",t."OP_USER",t."OP_TIME",t."IS_PROJECT",nvl(t2.isParent,0)isParentfromFSS...
MySQL多层级结构-树搜索介绍
12-16
基本上在每个系统中都有那么几张表是自关联父子关系的结构。往往有很多人都是使用pid来做关联。在刚进入IT行业时使用CAKEPHP框架编写WEB的时候，使用它里面的一个ACL plugin实现权限管理的时候。发现一个表结构硬是不明白是怎么回事。具体表结构如下： CREATE TABLE acos ( id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT, parent_id INTEGER(10) DEFAULT NULL, model VARCHAR(255) DEFAULT '', foreign_key INTEGER(10) UNSIG
sql 中 exists和 in如何选择
znanj的博客
09-09 827
如果外部查询是“大”和内部查询是“小”，IN是一般效率比EXISTS好。 如果外部查询是“小”和内部查询是“大”，那么EXISTS是相当有效的。
mysql查询末级节点_Linq or Sql 取树末级节点
weixin_39701834的博客
01-28 545
Listlist=newList{newEntity(){Id=1,Name="根",Pid=0},newEntity(){Id=2,Name="木",Pid=1},newEntity(){Id=3,Name="铁",Pid=1},newEntity(){Id=4,Name="金",Pid=1},newEntity(){...
[MySQL] 实现树形的遍历（关于多级菜单栏以及多级上下部门的查询问题）
niit_java的专栏
09-29 818
转自:http://blog.csdn.net/mchdba/article/details/39277301 前言：         关于多级别菜单栏或者权限系统中部门上下级的树形遍历，oracle中有connect by来实现，mysql没有这样的便捷途径，所以MySQL遍历数据表是我们经常会遇到的头痛问题，下面通过存储过程来实现。 1，建立测试表和数据： DROP T
mysql获取终节点_mysql 函数获取子节点
weixin_42512021的博客
01-28 411
DELIMITER $$USE `topsale`$$DROP FUNCTION IF EXISTS `getShopIdByUserId`$$CREATE DEFINER=`root`@`%` FUNCTION `getShopIdByUserId`(userId VARCHAR())RETURNS VARCHAR() CHARSET utf8 ##定义返回值类型 varchar()BEGI...
MySQL节点及下面子节点查询
weixin_44924565的博客
12-16 237
select id from ( select t1.id, if(find_in_set(superior,@pids) >0,@pids := concat(@pids,',', id),0) as ischild from ( select id,superior ...
mysql 数据多级筛选_[MySQL] 实现树形的遍历（关于多级菜单栏以及多级上下部门的查询问题）...
weixin_30045135的博客
01-21 356
前言：关于多级别菜单栏或者权限系统中部门上下级的树形遍历，oracle中有connect by来实现，mysql没有这样的便捷途径，所以MySQL遍历数据表是我们经常会遇到的头痛问题，下面通过存储过程来实现。1，建立测试表和数据：DROP TABLE IF EXISTS csdn.channel;CREATE TABLE csdn.channel (id INT(11) NOT NULL AUTO...
MySQL 使用函数级联查询上级和下级
不积硅步无以至千里
05-21 2211
：）项目中遇到了类似的处理，觉得比直接写sql好用，小记一下 创建表： -- ---------------------------- -- Table structure for level -- ---------------------------- DROP TABLE IF EXISTS `level`; CREATE TABLE `level` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARAC
MYSQL 自定义函数，查询节点所有父节点、查询节点所有子节点
一念轮回君千殇的博客
04-09 699
一.查询所有父节点。 1.s...
mysql 查询多级类别_SQL处理多级分类,查询结果呈树形结构 5.x版本
最新发布
05-26
在 MySQL 5.x 版本中，可以使用递归查询来实现处理多级分类。以下是一个示例： 假设我们有一个名为 `category` 的表，其中包含以下字段： - `id`：分类 ID - `name`：分类名称 - `parent_id`：父级分类 ID，如果该...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值