mysql中怎么查看从节点_MySQL：如何在特定节点中查找叶子

在单个查询中无法执行此操作.即使有它可能会非常低效.

我们可以使用存储过程和循环来完成它.使用你添加的索引它应该也很快.这使用两个表从输入表(A)中选择节点并将节点及其子节点插入(B).然后它将B交换为A,并重复直到A中不再存在非叶节点为止.好处是循环迭代只会与输入节点和最后一个叶节点之间的级别一样多,这在大多数情况下是可能没那么深.这个存储过程比在代码中外部执行更快.

仅供参考我在安装处理临时表时遇到困难,如果收到“错误2”,则删除临时关键字.

delimiter $$

drop procedure if exists GetLeafNodes $$

create procedure GetLeafNodes(nodeid int)

begin

declare N int default 1;

-- create two working sets of IDs, we'll go back and forth between these two sets

drop temporary table if exists A;

drop temporary table if exists B;

create temporary table A(node int, child int);

create temporary table B(node int, child int);

-- insert our single input node into the working set

insert into A values (null, nodeid);

while (N>0) do

-- keep selecting child nodes for each node we are now tracking

-- leaf nodes will end up with the child set to null

insert into B

select ifnull(A.child,A.node), tree.ID

from A

left outer join DATA_TREE as tree on A.child=tree.parent_id;

-- now swap A and B

rename table A to temp, B to A, temp to B;

-- remove non-leaf nodes from table B

delete from B;

-- exit when there are no longer any non-leaf nodes in A

set N=(select count(*) from A where child is not null);

end while;

-- now output our list of leaf nodes

select node from A;

drop temporary table A;

drop temporary table B;

end $$

DELIMITER ;

call GetLeafNodes(4);

我使用以下示例集进行测试：

CREATE TABLE `DATA_TREE` (

`ID` int(11) NOT NULL,

`PARENT_ID` int(11) NOT NULL,

PRIMARY KEY (`ID`),

UNIQUE KEY `ID_UNIQUE` (`ID`),

KEY `fk_DATA_TREE_1_idx` (`PARENT_ID`)

) ENGINE=InnoDB

;

insert into DATA_TREE values

(1,0),(2,1),(3,1),(4,1),(5,3),(6,3),(7,4),(8,4),(9,4),(10,6),(11,6),(12,7),(13,9),(14,9),(15,12),(16,12),(17,12),(18,14);