子节点下所有节点ID的存储过程

mysql递归查询父节点函数MySQL递归查询父节点函数是一种非常有用的功能，它可以帮助我们快速地查询一个节点的所有父节点。

在实际应用中，我们经常需要查询一个节点的所有父节点，这时候就可以使用MySQL递归查询父节点函数来实现。

MySQL递归查询父节点函数的实现方法比较简单，主要是通过递归的方式来查询节点的所有父节点。

具体实现方法如下：1. 创建一个存储过程，用来查询节点的所有父节点。

2. 在存储过程中，首先查询当前节点的父节点，如果父节点存在，则将父节点的ID保存到一个临时表中。

3. 然后再递归查询父节点的父节点，直到查询到根节点为止。

4. 最后，将临时表中的所有父节点ID返回给调用者。

下面是一个示例代码：DELIMITER $$CREATE PROCEDURE `get_parent_nodes`(IN `node_id` INT) BEGINDECLARE parent_id INT;CREATE TEMPORARY TABLE IF NOT EXISTS`temp_parent_nodes` (`id` INT);SELECT `parent_id` INTO parent_id FROM `nodes` WHERE `id` = node_id;IF parent_id IS NOT NULL THENINSERT INTO `temp_parent_nodes` (`id`) VALUES(parent_id);CALL `get_parent_nodes`(parent_id);END IF;SELECT `id` FROM `temp_parent_nodes`;END$$DELIMITER ;在上面的代码中，我们首先定义了一个存储过程`get_parent_nodes`，它接受一个节点ID作为参数。

然后我们创建了一个临时表`temp_parent_nodes`，用来保存所有父节点的ID。

采用左右值编码来存储无限分级树形结构的数据库表设计之前我介绍过一种按位数编码保存树形结构数据的表设计方法，详情见：浅谈数据库设计技巧(上)该设计方案的优点是：只用一条查询语句即可得到某个根节点及其所有子孙节点的先序遍历。

由于消除了递归，在数据记录量较大时，可以大大提高列表效率。

但是，这种编码方案由于层信息位数的限制，限制了每层能所允许的最大子节点数量及最大层数。

同时，在添加新节点的时候必须先计算新节点的位置是否超过最大限制。

上面的设计方案必须预先设定类别树的最大层数以及最大子节点数，不是无限分级，在某些场合并不能采用，那么还有更完美的解决方案吗？通过google的搜索，我又探索到一种全新的无递归查询，无限分级的编码方案——左右值。

原文的程序代码是用php写的，但是通过仔细阅读其数据库表设计说明及相关的sql语句，我彻底弄懂了这种巧妙的设计思路，并在这种设计中新增了删除节点，同层平移的需求（原文只提供了列表及插入子节点的sql语句）。

下面我力图用比较简短的文字，少量图表，及相关核心sql语句来描述这种设计方案：首先，我们弄一棵树作为例子：商品|---食品| |---肉类| | |--猪肉| |---蔬菜类| |--白菜|---电器|--电视机|--电冰箱select count(*) from tree where lft <= 2 and rgt >= 11为了方便列表，我们可以为tree表建立一个视图，添加一个层数列，该类别的层数可以写一个自定义函数来计算。

该函数如下：CREATE FUNCTION dbo.CountLayer(@type_id int)RETURNS intASbegindeclare@result intset@result=0declare@lft intdeclare@rgt intif exists (select1from tree where type_id=@type_id)beginselect@lft=lft,@rgt=rgt from tree where type_id=@type_idselect@result=count(*) from tree where lft <=@lft and rgt >=@rgtendreturn@resultendGO然后，我们建立如下视图：CREATE VIEW dbo.TreeViewASSELECT type_id, name, lft, rgt, dbo.CountLayer(type_id) AS layer FROM dbo.tree ORDE R BY lftGO()AS declare declare ifgo假定我们要在节点“肉类”下添加一个子节点“牛肉”，该树将变成：1商品18+2+--------------------------------------------+2食品11+2 12+2电器17+2+-----------------+ +-------------------------+3肉类6+2 7+2蔬菜类10+2 13+2电视机14+2 15+2电冰箱16+2 +-------------+4猪肉5 6牛肉78+2白菜9+2看完上图相应节点左右值的变化后，相信大家都知道该如何写相应的sql脚本吧？下面我给出相对完整的插入子节点的存储过程：CREATE PROCEDURE[dbo].[AddSubNodeByNode](@type_id int,@name varchar(50))ASdeclare@rgt intif exists (select1from tree where type_id=@type_id)beginSET XACT_ABORT ONBEGIN TRANSACTIONselect@rgt=rgt from tree where type_id=@type_idupdate tree set rgt=rgt+2where rgt>=@rgtupdate tree set lft=lft+2where lft>=@rgtinsert into tree (name,lft,rgt) values (@name,@rgt,@rgt+1)COMMIT TRANSACTIONSET XACT_ABORT OFFend然后，我们删除节点“电视机”，再来看看该树会变成什么情况：1商品20-2+-----------------------------------+2食品13 14电器19-2+-----------------+3肉类8 9蔬菜类12 17-2电冰箱18-2+----------+4猪肉5 6牛肉7 10白菜11相应的存储过程如下：CREATE PROCEDURE[dbo].[DelNode]@type_id intASdeclare@lft intdeclare@rgt intif exists (select1from tree where type_id=@type_id)beginSET XACT_ABORT ONBEGIN TRANSACTIONselect@lft=lft,@rgt=rgt from tree where type_id=@type_iddelete from tree where lft>=@lft and rgt<=@rgtupdate tree set lft=lft-(@rgt-@lft+1) where lft>@lftupdate tree set rgt=rgt-(@rgt-@lft+1) where rgt>@rgtCOMMIT TRANSACTIONSET XACT_ABORT OFFEnd注意：因为删除某个节点会同时删除该节点的所有子孙节点，而这些被删除的节点的个数为：(被删节点的右值-被删节点的左值+1)/2，而任何一个节点同时具有唯一的左值和唯一的右值，故删除作废节点后，其他相应节点的左、右值需要调整的幅度应为：减少(被删节点的右值-被删节点的左值+1)。

