在MySQL中使用分层结构检索数据

Question

鉴于下表

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，我想找到图像，如果没有图像，我想找到父母的图像，如果不存在，我想转到祖父母的图像…直到点击默认图片。

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

干杯!

Answer 1

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

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

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

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

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

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

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

您可以在我的演讲《SQL中的面向对象的实用模型》或即将出版的《SQL反模式：避免数据库编程的陷阱》一书中看到比较这些方法的更多信息。