MySQL递归树搜索

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

+----------------------+
| 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 \”。如果我搜索\“ j \”形式\“ leo \”，我应该只会得到\“ jim \”。我想不出任何简单的方法来完成此工作，因此我们将不胜感激。

已邀请:

6 个回复

呢率篓舍烫

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

+----------------------+-----+------+
| 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，您将对bob使用lft和rght值：

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

实现逻辑来更新ѭ5和ѭ6以增加，删除和重新排序节点是一个挑战（提示：如果可以，请使用现有的库），但是查询将很容易。

芳菱挨啡

没有很好/简单的方法可以做到这一点；数据库不能很好地支持树型数据结构。您将需要逐级进行工作以从子级到父级修剪结果，或者创建一个视图，该视图提供给定节点的所有9代，并在后代上使用OR进行匹配。

臂哦

您是否考虑过使用递归循环？我在cmign上建立了一个cms循环，使我可以在站点树中的任何地方启动，然后依次过滤所有子代>大子代>大子代等。此外，它还能使sql迅速缩短反对许多复杂的联接的查询。在您的情况下可能需要进行一些修改，但我认为它可以工作。

/**
 * build_site_tree
 *
 * @return void
 * @author Mike Waites
**/
public function build_site_tree($parent_id)
{
    return $this->find_children($parent_id);
}

/** end build_site_tree **/

// -----------------------------------------------------------------------

/**
 * find_children
 * Recursive loop to find parent=>child relationships
 *
 * @return array $children
 * @author Mike Waites
**/
public function find_children($parent_id)
{
    $this->benchmark->mark(\'find_children_start\');

    if(!class_exists(\'Account_model\'))
            $this->load->model(\'Account_model\');

    $children = $this->Account_model->get_children($parent_id);

    /** Recursively Loop over the results to build the site tree **/
    foreach($children as $key => $child)
    {
        $childs = $this->find_children($child[\'id\']);

        if (count($childs) > 0)
                $children[$key][\'children\'] = $childs;
    }

    return $children;

    $this->benchmark->mark(\'find_children_end\');
}

/** end find_children **/

如您所见，这是一个非常简化的版本，请记住，它已内置在codeigniter中，因此您需要对其进行修改以适应套件，但是基本上我们有一个循环，每次循环调用它都会自己添加到数组中。只要您首先拥有parent_id，就可以获取整棵树，甚至从树中的某个点开始！希望这可以帮助

倪蕊悲潍

新的\“ recursive with \\”结构可以完成这项工作，但是我不知道MySQL是否支持它（至今）。

with recursive bobs(id) as (
   select id from t where name = \'bob\'
   union all
   select t.id from t, bobs where t.parent_id = bobs.id
)
select t.name from t, bobs where t.id = bobs.id
and name like \'j%\'

哭木算

没有单个SQL查询会以树格式返回数据-您需要进行处理才能以正确的顺序遍历数据。一种方法是查询MySQL以返回MPTT： SELECT * FROM table ORDER BY父级asc; 树的根将是表的第一项，其子项将是下一个，依此类推，树被“广度优先”列出（以增加深度的层为单位）然后使用PHP处理数据，将其变成保存数据结构的对象。另外，您可以实现给定节点的MySQL搜索功能，以递归方式搜索并返回其所有后代的表或其所有祖先的表。由于这些过程往往很慢（递归，返回太多的数据，然后再由其他条件过滤），因此仅当您知道自己不会一次又一次地查询此类数据时，才想这样做您知道数据集仍然很小（9层深度，有多宽？）

踩什不

您可以使用存储过程执行以下操作：呼叫范例

mysql> call names_hier(1, \'a\');
+----+----------+--------+-------------+-------+
| id | emp_name | parent | parent_name | depth |
+----+----------+--------+-------------+-------+
|  2 | ali      |      1 | f00         |     1 |
|  8 | anna     |      6 | keira       |     4 |
+----+----------+--------+-------------+-------+
2 rows in set (0.00 sec)

mysql> call names_hier(3, \'k\');
+----+----------+--------+-------------+-------+
| id | emp_name | parent | parent_name | depth |
+----+----------+--------+-------------+-------+
|  6 | keira    |      5 | eva         |     2 |
+----+----------+--------+-------------+-------+
1 row in set (0.00 sec)

$sqlCmd = sprintf(\"call names_hier(%d,\'%s\')\", $id, $name);  // dont forget to escape $name
$result = $db->query($sqlCmd);

完整脚本

drop table if exists names;
create table names
(
id smallint unsigned not null auto_increment primary key,
name varchar(255) not null,
parent smallint unsigned null,
key (parent)
)
engine = innodb;

insert into names (name, parent) values
(\'f00\',null), 
  (\'ali\',1), 
  (\'megan\',1), 
      (\'jessica\',3), 
      (\'eva\',3), 
         (\'keira\',5), 
            (\'mandy\',6), 
            (\'anna\',6);

drop procedure if exists names_hier;

delimiter #

create procedure names_hier
(
in p_id smallint unsigned,
in p_name varchar(255)
)
begin

declare v_done tinyint unsigned default(0);
declare v_dpth smallint unsigned default(0);

set p_name = trim(replace(p_name,\'%\',\'\'));

create temporary table hier(
 parent smallint unsigned, 
 id smallint unsigned, 
 depth smallint unsigned
)engine = memory;

insert into hier select parent, id, v_dpth from names where id = p_id;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table tmp engine=memory select * from hier;

while not v_done do

    if exists( select 1 from names n inner join tmp on n.parent = tmp.id and tmp.depth = v_dpth) then

        insert into hier select n.parent, n.id, v_dpth + 1 
            from names n inner join tmp on n.parent = tmp.id and tmp.depth = v_dpth;

        set v_dpth = v_dpth + 1;            

        truncate table tmp;
        insert into tmp select * from hier where depth = v_dpth;

    else
        set v_done = 1;
    end if;

end while;


select 
 n.id,
 n.name as emp_name,
 p.id as parent,
 p.name as parent_name,
 hier.depth
from 
 hier
inner join names n on hier.id = n.id
left outer join names p on hier.parent = p.id
where
 n.name like concat(p_name, \'%\');

drop temporary table if exists hier;
drop temporary table if exists tmp;

end #

delimiter ;

-- call this sproc from your php

call names_hier(1, \'a\');
call names_hier(3, \'k\');

要回复问题请先登录或注册

MySQL递归树搜索

6 个回复

发起人

recursive_query

php

mysql

问题状态

MySQL递归树搜索

与内容相关的链接

6 个回复

发起人

recursive_query

php

mysql

问题状态