从MySQL中的分层数据生成基于深度的树（无CTE）

嗨，好几天以来，我一直在MySQL中解决这个问题，但是我无法弄清楚。大家有什么建议吗？

基本上，我有一个类别表，其域如下：id，name（类别名称）和parent（类别父级的ID）。

示例数据：

1  Fruit        0
2  Apple        1
3  pear         1
4  FujiApple    2
5  AusApple     2
6  SydneyAPPLE  5
....

有很多级别，可能超过3个级别。我想创建一个根据层次结构将数据分组的sql查询：父级>子级>孙子级>等。

它应该输出树形结构，如下所示：

1 Fruit 0
 ^ 2 Apple 1
   ^ 4 FujiApple 2
   - 5 AusApple 2
     ^ 6 SydneyApple 5
 - 3 pear 1

我可以使用单个SQL查询吗？我尝试并起作用的替代方法如下：

SELECT * FROM category WHERE parent=0

之后，我再次遍历数据，并选择了parent = id所在的行。这似乎是一个不好的解决方案。因为它是mySQL，所以不能使用CTE。

仍在阅读和理解所有解决方案，不确定选择哪一个。

太糟糕了，您没有使用MSSQL-HierachyId功能可以解决此问题，而且速度非常快。

stackoverflow.com/questions/4048151 / ...

在我的答案中添加了一些其他信息:)

#1 楼

如果使用存储过程，则可以从php到mysql的单个调用中完成：

示例调用

mysql> call category_hier(1);

+--------+---------------+---------------+----------------------+-------+
| cat_id | category_name | parent_cat_id | parent_category_name | depth |
+--------+---------------+---------------+----------------------+-------+
|      1 | Location      |          NULL | NULL                 |     0 |
|      3 | USA           |             1 | Location             |     1 |
|      4 | Illinois      |             3 | USA                  |     2 |
|      5 | Chicago       |             3 | USA                  |     2 |
+--------+---------------+---------------+----------------------+-------+
4 rows in set (0.00 sec)


$sql = sprintf("call category_hier(%d)", $id);

希望有帮助：）

完整脚本

测试表结构：

drop table if exists categories;
create table categories
(
cat_id smallint unsigned not null auto_increment primary key,
name varchar(255) not null,
parent_cat_id smallint unsigned null,
key (parent_cat_id)
)
engine = innodb;

测试数据：

insert into categories (name, parent_cat_id) values
('Location',null),
   ('USA',1), 
      ('Illinois',2), 
      ('Chicago',2),  
('Color',null), 
   ('Black',3), 
   ('Red',3);

步骤：

drop procedure if exists category_hier;

delimiter #

create procedure category_hier
(
in p_cat_id smallint unsigned
)
begin

declare v_done tinyint unsigned default 0;
declare v_depth smallint unsigned default 0;

create temporary table hier(
 parent_cat_id smallint unsigned, 
 cat_id smallint unsigned, 
 depth smallint unsigned default 0
)engine = memory;

insert into hier select parent_cat_id, cat_id, v_depth from categories where cat_id = p_cat_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 categories p inner join hier on p.parent_cat_id = hier.cat_id and hier.depth = v_depth) then

        insert into hier 
            select p.parent_cat_id, p.cat_id, v_depth + 1 from categories p 
            inner join tmp on p.parent_cat_id = tmp.cat_id and tmp.depth = v_depth;

        set v_depth = v_depth + 1;          

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

    else
        set v_done = 1;
    end if;

end while;

select 
 p.cat_id,
 p.name as category_name,
 b.cat_id as parent_cat_id,
 b.name as parent_category_name,
 hier.depth
from 
 hier
inner join categories p on hier.cat_id = p.cat_id
left outer join categories b on hier.parent_cat_id = b.cat_id
order by
 hier.depth, hier.cat_id;

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

end #

测试运行：

delimiter ;

call category_hier(1);

call category_hier(2);

有些使用Yahoo geoplanet进行性能测试将数据放置在表中，因此表中有560万行（位置），让我们看看如何从php调用邻接表实现/存储过程来处理以下内容：。

