深度嵌套的子查询，用于遍历MySQL中的树-Java 学习之路

我在我的数据库中有一个表，我使用混合嵌套集（MPTT）模型（具有 lft 和 rght 值的模型）和邻接列表模型（在每个节点上存储 parent_id ）存储树结构 .

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

如果有人对如何利用它有一个好主意，这个问题不会留下来 .

我想将别名路径转换为特定节点 . 例如： "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 回答

这有用吗？

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

在我看来，并不需要嵌套的子查询 .

或者我错了，错过了什么？

回复于 2024-05-09T00:41:30+08:00

2
我自己也在想这个，并且正在寻找一些随着你走得更深而没有变慢的东西（意味着上面两个选项中的更多级别 . ）我对“我的版本”的问题是，它必须创建每一个可能的路径在它将结果缩小到你实际搜索的那个之前...所以我认为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'
```
回复于 2024-05-09T00:41:30+08:00

深度嵌套的子查询，用于遍历MySQL中的树

2 回答

相关问题