blocks|key|999681|text|这取决于您存储数据的方式。在MySQL.com上有一篇名为Managing+Hierarchical+Data+in+MySQL的好文章谈到了这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|999682|entityMap|0|LINK|mutability|MUTABLE|url|http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/^0|T|Z|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

That depends on how you have stored your data. There is a good article on MySQL.com called <a href="http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/" rel="nofollow noreferrer">Managing Hierarchical Data in MySQL</a> that talks about this.

blocks|key|1213992|text|文章的前半部分只涉及4个层次，后半部分不是我想要的方式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1213993|我的结构是这样的：|1213994|%2B-------------%2B----------------------%2B--------%2B
%7C+category_id+%7C+name+++++++++++++++++%7C+parent+%7C
%2B-------------%2B----------------------%2B--------%2B
%7C+++++++++++1+%7C+test+++++++++++++++++%7C+++NULL+%7C
%7C+++++++++++2+%7C+subject1+++++++++++++%7C+++1++++%7C
%7C+++++++++++3+%7C+subject2+++++++++++++%7C+++1++++%7C
%7C+++++++++++4+%7C+subject3+++++++++++++%7C+++2++++%7C
%7C+++++++++++5+%7C+subject4+++++++++++++%7C+++4++++%7C
%2B-------------%2B----------------------%2B--------%2B|code-block|syntax|javascript|1213995|我不想把事情复杂化，我想用最简单的方式来做，而是以最有效的方式来获取数据。|1213996|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

the first part of the article relates only to 4 levels, the last part is not the way I want to do it.

my structure is something like that:

<pre><code>+-------------+----------------------+--------+
| category_id | name | parent |
+-------------+----------------------+--------+
| 1 | test | NULL |
| 2 | subject1 | 1 |
| 3 | subject2 | 1 |
| 4 | subject3 | 2 |
| 5 | subject4 | 4 |
+-------------+----------------------+--------+
</code></pre>

I dont want to complicate things, I want to do it in the most simple way, but to fetch the data in the most efficient way.

blocks|key|999811|text|正如您所知道的，您可以在一个查询中获取一个数组中的所有类别：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|999812|$query+=+"SELECT+`name`,`id`+from+`table`";|code-block|syntax|javascript|999813|将其放入数组中，您可以使用一些嵌套循环来构建树。它不会很快，但很简单，而且比使用递归查询更快。此外，您可以缓存已构建的树，而不必每次都重新构建它。|999814|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Well, you can fetch all the categories in an array in just the one query, as you know:

<pre><code>$query = "SELECT `name`,`id` from `table`";
</code></pre>

Having that in an array, you can build the tree with some nested loops. It won't be fast, but is simple, and faster than using recursive queries. Also, you can cache the built tree and not having to rebuild it every time.

blocks|key|999849|text|你可以看看这个主题:昨天打开的how+to+get+the+hierarchical+menu+from+mysql也是一样的。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|999850|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/3102447/how-to-get-the-hierarchical-menu-from-mysql/^0|F|17|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

You could have a look at this topic : <a href="https://stackoverflow.com/questions/3102447/how-to-get-the-hierarchical-menu-from-mysql/">how to get the hierarchical menu from mysql</a> that was opened yesterday and is about the same thing.

blocks|key|999900|text|如果树不是太大，您可以使用一些巧妙的引用在PHP中简单地构建树。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|999901|$nodeList+=+array();
$tree+++++=+array();

$query+=+mysql_query("SELECT+category_id,+name,+parent+FROM+categories+ORDER+BY+parent");
while($row+=+mysql_fetch_assoc($query)){
++++$nodeList[$row['category_id']]+=+array_merge($row,+array('children'+=>+array()));
}
mysql_free_result($query);

foreach+($nodeList+as+$nodeId+=>+&$node)+{
++++if+(!$node['parent']+%7C%7C+!array_key_exists($node['parent'],+$nodeList))+{
++++++++$tree[]+=+&$node;
++++}+else+{
++++++++$nodeList[$node['parent']]['children'][]+=+&$node;
++++}
}
unset($node);
unset($nodeList);|code-block|syntax|javascript|999902|这将为您提供$tree中的树结构，以及相应children-slot中的子项。|offset|length|style|CODE|999903|我们已经用相当大的树(+>>+1000项)做到了这一点，而且它非常稳定，而且比MySQL中的递归查询快得多。|999904|entityMap^0|0|0|6|5|L|8|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]|$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

If the tree isn't too large, you can simply build the tree in PHP using some clever references.

<pre><code>$nodeList = array();
$tree = array();

$query = mysql_query("SELECT category_id, name, parent FROM categories ORDER BY parent");
while($row = mysql_fetch_assoc($query)){
 $nodeList[$row['category_id']] = array_merge($row, array('children' =&gt; array()));
}
mysql_free_result($query);

foreach ($nodeList as $nodeId =&gt; &amp;$node) {
 if (!$node['parent'] || !array_key_exists($node['parent'], $nodeList)) {
 $tree[] = &amp;$node;
 } else {
 $nodeList[$node['parent']]['children'][] = &amp;$node;
 }
}
unset($node);
unset($nodeList);
</code></pre>

This will give you the tree structure in <code>$tree</code> with the children in the respective <code>children</code>-slot.

We've done this with fairly large trees ( >> 1000 items) and it's very stable and a lot faster than doing recursive queries in MySQL.

blocks|key|1214181|text|从示例到每个类别，将其完整路径保存在另一个字段中：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1214182|1-1|1214183|2-+1.2|1214184|3-+1.2.3|1214185|4-+1.4|1214186|5-+1.4.5|1214187|6-+1.4.6|1214188|7-+1.4.6.7|1214189|8-8|1214190|9-9|1214191|然后在该字段上使用ORDER+BY查询|1214192|entityMap^0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Z|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|10|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|11|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|12|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|13|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|14|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|15|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|16|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|17|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|W|$]]

