代码之家 › 专栏 › 技术社区 › nickf

mysql中遍历树的深度嵌套子查询

mptt adjacency-list tree mysql sql

nickf · 技术社区 · 14 年前

我在数据库中有一个表,在该表中,我使用混合嵌套集(MPTT)模型(具有 lft 和 rght 值)和邻接列表模型(存储 parent_id 在每个节点上)。

my_table (id, parent_id, lft, rght, alias)

这个问题与树的任何MPTT方面都没有关系,但我想我会离开它,以防有人对如何利用它有一个好主意。

我要将别名路径转换为特定节点。例如: "users.admins.nickf" 将找到别名为“nickf”的节点,它是别名为“admins”的节点的子节点,别名为“users”的子节点,位于根节点上。上有唯一索引 (parent_id, alias) .

我首先编写函数,这样它就可以将路径拆分为各个部分,然后逐个查询数据库:

SELECT `id` FROM `my_table` WHERE `parent_id` IS NULL AND `alias` = 'users';-- 1
SELECT `id` FROM `my_table` WHERE `parent_id` = 1 AND `alias` = 'admins';   -- 8
SELECT `id` FROM `my_table` WHERE `parent_id` = 8 AND `alias` = 'nickf';    -- 37

但后来我意识到我可以用一个查询来完成,使用一个可变数量的嵌套:

SELECT `id` FROM `my_table` WHERE `parent_id` = (
    SELECT `id` FROM `my_table` WHERE `parent_id` = (
        SELECT `id` FROM `my_table`
        WHERE `parent_id` IS NULL AND `alias` = 'users'
    ) AND `alias`  = 'admins'
) AND `alias` = 'nickf';

由于子查询的数量取决于路径中的步骤数量,我是否会遇到 子查询太多 ?(即使有这样的事情)

有没有更好/更聪明的方法来执行这个查询?

2 回复 | 直到 14 年前

lexu 14 年前

这行吗?

select r0.id 
  from my_table as r0 
  join my_table as r1 on(r0.parent_id = r1.id) 
  join my_table as r2 on(r1.parent_id = r2.id)
 where r0.alias='nickf'
   and r1.alias='admins'
   and r2.alias='users'
   and r2.parent_id is null

在我看来,实际上并不需要嵌套的子查询。

还是我错了,错过了什么?

zbateson 14 年前

我自己也在想这个问题,并且在寻找一些不会随着你深入而变慢的东西(这意味着上面两个选项都有更多的层次)。我对“我的版本”的问题是,它必须在将结果缩小到你实际搜索的结果之前创建所有可能的路径…所以我认为lexu的版本应该比我的更好,即使是非常大的嵌套,因为它是一个简单的连接,但我希望有人可以看到它,并希望进一步扩展它。

此外,这种方法绝对可以从存储过程和/或其中“路径”部分的视图(不带HAVING子句)中获益。也许有了这些,这是一个更好的解决方案,但不幸的是,目前我对SQL性能的了解还不够,不能肯定。我可以说,当数据(可能的路径组合的数量)变大时,我的速度会变慢,但是对于视图(因为结果被缓存,并使用它缩小范围),它看起来很快(我发现的最大数据集总数为370,在某个时刻,我将创建一个更大的集进行测试)。

SELECT node.id, GROUP_CONCAT(parent.alias
                 ORDER BY parent.lft SEPARATOR '.') AS path_name
FROM my_table AS node, my_table AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rght
GROUP BY node.id HAVING path_name = 'users.admins.nickf'