問題描述
多年來我一直在 MySQL 中解決這個問題，但是我無法弄清楚.大家有什么建議嗎?
Hi For many days I have been working on this problem in MySQL, however I can not figure it out. Do any of you have suggestions?
基本上，我有一個類別表，其中包含以下域:id、name(類別名稱)和 parent(父項的 id類別).
Basically, I have a category table with domains like: id, name (name of category), and parent (id of parent of the category).
示例數據:
1 Fruit 0
2 Apple 1
3 pear 1
4 FujiApple 2
5 AusApple 2
6 SydneyAPPLE 5
....
有很多級別，可能超過3個級別.我想創建一個 sql 查詢，根據他的層次結構對數據進行分組:父 > 子 > 孫子 > 等等.
There are many levels, possibly more than 3 levels. I want to create an sql query that groups the datas according to he hierarchy: parent > child > grandchild > etc.
它應該輸出樹結構，如下:
It should output the tree structure, as follows:
1 Fruit 0
^ 2 Apple 1
^ 4 FujiApple 2
- 5 AusApple 2
^ 6 SydneyApple 5
- 3 pear 1
我可以使用單個 SQL 查詢來執行此操作嗎?我嘗試過并且確實有效的替代方法如下:
Can I do this using a single SQL query? The alternative, which I tried and does work, is the following:
SELECT * FROM category WHERE parent=0
此后，我再次遍歷數據，并選擇 parent=id 的行.這似乎是一個糟糕的解決方案.因為是mySQL，所以不能使用CTE.
After this, I loop through the data again, and select the rows where parent=id. This seems like a bad solution. Because it is mySQL, CTEs cannot be used.
推薦答案
如果您使用存儲過程，您可以在從 php 到 mysql 的單個調用中完成:
You can do it in a single call from php to mysql if you use a stored procedure:
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 位置數據的性??能測試
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 |
+----------+
表中有 560 萬行(位置)，讓我們看看從 php 調用的鄰接列表實現/存儲過程是如何處理的.
so that's 5.6 million rows (places) in the table let's see how the adjacency list implementation/stored procedure called from php handles that.
1 records fetched with max depth 0 in 0.001921 secs
250 records fetched with max depth 1 in 0.004883 secs
515 records fetched with max depth 1 in 0.006552 secs
822 records fetched with max depth 1 in 0.009568 secs
918 records fetched with max depth 1 in 0.009689 secs
1346 records fetched with max depth 1 in 0.040453 secs
5901 records fetched with max depth 2 in 0.219246 secs
6817 records fetched with max depth 1 in 0.152841 secs
8621 records fetched with max depth 3 in 0.096665 secs
18098 records fetched with max depth 3 in 0.580223 secs
238007 records fetched with max depth 4 in 2.003213 secs
總的來說，我對那些冷運行時非常滿意，因為我什至不會考慮將數萬行數據返回到我的前端，而是寧愿構建樹，每次調用只獲取幾個級別.哦，以防萬一你認為 innodb 比 myisam 慢——我測試的 myisam 實現在所有方面都慢了兩倍.
Overall i'm pretty pleased with those cold runtimes as I wouldn't even begin to consider returning tens of thousands of rows of data to my front end but would rather build the tree dynamically fetching only several levels per call. Oh and just incase you were thinking innodb is slower than myisam - the myisam implementation I tested was twice as slow in all counts.
更多內容:http://pastie.org/1672733
希望這有幫助:)
這篇關于從 MySQL 中的分層數據生成基于深度的樹(無 CTE)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！