如何递归查找所有孩子的ID? [英] How to find all IDs of children recursively?

问题描述

我只想从一棵树中的子级获取所有ID，并且仅使用MySQL.

我有一个这样的表:

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.有什么方法可以实现这一目标吗?

解决方案

执行此操作的基本方法有两种:邻接列表和嵌套列表.看看在MySQL中管理分层数据.

您拥有的是邻接表.没有，没有一种方法可以通过单个SQL语句递归地获取所有后代.如果可能的话，只需将它们全部捕获并在代码中将它们全部映射.

嵌套集可以执行您想要的操作，但是我倾向于避免这样做，因为插入记录的成本很高并且容易出错.

I would like to get all IDs from children in a tree with MySQL only.

I have a table like this:

ID parent_id name
1  0         cat1
2  1         subcat1
3  2         sub-subcat1
4  2         sub-subcat2
5  0         cat2

Now I'm trying to get all child IDs for cat1 (2,3,4) recursively. Is there any way how to achieve that?

解决方案

There are two basic methods for doing this: adjacency lists and nested lists. Take a look at Managing Hierarchical Data in MySQL.

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.

这篇关于如何递归查找所有孩子的ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！