MySQL 5.7 存储过程是一种在数据库中执行的预编译代码块，用于执行一系列操作。

以下是MySQL 5.7 存储过程的一些常见写法和总结：创建存储过程：sqlCREATE PROCEDURE procedure_name ([parameters])BEGIN-- 存储过程的逻辑代码END;参数定义：IN 参数：用于向存储过程传递值。

OUT 参数：用于从存储过程返回值。

INOUT 参数：既可以向存储过程传递值，也可以从存储过程返回值。

示例：sqlCREATE PROCEDURE example_procedure(IN param1 INT, OUT param2 VARCHAR(255)) BEGIN-- 存储过程的逻辑代码END;变量声明和使用：在存储过程中，可以使用 DECLARE 语句声明变量，并使用 SET 或 SELECT 语句为变量赋值。

示例：sqlDECLARE variable_name datatype;SET variable_name = value;-- 或者SELECT column_name INTO variable_name FROM table_name WHERE condition;控制流语句：MySQL 5.7 存储过程支持各种控制流语句，如 IF、CASE、LOOP、WHILE 等。

示例：sqlIF condition THEN-- 执行逻辑代码ELSEIF another_condition THEN-- 执行其他逻辑代码ELSE-- 执行默认逻辑代码END IF;错误处理：可以使用 DECLARE 语句声明错误处理程序，并在存储过程中使用 SIGNAL 或 RESIGNAL 语句引发或重新引发错误。

示例：sqlDECLARE CONTINUE HANDLER FOR SQLEXCEPTIONBEGIN-- 错误处理逻辑代码END;游标：游标用于在存储过程中遍历查询结果集。

mysql递归查找菜单节点的所有⼦节点背景项⽬中遇到⼀个需求，要求查处菜单节点的所有节点，在⽹上查了⼀下，⼤多数的⽅法⽤到了存储过程，由于线上环境不能随便添加存储过程，因此在这⾥采⽤类似递归的⽅法对菜单的所有⼦节点进⾏查询。

准备创建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_url` varchar(128) DEFAULT'' COMMENT '菜单路径',`status` tinyint(3) DEFAULT'1' COMMENT '菜单状态 1-有效；0-⽆效',PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=12212DEFAULT CHARSET=utf8;插⼊数据：INSERT INTO `menu` VALUES ('0', null, '菜单0', '', '1');INSERT INTO `menu` VALUES ('1', '0', '菜单1', '', '1');INSERT INTO `menu` VALUES ('11', '1', '菜单11', '', '1');INSERT INTO `menu` VALUES ('12', '1', '菜单12', '', '1');INSERT INTO `menu` VALUES ('13', '1', '菜单13', '', '1');INSERT INTO `menu` VALUES ('111', '11', '菜单111', '', '1');INSERT INTO `menu` VALUES ('121', '12', '菜单121', '', '1');INSERT INTO `menu` VALUES ('122', '12', '菜单122', '', '1');INSERT INTO `menu` VALUES ('1221', '122', '菜单1221', '', '1');INSERT INTO `menu` VALUES ('1222', '122', '菜单1222', '', '1');INSERT INTO `menu` VALUES ('12211', '1222', '菜单12211', '', '1');得到的⽬录结构如下图所⽰：查询先贴出sql语句：select id from (select t1.id,if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischildfrom (select id,parent_id from re_menu t where t.status = 1 order by parent_id, id) t1,(select @pids := 要查询的菜单节点 id) t2) t3 where ischild != 0⽐如，要查询菜单节点12的所有⼦节点,则查处的结果为：分析⾸先分析from后⾯的语句，根据parent_id和id 排序，并将要查询的菜单节点当做变量，from后⾯的结果为接下来看if（express1,express2,express3）条件语句，if语句类似三⽬运算符，当exprss1成⽴时，执⾏express2,否则执⾏express3;FIND_IN_SET(str,strlist),str 要查询的字符串,strlist 字段名参数以”,”分隔如 (1,2,6,8),查询字段(strlist)中包含(str)的结果，返回结果为null或记录如果parent_id 在@pid中，则将@pid ⾥⾯再加上parent_id,按⾏依次执⾏，执⾏过程如下表所⽰：这时，显⽰的id就是菜单id为12的所有⼦节点id。