计算MySQL中父子模型的深度

How do I calculate a node's depth in a parent-child model under MySQL?

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

I'll need the depth to, among other things, create the indent in my list (coded with PHP).

我需要深度，除其他外，在我的列表中创建缩进（用PHP编码）。

3 个解决方案

#1

That depends on the actual implementation of your hierarchy in the database. If you are using nested sets model (http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) you can retrieve the full parent-to-child path via a single select.

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

Update: Ok, since you're going with adjacency list model I suggest to store node level in the table. Not only will it give you the node depth in one query, but it will also allow you to retrieve the entire path to that node in one query (albeit that query would have to be dynamically generated):

更新：好的，因为你要使用邻接列表模型，我建议将节点级别存储在表中。它不仅会在一个查询中为您提供节点深度，而且还允许您在一个查询中检索该节点的整个路径（尽管该查询必须动态生成）：

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;

Since you know that your node is on level N there's no need for left joins and, given appropriate indexes on id / parent_id this should be reasonably fast.
The downside to this approach is that you'll have to keep node level updated during node moves, but that should be reasonably straightforward and fast as you would only do it for the node itself and its children - not for the majority of the table as you would do with nested sets.

既然你知道你的节点在N级上就不需要左连接，并且在id / parent_id上有适当的索引，这应该相当快。这种方法的缺点是你必须在节点移动期间保持节点级别的更新，但是这应该是相当简单和快速的，因为你只对节点本身及其子节点这样做 - 而不是对于大多数表来说你会做嵌套集。

#2

This might be an old question, but I just want to let others know that I found a solution some months ago. I did recently write about it here: http://en.someotherdeveloper.com/articles/adjacency-list-model-with-depth-calculation/

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

#3

If you want just to copy paste here is my example. I have table Projects with ID and PARENT_ID fileds.

如果你只想复制粘贴，这是我的例子。我有表项目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 ;

#1

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;

#2

#3

If you want just to copy paste here is my example. I have table Projects with ID and PARENT_ID fileds.

如果你只想复制粘贴，这是我的例子。我有表项目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 ;

秒客网

计算MySQL中父子模型的深度

3 个解决方案

#1

#2

#3

#1

#2

#3

相关文章