在MySQL中计算父子模型中的深度

Question

如何在MySQL下的父子模型中计算节点的深度？

我需要深度来在我的列表中创建缩进(用PHP编码)。

Answer 1

这可能是一个老问题，但我只想让别人知道我几个月前找到了一个解决方案。我最近确实在这里写了一篇文章：http://en.someotherdeveloper.com/articles/adjacency-list-model-with-depth-calculation/

Answer 2

这取决于您的层次结构在数据库中的实际实现。如果您使用的是嵌套集模型(http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/)，则可以通过一次选择检索完整的父到子路径。

更新：好的，既然你要使用邻接表模型，我建议将节点级别存储在表中。它不仅在一个查询中给出了节点深度，而且还允许您在一个查询中检索到该节点的完整路径(尽管该查询必须动态生成)：

SELECT n1.name AS lvl1, n2.name as lvl2, n3.name as lvl3, ..., nN.name as lvlN
  FROM nodes AS n1
  JOIN nodes AS n2 ON n2.parent_id = n1.id
  JOIN nodes AS n3 ON n3.parent_id = n2.id
  ...
  JOIN nodes AS nN ON nN.parent_id = n(N-1).id
WHERE nN.id = myChildNode;

由于您知道您的节点在level N上，因此不需要左连接，并且给定id / parent_id上的适当索引，这应该是相当快的。

这种方法的缺点是，您必须在节点移动期间保持节点级别的更新，但这应该是相当直接和快速的，因为您只对节点本身及其子节点执行此操作-而不是像对嵌套集合那样对表的大部分执行此操作。

Answer 3

如果你只是想复制粘贴，这里就是我的例子。我有带有ID和PARENT_ID文件的表项目。

DELIMITER $$
DROP FUNCTION IF EXISTS `getDepth` $$
CREATE FUNCTION `getDepth` (project_id INT) RETURNS int
BEGIN
    DECLARE depth INT;
    SET depth=1;

    WHILE project_id > 0 DO
        SELECT IFNULL(parent_id,-1) 
        INTO project_id 
        FROM ( SELECT parent_id FROM Projects WHERE id = project_id) t;

        IF project_id > 0 THEN
            SET depth = depth + 1;
        END IF;

    END WHILE;

    RETURN depth;

END $$
DELIMITER ;