SQL树遍历 [英] SQL tree traversal

问题描述

我不完全确定我是命名这个权利，但请与我保持联系。

I am not totally sure I am naming this right, but please bear with me.

我想知道是否可以在SQL（MySQL）具体来说）：
假设我们有如下表所示的数据库中的树状数据：

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    |                |

所以每行都有一个父级，除了'root'行，每行都有子项，除了

So each row has a parent, except for the 'root' row and each row has children except for leaf rows.

是否可以使用单独的SQL查找任何给定行的所有后代？

Is it possible to find all descendants of any given row utilizing solely SQL?

推荐答案

可以使用单独的SQL来获取所有后代，但不能在单个查询中。但我确定你想出来了我假设你的意思是要在一个单一的查询中执行。

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.

您可能有兴趣阅读一些替代设计来存储树结构，这样可以让您获取所有后代使用单个SQL查询。请参阅我的演示文稿使用SQL和PHP分层数据的模型。

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.

您还可以使用递归SQL查询与其他品牌的数据库（例如PostgreSQL），但MySQL目前不支持此功能。

You can also use recursive SQL queries with other brands of database (e.g. PostgreSQL), but MySQL does not currently support this feature.

这篇关于SQL树遍历的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！