SQL树遍历
我不确定我的命名是否正确，但请耐心等待。
我想知道是否可以在 SQL（特别是 MySQL）中执行类似的操作： 假设我们有保存在数据库中的树状数据，如下表所示：
mysql> desc data_table;
+------------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| parent_id | int(10) unsigned | YES | MUL | NULL | |
| value | text | YES | | NULL | |
因此，除了“根”行之外，每行都有一个父行，并且除了叶行之外，每行都有子行。
是否可以仅使用 SQL 找到任何给定行的所有后代？
I am not totally sure I am naming this right, but please bear with me.
I am wondering if is possible to do something like this in SQL(MySQL specifically):
Let's say we have tree-like data that is persisted in the database in the following table:
mysql> desc data_table;
+------------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| parent_id | int(10) unsigned | YES | MUL | NULL | |
| value | text | YES | | NULL | |
So each row has a parent, except for the 'root' row and each row has children except for leaf rows.
Is it possible to find all descendants of any given row utilizing solely SQL?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
可以仅使用 SQL 获取所有后代，但不能在单个查询中获取。但我相信你已经明白了；我假设您的意思是您想在单个查询中完成此操作。
您可能有兴趣阅读一些存储树结构的替代设计，这些设计确实使您能够使用单个 SQL 查询获取所有后代。请参阅我的演示文稿使用 SQL 和 PHP 构建分层数据模型。
您还可以对其他品牌的数据库（例如PostgreSQL）使用递归SQL查询，但MySQL目前不支持此功能。
It's possible to fetch all descendants utilizing solely SQL, but not in a single query. But I'm sure you figured that out; I assume you mean you want to do it in a single query.
You might be interested in reading about some alternative designs to store tree structures, that do enable you to fetch all descendants using a single SQL query. See my presentation Models for Hierarchical Data with SQL and PHP.
You can also use recursive SQL queries with other brands of database (e.g. PostgreSQL), but MySQL does not currently support this feature.
您可能更适合使用嵌套集模型（请参阅 http:// mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ - 再往下）。它的选择效率要高得多，您可以通过简单的自连接获得每个节点的完整路径。
然而，在实践中，如果您想要执行“其中深度= 3”之类的操作，或者如果表中有超过 1000 条记录，则想要显示多个节点的完整路径，那么预缓存路径和深度是一个好主意。
You're probably better of with the nested set model instead (see http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ - further down). Its far more efficient for selects and you can get the complete path to each node with a simple self join.
However, in practice it is a good idea to pre-cache path and depth if you want to do things like " where depth = 3" or want to show the complete path for multiple nodes if you have more than 1000 records in your table.
我只是问自己同样的问题。
这是我用谷歌搜索的：
http://moinne.com/blog/ronald /mysql/manage-hierarchical-data-with-mysql-stored-procedures
它适用于存储过程。
但在我看来，数据库中有这么多逻辑并不是一件好事。
I was just asking myself the same question.
This is what i googled:
http://moinne.com/blog/ronald/mysql/manage-hierarchical-data-with-mysql-stored-procedures
It works with stored procedures.
But so much logic in the DB is not a good thing in my opinion..