如何递归地查找所有子项的ID？

Question

我希望仅使用 MySQL 从树形结构的所有子元素中获取其 ID。

我有一个如下所示的表：

ID parent_id name
1  0         cat1
2  1         subcat1
3  2         sub-subcat1
4  2         sub-subcat2
5  0         cat2

现在我想递归地获取cat1的所有子ID（2、3、4）。有没有什么方法可以实现这个目标？

- dan

Answer 1

有两种基本方法可以实现此操作：邻接表和嵌套列表。请查看在 MySQL 中管理分层数据。

您拥有的是邻接表。没有一种方法可以使用单个 SQL 语句递归地抓取所有后代。如果可能的话，只需获取它们并在代码中映射它们。

嵌套集可以做到您想要的，但我倾向于避免使用它，因为插入记录的成本很高，并且容易出错。

- cletus

---

在支持递归的关系型数据库管理系统中，比如PostgreSQL，可以通过普通SQL查询实现递归。 - shesek

此链接返回的网页不可用。 - HMagdy

Answer 2

这里是一个简单的单查询MySql解决方案：

SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (
   SELECT @Ids := (
       SELECT GROUP_CONCAT(`ID` SEPARATOR ',')
       FROM `table_name`
       WHERE FIND_IN_SET(`parent_id`, @Ids)
   ) Level
   FROM `table_name`
   JOIN (SELECT @Ids := <id>) temp1
) temp2

只需将<id>用父元素的ID替换。

这将返回一个字符串，其中包含具有ID = <id>的元素的所有后代的ID，由,分隔。如果您希望返回多行，并在每行上有一个后代，请使用类似以下内容的内容：

SELECT *
FROM `table_name`
WHERE FIND_IN_SET(`ID`, (
   SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (
      SELECT @Ids := (
          SELECT GROUP_CONCAT(`ID` SEPARATOR ',')
          FROM `table_name`
          WHERE FIND_IN_SET(`parent_id`, @Ids)
      ) Level
      FROM `table_name`
      JOIN (SELECT @Ids := <id>) temp1
   ) temp2
))

包括根/父元素

OP请求元素的子级，已在上面回答。某些情况下，将根/父元素包含在结果中可能有用。以下是我的建议解决方案：

逗号分隔的ID字符串：

SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (
   SELECT <id> Level
   UNION
   SELECT @Ids := (
       SELECT GROUP_CONCAT(`ID` SEPARATOR ',')
       FROM `table_name`
       WHERE FIND_IN_SET(`parent_id`, @Ids)
   ) Level
   FROM `table_name`
   JOIN (SELECT @Ids := <id>) temp1
) temp2

多行：

SELECT *
FROM `table_name`
WHERE `ID` = <id> OR FIND_IN_SET(`ID`, (
   SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (
      SELECT @Ids := (
          SELECT GROUP_CONCAT(`ID` SEPARATOR ',')
          FROM `table_name`
          WHERE FIND_IN_SET(`parent_id`, @Ids)
      ) Level
      FROM `table_name`
      JOIN (SELECT @Ids := <id>) temp1
   ) temp2
))

- Magnar Myrtveit

---

你能解释一下你的解决方案吗？它看起来非常有前途，但我不确定我是否理解了它。@Ids是一个用于递归连接的别名函数/变量吗？你有关于哪些MySQL版本支持这种查询的更多信息吗？ - MonkeyMonkey

@Ids 是一个用户定义变量，在 MySQL 中已经存在很长时间了。我认为这个查询应该可以在任何你想使用的 MySQL 版本中运行。该查询的工作原理是将原始项目的所有子项的 ID 连接到第一行，然后将包含在第一行中的任何项目的所有子项的 ID 连接到第二行，依此类推。最后，所有行都连接到单个行中。可选地，该行可以拆分，以便每个项目都有自己的行（最后一个查询）。 - Magnar Myrtveit

这太棒了和非常出色，我们已经使用它一段时间了，但今天我意识到最后一个嵌套级别的子元素被截断了 - 在WHERE子句中必须有OR FIND_IN_SET(id, @Ids)。谢谢！ - Wirone

感谢@Wirone。这非常有趣。这实际上可能是MySQL中的一个bug。我已经在这里创建了另一个问题（链接：https://stackoverflow.com/questions/47430643/adding-an-index-changes-the-result-set）。 - Magnar Myrtveit

@Wirone 我已根据其他问题的反馈更新了查询。删除最后一个WHERE解决了这个问题。 - Magnar Myrtveit

Answer 3

创建表格应该像下面这样。

DROP TABLE IF EXISTS `parent_child`;
CREATE TABLE `parent_child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

insert  into `parent_child`(`id`,`name`,`parent_id`)
values (1,'cat1',0),(2,'subcat1',1),
(3,'sub-subcat1',2),(4,'sub-subcat2',2),
(5,'cat2',0);

创建获取父子元素的函数。

DELIMITER $$

USE `yourdatabase`$$

DROP FUNCTION IF EXISTS `GetAllNode1`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `GetAllNode1`(GivenID INT) RETURNS TEXT CHARSET latin1
    DETERMINISTIC
BEGIN
    DECLARE rv,q,queue,queue_children TEXT;
    DECLARE queue_length,front_id,pos INT;
    SET rv = '';
    SET queue = GivenID;
    SET queue_length = 1;
    WHILE queue_length > 0 DO
        SET front_id = queue;
        IF queue_length = 1 THEN
            SET queue = '';
        ELSE
            SET pos = LOCATE(',',queue) + 1;
            SET q = SUBSTR(queue,pos);
            SET queue = q;
        END IF;
        SET queue_length = queue_length - 1;
        SELECT IFNULL(qc,'') INTO queue_children
        FROM (SELECT GROUP_CONCAT(id) AS qc
        FROM `parent_child` WHERE `parent_id` = front_id) A ;
        IF LENGTH(queue_children) = 0 THEN
            IF LENGTH(queue) = 0 THEN
                SET queue_length = 0;
            END IF;
        ELSE
            IF LENGTH(rv) = 0 THEN
                SET rv = queue_children;
            ELSE
                SET rv = CONCAT(rv,',',queue_children);
            END IF;
            IF LENGTH(queue) = 0 THEN
                SET queue = queue_children;
            ELSE
                SET queue = CONCAT(queue,',',queue_children);
            END IF;
            SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
        END IF;
    END WHILE;
    RETURN rv;
END$$

DELIMITER ;

编写查询以获取所需输出。

SELECT GetAllNode1(id) FROM parent_child 
or 
SELECT GetAllNode1(id) FROM parent_child  where id =1 //for specific parent's child element 

- Pankaj katiyar

Answer 4

如果您可以使用存储过程，那么您可能可以通过存储过程来完成它。

否则，您无法使用单个SQL语句完成它。

理想情况下，您应该从程序中进行递归调用以遍历树形结构。

- jitter

Answer 5

你的问题似乎有点不精确。你想要它们，并且你所说的“在树形结构中”是什么意思？

你拥有的表格已经是（关系型表示方式的）树形结构了。

如果你想把它们“放入一个表格中”，并使用包含ID 4和ParentID 0对的行来保存它们，那么你需要你的SQL引擎版本的递归SQL来完成这个任务，前提是该引擎支持此功能。

我不知道MySQL具体情况，但我了解的情况是，他们曾计划使用Oracle相同的语法（即CONNECT BY）来实现递归SQL。

如果你在手册的目录中查找“递归查询”或“CONNECT BY”等关键词，我想你应该能够找到答案。

（很抱歉我不能提供更易于理解的答案。）

- Erwin Smout