MySQL-是否可以获取层次结构中的所有子项目? [英] MySQL - Is it possible to get all sub-items in a hierarchy?
问题描述
我已经看过了:
它正在谈论CTE，在MySQL中不存在.我有这样的结构:
And it is talking about CTE, which doesn't exist in MySQL. I have structure like:
category_id | parent_category_id | name
我想检索给定category_id的所有子类别ID.是否可以不获取层级然后遍历这些层级?
I want to retrieve all the sub-categories ids of a given category_id. Is this possible without grabbing a tier, then looping through those?
推荐答案
这只是一个邻接模型表吗?这样一来，在不知道最大深度的情况下就不可能进行查询.
This is just simpy a Adjacency Model table? Then it is not possible in one query without knowing the maximum depth.
值得深思的是在MySQL中管理分层数据(尽管我不主张使用嵌套集模型来定期更改数据).
Food for thought is Managing Hierarchical Data in MySQL (although I don't advocate using the Nested Set Model for data that alter regularly).
具有很多(左)连接，更具体地说:与树的最大深度一样多的左连接，将有可能在一个查询中.这就是很多人倾向于保存特定类别的深度"的原因，因此您可以过滤并限制同一张表的联接数量，使其更为合理.
With a lot of (left) joins, more specifically: with as many left joins as the maximum depth of the tree, it will be possible in one query. This is the reason a lot of people tend to save the 'depth' of a specific category, so you 'll be able to filter and limit the amount of joins to the same table to a more sane amount.
就个人而言，为了定期更改数据:我倾向于在插入/更新上配置触发器，该触发器将基于id保存/缓存节点的当前路径"(例如:路径为"12/62/28/345'，其中定界符/之间的每一步都是父节点的主键，其顺序正确(345的父级为28，28的父级为62，依此类推)，因此我可以用这样的一个联接(/用作分隔符):
Personally, for regularly altering data: I tend to configure a trigger on an insert / update, which will save / cache the current 'path' of a node based on id's (for instance: a path is '12/62/28/345', in which every step between the delimiter / is the primary key of a parent node in the correct order (345's parent is 28, 28's parent is 62, etc.)), so I can query it with just one join like this (/ used as separator):
SELECT j.*
FROM tablename o
JOIN tablename j
WHERE j.path LIKE CONCAT (o.path,'/%')
AND j.id != o.id -- skip parent asked for.
WHERE o.id = <the id of the node you're looking for>;
这篇关于MySQL-是否可以获取层次结构中的所有子项目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
