如何查找树表示的层次结构路径

Question

我有一个树状的层次结构，它内置于一个表中，其中的parent_id指向上一个根节点。

我遍历所有根节点(root1、root2)，并将路径设置为root1或root1和child1的根1/子节点1。为了找到child1的路径，我必须进行至少2次调用才能形成路径。有没有一种有效的方法来填充路径，因为我们要处理大量的根节点和子节点，它们嵌套在5-7层深。

create table foo (id, name, parent_id, path)
insert into foo (1, "root1', null, null)
insert into foo (2, "child1', 1, null)

root1 (path = null)
  child1 (path = root1)
    subchild1 (path = root1/child1)

root2
   child2
     subchild2

Answer 1

您可以使用您在问题中提到的存储过程，因为嵌套深度可以达到7层。

存储过程

CREATE PROCEDURE updatePath()
BEGIN
declare cnt, n int;
    select count(*) into n from foo where parent_id is null;
    update foo a, foo b set a.path = b.name where b.parent_id is null and a.parent_id = b.id;
    select count(*) into cnt from foo where path is null;
    while cnt > n do
        update foo a, foo b set a.path = concat(b.path, '/', b.name) where b.path is not null and a.parent_id = b.id;
        select count(*) into cnt from foo where path is null;
    end while;
END//

为了检查实际记录，我们只打印了path列中有空值的普通记录

select * from foo

结果

| ID |         NAME | PARENT_ID |   PATH |
------------------------------------------
|  1 |        root1 |    (null) | (null) |
|  2 |       child1 |         1 | (null) |
|  3 |    subchild1 |         2 | (null) |
|  4 |       child2 |         1 | (null) |
|  5 |       child3 |         1 | (null) |
|  6 |    subchild2 |         4 | (null) |
|  7 | subsubchild1 |         6 | (null) |

调用过程的

call updatepath

过程执行后的结果

select * from foo

结果

| ID |         NAME | PARENT_ID |                   PATH |
----------------------------------------------------------
|  1 |        root1 |    (null) |                 (null) |
|  2 |       child1 |         1 |                  root1 |
|  3 |    subchild1 |         2 |           root1/child1 |
|  4 |       child2 |         1 |                  root1 |
|  5 |       child3 |         1 |                  root1 |
|  6 |    subchild2 |         4 |           root1/child2 |
|  7 | subsubchild1 |         6 | root1/child2/subchild2 |

希望这能帮上忙。

Answer 2

我真的很喜欢经过修改的前序树遍历。它允许您在一个查询中获得整个树的层次结构。这里有一个详细的教程：http://www.sitepoint.com/hierarchical-data-database-2/

如果你有任何关于MPTT的问题，请让我知道，我很乐意帮助你！

Answer 3

虽然在单个调用中严格来说是不可能的，但您可以隐藏多个调用，但可以将它们放入从SQL调用的MySQL函数中，该函数返回父路径。

虽然这可能比在脚本中更高效，但我不希望它有那么高的效率。

如果最大层数是固定的，你可以像下面这样使用连接：

SELECT foo.id, foo.name, CONCAT_WS(',', d.name, c.name, b.name, a.name)
FROM foo
LEFT OUTER JOIN foo a ON foo.parent_id = a.id
LEFT OUTER JOIN foo b ON a.parent_id = b.id
LEFT OUTER JOIN foo c ON b.parent_id = c.id
LEFT OUTER JOIN foo d ON c.parent_id = d.id

虽然这将工作，但它是相当有限制的(即，如果最大级别数改变，您将不得不使用此更改SQL的每一位)，另外，如果级别数不是很小，它将变得不可读。