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

问题描述

我想仅使用 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.

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