From your example to each category save its full path in another field: 
1 - 1 
2 - 1.2 
3 - 1.2.3 
4 - 1.4 
5 - 1.4.5 
6 - 1.4.6 
7 - 1.4.6.7 
8 - 8 
9 - 9 

and then just query with ORDER BY on that field

blocks|key|1000008|text|我的也对一个查询使用了递归...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1000009|A+recursive+method+to+storing+hierarchical+data+without+multiple+calls+to+the+database|offset|length|1000010|我也想使用递归，因为它很简单，但和您一样，我也想消除递归查询的开销。我的逻辑是，通过这种方式，您只是根据数据库结果的大小将负载从数据库移动到内存，但我没有对此进行任何真正的可伸缩性测试，所以我不知道它对非递归方法有多大影响。|1000011|entityMap|0|LINK|mutability|MUTABLE|url|http://www.henryweismann.com/?p=14^0|0|0|2E|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@$D|R|E|S|1|T]]|A|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]]]

Mine uses recursion with one query as well...

<a href="http://www.henryweismann.com/?p=14" rel="nofollow">A recursive method to storing hierarchical data without multiple calls to the database</a>

I wanted to use recursion also due to its simplicity but like you I wanted to remove the overhead of a recursive query. My logic says in this way you are just moving the load from the database to memory depending on the size of your database result but I have not done any real scalability testing on this so I don't know how much of an impact it has vs non recursive methods.

blocks|key|1000063|text|我已经为你做了一个询问。这将为您提供带有单个查询的递归类别：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1000064|SELECT+id,NAME,''+AS+subName,''+AS+subsubName,''+AS+subsubsubName+FROM+app_category+WHERE+Parent_id=0
UNION+
SELECT+b.id,a.name,b.name+AS+subName,''+AS+subsubName,''+AS+subsubsubName+FROM+app_category+AS+a+LEFT+JOIN+app_category+AS+b+ON+b.parent_id=a.id+WHERE+a.Parent_id=0+AND+b.name+IS+NOT+NULL+
UNION+
SELECT+c.id,a.name,b.name+AS+subName,c.name+AS+subsubName,''+AS+subsubsubName+FROM+app_category+AS+a+LEFT+JOIN+app_category+AS+b+ON+b.parent_id=a.id+LEFT+JOIN+app_category+AS+c+ON+c.parent_id=b.id+WHERE+a.Parent_id=0+AND+c.name+IS+NOT+NULL+
UNION+
SELECT+d.id,a.name,b.name+AS+subName,c.name+AS+subsubName,d.name+AS+subsubsubName+FROM+app_category+AS+a+LEFT+JOIN+app_category+AS+b+ON+b.parent_id=a.id+LEFT+JOIN+app_category+AS+c+ON+c.parent_id=b.id+LEFT+JOIN+app_category+AS+d+ON+d.parent_id=c.id+WHERE+a.Parent_id=0+AND+d.name+IS+NOT+NULL+
ORDER+BY+NAME,subName,subsubName,subsubsubName+|code-block|syntax|javascript|1000065|这是一个fiddle。|offset|length|1000066|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!9/b4f997/3^0|0|0|4|6|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

I have made a query for you. This will give you Recursive Category with a Single Query:

<pre><code>SELECT id,NAME,'' AS subName,'' AS subsubName,'' AS subsubsubName FROM app_category WHERE Parent_id=0
UNION 
SELECT b.id,a.name,b.name AS subName,'' AS subsubName,'' AS subsubsubName FROM app_category AS a LEFT JOIN app_category AS b ON b.parent_id=a.id WHERE a.Parent_id=0 AND b.name IS NOT NULL 
UNION 
SELECT c.id,a.name,b.name AS subName,c.name AS subsubName,'' AS subsubsubName FROM app_category AS a LEFT JOIN app_category AS b ON b.parent_id=a.id LEFT JOIN app_category AS c ON c.parent_id=b.id WHERE a.Parent_id=0 AND c.name IS NOT NULL 
UNION 
SELECT d.id,a.name,b.name AS subName,c.name AS subsubName,d.name AS subsubsubName FROM app_category AS a LEFT JOIN app_category AS b ON b.parent_id=a.id LEFT JOIN app_category AS c ON c.parent_id=b.id LEFT JOIN app_category AS d ON d.parent_id=c.id WHERE a.Parent_id=0 AND d.name IS NOT NULL 
ORDER BY NAME,subName,subsubName,subsubsubName 
</code></pre>

Here is a <a href="http://sqlfiddle.com/#!9/b4f997/3" rel="nofollow noreferrer">fiddle</a>.

I have a website with articles and sections,
each sections can have a parent section, as much as they like
for example:

<pre><code>subject 1
 -subject 2 
 --subject 3
 -subject 4
 --subject 5
 --subject 6
 ---subject 7
subject 8
subject 9
</code></pre>

etc..

Now, i want to fetch them recursively, what is the most efficient way to do it via php and mysql?

Tnx in advanced.

Recursive categories with a single query?

数据库

MySQL

我有一个网站的文章和部分，每个部分可以有一个父部分，因为他们喜欢，例如：subject 1 -subject 2  --subject 3 -subject 4 --subject 5 --subject 6 ---subject 7subject 8subject 9等等。现在，我想递归地获取它们，那么通过php和m...

问使用单个查询的递归类别？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用单个查询的递归类别？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用单个查询的递归类别？
EN