在MySQL中使用分层结构检索数据 [英] Retrieving data with a hierarchical structure in MySQL

问题描述

给出下表

id    parentID   name      image
0     0                    default.jpg
1     0          Jason   
2     1          Beth      b.jpg
3     0          Layla     l.jpg
4     2          Hal     
5     4          Ben       

我要执行以下操作:

如果我搜索Ben，我想找到图像，如果没有图像，我想找到父母的图像，如果不存在，我想转到祖父母的图像...直到我们点击默认图片为止.

If I search for Ben, I would like to find the image, if there is no image, I would like to find the parent's image, if that does not exist, I would like to go to the grandparent's image... up until we hit the default image.

最有效的方法是什么?我知道SQL并不是真正为分层值设计的，但这是我需要做的.

What is the most efficient way to do this? I know SQL isn't really designed for hierarchical values, but this is what I need to do.

干杯！

推荐答案

MySQL缺少递归查询，这是标准SQL的一部分.许多其他品牌的数据库都支持此功能，包括PostgreSQL(请参见 http://www.postgresql.org/docs/8.4/static/queries-with.html ).

MySQL lacks recursive queries, which are part of standard SQL. Many other brands of database support this feature, including PostgreSQL (see http://www.postgresql.org/docs/8.4/static/queries-with.html).

在MySQL中有几种处理分层数据的技术.

There are several techniques for handling hierarchical data in MySQL.

• 最简单的方法是添加一列，以记录给定照片所属的层次结构.然后，您可以搜索属于同一层次结构的照片，将它们全部取回应用程序，并在其中找出所需的照片.就带宽而言，这有点浪费，需要您编写更多的应用程序代码，并且如果您的树上有许多节点，那就不好了.

还有一些巧妙的技术可以存储分层数据，以便您查询它们:

There are also a few clever techniques to store hierarchical data so you can query them:

• 路径枚举存储每个节点的祖先列表.例如，您的示例中的照片5将存储"0-2-4-5".您可以通过搜索路径与％"串联的节点匹配LIKE谓词来匹配5的路径来搜索祖先.

• Path Enumeration stores the list of ancestors with each node. For instance, photo 5 in your example would store "0-2-4-5". You can search for ancestors by searching for nodes whose path concatenated with "%" is a match for 5's path with a LIKE predicate.

嵌套集是乔·塞尔科(Joel Celko)在他的文章和他的《 SQL for Smarties中的树和层次结构》一书中普及的复杂技术.也有许多在线博客和有关它的文章.查询树很容易，但是查询直接的孩子或父母很困难，也很难插入或删除节点.

Nested Sets is a complex but clever technique popularized by Joe Celko in his articles and his book "Trees and Hierarchical in SQL for Smarties." There are numerous online blogs and articles about it too. It's easy to query trees, but hard to query immediate children or parents and hard to insert or delete nodes.

关闭表涉及将每个祖先/后代关系存储在单独的表中.如果添加 pathlength 列，则查询树很容易，易于插入和删除，还可以查询直接的父母或孩子.

Closure Table involves storing every ancestor/descendant relationship in a separate table. It's easy to query trees, easy to insert and delete, and easy to query immediate parents or children if you add a pathlength column.

您可以在我的演示文稿中看到比较这些方法的更多信息实用SQL中的面向对象模型或我即将出版的新书 SQL反模式:避免以下方面的陷阱数据库编程.

You can see more information comparing these methods in my presentation Practical Object-Oriented Models in SQL or my upcoming book SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

这篇关于在MySQL中使用分层结构检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！