MySQL：如何在特定节点中查找叶子

MySQL：如何在特定节点中查找叶子

无法在单个查询中执行此 *** 作。即使有，它也可能效率很低。

我们可以通过存储过程和循环来实现。使用添加的索引，它也应该很快。这使用两个表从输入表（A）中选择节点，并将该节点及其子级插入（B）。然后，它将B交换为A，并重复执行直到直到A中不再存在非叶节点为止。可喜的是，循环迭代的数量将与输入节点和最后一个叶节点之间的级别一样多，在大多数情况下，循环迭代次数为可能没有那么深。此存储过程比在代码中进行外部存储过程要快。

仅供参考，我在安装临时表时遇到了困难，如果遇到“错误2”，请删除临时关键字。

delimiter $$drop procedure if exists GetLeafNodes $$create procedure GetLeafNodes(nodeid int)begindeclare N int default 1;-- create two working sets of IDs, we'll go back and forth between these two setsdrop 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 setinsert 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 nodesselect 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);