在MySQL表中获取树路径 [英] Get tree path in MySQL table
本文介绍了在MySQL表中获取树路径的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
邻接列表也许是管理MySQL数据库中分层数据的最简单方法模型.是的,给每个节点一个父节点:
Perhaps the easiest approach to manage hierarchical data in MySQL databases is the adjacency list model. It is, give to every node a parent:
CREATE TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent INT DEFAULT NULL);
很容易获得父节点,或者即使有最大树深度,也可以使用以下方法获得整个树:
It is easy to get the parent node, or even if there is a maximum tree depth, you can get the whole tree using this:
SELECT CONCAT_WS('/', `t3`.`name`, `t2`.`name`, `t1`.`name`) AS `path`
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'xxxxx';
在许多情况下这就足够了,但是如何针对深度超过3个节点的树推广该解决方案呢? IE.您的路径可能类似于电子/音频/发射器/FM/摩托罗拉".
That's enough in many cases, but how can you generalize this solution for trees deeper than 3 nodes? I.e. You may have a path like "Electronics/Audio/Transmiter/FM/Motorola".
只有一个查询可以吗?
推荐答案
这是完成任务的简单非递归存储过程:
here's a simple non recursive stored procedure that does the job:
drop table if exists employees;
create table employees
(
emp_id smallint unsigned not null auto_increment primary key,
name varchar(255) not null,
boss_id smallint unsigned null,
key (boss_id)
)
engine = innodb;
insert into employees (name, boss_id) values
('f00',null),
('ali later',1),
('megan fox',1),
('jessica alba',3),
('eva longoria',3),
('keira knightley',5),
('liv tyler',6),
('sophie marceau',6);
drop procedure if exists employees_hier;
delimiter #
create procedure employees_hier
(
in p_emp_id smallint unsigned
)
begin
declare v_done tinyint unsigned default(0);
declare v_dpth smallint unsigned default(0);
create temporary table hier(
boss_id smallint unsigned,
emp_id smallint unsigned,
depth smallint unsigned
)engine = memory;
insert into hier select boss_id, emp_id, v_dpth from employees where emp_id = p_emp_id;
/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */
create temporary table emps engine=memory select * from hier;
while not v_done do
if exists( select 1 from employees e inner join hier on e.boss_id = hier.emp_id and hier.depth = v_dpth) then
insert into hier select e.boss_id, e.emp_id, v_dpth + 1
from employees e inner join emps on e.boss_id = emps.emp_id and emps.depth = v_dpth;
set v_dpth = v_dpth + 1;
truncate table emps;
insert into emps select * from hier where depth = v_dpth;
else
set v_done = 1;
end if;
end while;
select
e.emp_id,
e.name as emp_name,
p.emp_id as boss_emp_id,
p.name as boss_name,
hier.depth
from
hier
inner join employees e on hier.emp_id = e.emp_id
left outer join employees p on hier.boss_id = p.emp_id;
drop temporary table if exists hier;
drop temporary table if exists emps;
end #
delimiter ;
-- call this sproc from your php
call employees_hier(1);
这篇关于在MySQL表中获取树路径的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文