MySQL：如何递归地找到所有的孩子ID？ [英] MySQL: How to find all IDs of children recursively?

问题描述

我有一个这样的表：

$

b $ b

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

现在我想获得cat1的所有子ID ，3,4）。

解决方案

有两种基本的方法：邻接表和嵌套列表。请查看在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?

Thanks for all your help!

解决方案

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.

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