如何通过在 mysql 中查询自引用表来获得递归结果?
我有一个自引用表comments"，其中comments.replyToId REFERENCES comments.ID.
I have a self-referencing table 'comments' where comments.replyToId REFERENCES comments.ID.
我的问题是，如何查询带有自引用表的数据库以获得正确排序的结果，以便我可以在 PHP 中将结果表示为树?
My question is, how do I query a database with a self-referencing table to get a result that is properly ordered so that I can represent the result as a tree in PHP?
我试过了
select * from comments as comments_1
left join comments as comments_2
on comments_1.id = comments_2.replyToId
我正在尝试在 php 中使用这个结果
I'm trying to use the result of this in php
您不会直接从 MySQL 中得到递归结果.最近有一个类似的讨论 - 使用存储过程等的一些 RDBMS 可能是可能的，但不能使用开箱即用的 SQL(参见 如何在一个 SQL 查询中获取任意递归深度的祖先 ID?).
You're not going to get a recursive result out of MySQL directly. There was a similar discussion recently - it is maybe possible with some RDBMS using stored procedures etc, but not with out-of-the-box SQL (see How can I get ancestor ids for arbitrary recursion depth in one SQL query?).
在类似情况下我会做什么:在没有父母的情况下获取所有评论.然后，对于每条评论，获取其子项(如果您存储每个评论的深度"，则可以使用一个 SQL 查询获得所有这些子项和下一层的所有子项).将孩子存放在树结构中的适当位置，重复.
What I do instead in similar cases: Get all comments without parents. Then, for each comment, get its children (if you store the "depth" of each comment you may get all these children and all children of the next layers with one SQL query). Store the children in the appropriate place in your tree structure, repeat.
如果您需要更底层的代码，您通常需要共享一些代码、解释您的数据结构、到目前为止您尝试过的内容等，这只是一般方法.
If you need a more low-level, you'll prly need to share some code, explain your data structure, what you've tried so far etc., this is just the general approach.