drop table if exists geoplanet_places;
create table geoplanet_places
(
woe_id int unsigned not null,
iso_code  varchar(3) not null,
name varchar(255) not null,
lang varchar(8) not null,
place_type varchar(32) not null,
parent_woe_id int unsigned not null,
primary key (woe_id),
key (parent_woe_id)
)
engine=innodb;

mysql> select count(*) from geoplanet_places;
+----------+
| count(*) |
+----------+
|  5653967 |
+----------+

总体上，我对那些寒冷的运行时感到非常满意，因为我什至不会开始考虑将数万行数据返回到我的前端，但是宁愿构建树来动态获取每个调用仅几个级别。哦，以防万一您以为innodb的速度要比myisam慢-我测试过的myisam实现在所有方面都慢了一倍。

更多内容在这里：http://pastie.org/1672733

希望这会有所帮助:)

我担心此方法会遇到一些严重的性能问题。

–CyberDude
2011年3月13日在17:46

不要害怕-它不会。

–乔恩·布莱克
2011年3月13日在18:26

我正在尝试这种方法，编写自己的方法，那么也许我会检查出处理时间

– bluedream
2011-3-14在0:10

您可以使用以下位置的Yahoo GeoPlanet数据进行一些性能和压力测试：developer.yahoo.com/geo/geoplanet/data

–乔恩·布莱克
2011-3-14在21:53

很酷的解决方案。我对其进行了一些修改，以支持为名称建立“完整路径”，并接受NULL id参数来打印所有父母及其子女。代码在gist.github.com/jdmullin/9377818

–杰里米·穆林（Jeremy Mullin）
2014年5月5日22:20

#2 楼

在RDBMS中存储分层数据有两种常用的方法：邻接表（正在使用）和嵌套集。在MySQL的“管理层次数据”中有关于这些替代方案的很好的文章。您只能使用嵌套集模型在单个查询中执行所需的操作。但是，嵌套集模型使更新层次结构的工作量更大，因此您需要根据您的操作需求来考虑取舍。

#3 楼

您无法使用单个查询来实现。在这种情况下，您的分层数据模型无效。我建议您尝试另外两种在数据库中存储层次结构数据的方法：MPTT模型或“谱系”模型。使用这些模型中的任何一个，您都可以一次选择所需的内容。

这里有一篇文章，提供了更多详细信息：http://articles.sitepoint.com/article/hierarchical-data-数据库

这是沿袭模型的描述。

–特德·霍普（Ted Hopp）
2011年3月13日在17:46

为什么要删除MySQL标签？

–马丁·史密斯
2011-3-14在0:03

这不是特定于MySQL的。这是一般的SQL主题。

–CyberDude
2011-3-14在6:52

@CyberDude-但是，例如，如果OP在SQL Server上，则可以使用递归CTE来实现。有人确实浪费了时间提供这样的答案，直到他们发现OP在MySQL上才将其删除。

–马丁·史密斯
2011-3-14在12:36

@Martin：实际上所有其他主要的DBMS支持递归CTE-MySQL除外

– a_horse_with_no_name
2011-3-14在12:39

#4 楼

线性方式：

我正在使用一个丑陋的函数在简单的字符串字段中创建树。

/              topic title
/001           message 1
/002           message 2
/002/001       reply to message 2
/002/001/001/  reply to reply
/003           message 3
etc...

可以使用该表使用简单的SQL查询来选择树顺序中的所有行：select * from morum_messages where m_topic=1234 order by m_linear asc只是选择父线性（和子线性）并根据需要计算字符串。

select M_LINEAR FROM forum_messages WHERE m_topic = 1234 and M_LINEAR LIKE '{0}/___' ORDER BY M_LINEAR DESC limit 0,1  
/* {0} - m_linear of the parent message*/

INSERT很简单，如删除邮件，或线性删除父级所有答复。

编程黑洞网

从MySQL中的分层数据生成基于深度的树（无CTE）

评论

#1 楼

评论

#2 楼

#3 楼

评论

#4 楼