如何递归查找孩子的所有ID?
我想仅使用 MySQL 获取树中子项的所有 ID.
I would like to get all IDs from children in a tree with MySQL only.
我有一张这样的桌子:
ID parent_id name
1 0 cat1
2 1 subcat1
3 2 sub-subcat1
4 2 sub-subcat2
5 0 cat2
现在我正在尝试递归获取 cat1 (2,3,4) 的所有子 ID.有什么方法可以实现吗?
Now I'm trying to get all child IDs for cat1 (2,3,4) recursively. Is there any way how to achieve that?
推荐答案
有两种基本方法可以做到这一点:邻接列表和嵌套列表.看看在 MySQL 中管理分层数据.
There are two basic methods for doing this: adjacency lists and nested lists. Take a look at Managing Hierarchical Data in MySQL.
你拥有的是一个邻接表.不，没有一种方法可以使用单个 SQL 语句递归地获取所有后代.如果可能，只需将它们全部抓取并在代码中全部映射.
What you have is an adjacency list. No there isn't a way of recursively grabbing all descendants with a single SQL statement. If possible, just grab them all and map them all in code.
嵌套集可以做你想做的事，但我倾向于避免它，因为插入记录的成本很高，而且容易出错.
Nested sets can do what you want but I tend to avoid it because the cost of inserting a record is high and it's error-prone.
相关文章