我有一个树的层次结构,它内置到一个表中,parent_id指向前一个根节点。
我正在遍历所有根节点(root1, root2),并且为root1和child1设置路径为root1或root1/child1。为了找到child1的路径,我必须至少调用2次才能形成路径。是否有一种有效的方法来填充路径,因为我们要处理大量的根节点和子节点,它们嵌套在5-7层深。
create table foo (id, name, parent_id, path)
insert into foo (1, "root1', null, null)
insert into foo (2, "child1', 1, null)
root1 (path = null)
child1 (path = root1)
subchild1 (path = root1/child1)
root2
child2
subchild2
您可以使用您在问题中提到的存储过程,因为嵌套深度可以达到7级。
存储过程CREATE PROCEDURE updatePath()
BEGIN
declare cnt, n int;
select count(*) into n from foo where parent_id is null;
update foo a, foo b set a.path = b.name where b.parent_id is null and a.parent_id = b.id;
select count(*) into cnt from foo where path is null;
while cnt > n do
update foo a, foo b set a.path = concat(b.path, '/', b.name) where b.path is not null and a.parent_id = b.id;
select count(*) into cnt from foo where path is null;
end while;
END//
为了检查实际记录,我们只打印路径列
中具有空值的普通记录select * from foo
结果:
| ID | NAME | PARENT_ID | PATH |
------------------------------------------
| 1 | root1 | (null) | (null) |
| 2 | child1 | 1 | (null) |
| 3 | subchild1 | 2 | (null) |
| 4 | child2 | 1 | (null) |
| 5 | child3 | 1 | (null) |
| 6 | subchild2 | 4 | (null) |
| 7 | subsubchild1 | 6 | (null) |
调用过程:
call updatepath
程序执行后的结果:
select * from foo
结果:
| ID | NAME | PARENT_ID | PATH |
----------------------------------------------------------
| 1 | root1 | (null) | (null) |
| 2 | child1 | 1 | root1 |
| 3 | subchild1 | 2 | root1/child1 |
| 4 | child2 | 1 | root1 |
| 5 | child3 | 1 | root1 |
| 6 | subchild2 | 4 | root1/child2 |
| 7 | subsubchild1 | 6 | root1/child2/subchild2 |
SQLFIDDLE
希望对您有所帮助....
我真的很喜欢修改的预购树遍历。它允许您在单个查询中获得整个树层次结构。这里有一个详细的教程:http://www.sitepoint.com/hierarchical-data-database-2/
如果您对MPTT有任何疑问,请告诉我,我很乐意帮助您!
虽然在单个调用中严格不可能,但您可以隐藏多个调用,但将它们放入您从SQL调用的MySQL函数中,该函数返回父路径。
虽然这可能比在脚本中更有效,但我不期望它那么有效。
如果最大级别数是固定的,您可以使用join如下:-
SELECT foo.id, foo.name, CONCAT_WS(',', d.name, c.name, b.name, a.name)
FROM foo
LEFT OUTER JOIN foo a ON foo.parent_id = a.id
LEFT OUTER JOIN foo b ON a.parent_id = b.id
LEFT OUTER JOIN foo c ON b.parent_id = c.id
LEFT OUTER JOIN foo d ON c.parent_id = d.id
虽然这将工作,但它是相当有限的(即,如果最大数量的级别改变,你将不得不改变每一点SQL使用这个),加上如果级别的数量不是很小,它将成为一个不可读的混乱。
您可以考虑添加一个包含从每个树根到叶子的所有路径的闭包表。在SQL中维护图的传递闭包(从1999年开始)描述了一些理论背景。
关于分层数据的stackoverflow审查问题描述了许多替代方法。在那里,Tegiri Nenashi指出了一个全面的参考书目,其中包括rdbms中的分层数据。
闭包表的优点是查询效率高,而且解决方案不会影响当前的数据结构。您需要扩展您的关闭表,并在修改森林时维护它。
表示表中有大量具有短路径的项。这使得闭包表的性能在适当的索引下保持近似线性。您还可以保留到关闭表中每个节点的路径,以避免重新计算。该方法对每个操作都有固定数量的查询,并且支持任意深度的层次结构。
您应该使用嵌套集模型http://en.wikipedia.org/wiki/Nested_set_model