MySQL递归树搜索 [英] MySQL recursive tree search

问题描述

我有一个带有名称树的数据库，该名称树可以总共下降9级，而且我需要能够从分支的任何点向下搜索该树的信号分支.

I have a database with a tree of names that can go down a total of 9 levels deep and I need to be able to search down a signal branch of the tree from any point on the branch.

数据库:

+----------------------+
| id |  name  | parent |
+----------------------+
| 1  |  tom   |   0    |
| 2  |  bob   |   0    |
| 3  |  fred  |   1    |
| 4  |  tim   |   2    |
| 5  |  leo   |   4    |
| 6  |  sam   |   4    |
| 7  |  joe   |   6    |
| 8  |  jay   |   3    |
| 9  |  jim   |   5    |
+----------------------+

树:

tom
 fred
  jay
bob
 tim
  sam
   joe
  leo
   jim

例如:

如果我从用户"bob"中搜索"j"，我应该只会得到"joe"和"jim".如果我以"leo"形式搜索"j"，我应该只会得到"jim".

If I search "j" from the user "bob" I should get only "joe" and "jim". If I search "j" form "leo" I should only get "jim".

我想不出任何简单的方法来完成此工作，因此我们将不胜感激.

I can't think of any easy way do to this so any help is appreciated.

推荐答案

您应该真正考虑使用修改后的预排序树遍历，使此类查询更加容易.这是用MPTT表示的表格.我离开了父字段，因为它使某些查询更加容易.

You should really consider using the Modified Preorder Tree Traversal which makes such queries much easier. Here's your table expressed with MPTT. I have left the parent field, as it makes some queries easier.

+----------------------+-----+------+
| id |  name  | parent | lft | rght |
+----------------------+-----+------+
| 1  |  tom   |   0    |  1  |   6  |
| 2  |  bob   |   0    |  7  |  18  |
| 3  |  fred  |   1    |  2  |   5  |
| 4  |  tim   |   2    |  8  |  17  |
| 5  |  leo   |   4    | 12  |  15  |
| 6  |  sam   |   4    |  9  |  16  |
| 7  |  joe   |   6    | 10  |  11  |
| 8  |  jay   |   3    |  3  |   4  | 
| 9  |  jim   |   5    | 13  |  14  |
+----------------------+-----+------+

要从用户bob中搜索j，您将lft和rght值用于bob:

To search j from user bob you'd use the lft and rght values for bob:

SELECT * FROM table WHERE name LIKE 'j%' AND lft > 7 AND rght < 18

实施逻辑来更新lft和rght以添加，删除和重新排序节点可能是一个挑战(提示:如果可以，请使用现有的库)，但是查询将轻而易举.

Implementing the logic to update lft and rght for adding, removing and reordering nodes can be a challenge (hint: use an existing library if you can) but querying will be a breeze.

这篇关于MySQL递归树搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！