在 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，我想找到图像，如果没有图像，我想找到父母的图像，如果不存在，我想转到祖父母的图像...直到我们点击默认图像。
做到这一点最有效的方法是什么？我知道 SQL 并不是真正为分层值设计的，但这就是我需要做的。
干杯!
Given the following table
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
I am wanting to do the following:
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.
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.
Cheers!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
MySQL 缺少递归查询，而递归查询是标准 SQL 的一部分。许多其他品牌的数据库支持此功能，包括 PostgreSQL（请参阅 http:// /www.postgresql.org/docs/8.4/static/queries-with.html）。
MySQL 中有多种处理分层数据的技术。
还有一些巧妙的技术来存储分层数据，以便您可以查询它们：
路径枚举存储每个节点的祖先列表。例如，示例中的照片 5 将存储“0-2-4-5”。您可以通过搜索其路径与“%”连接的节点与带有
LIKE谓词的 5 路径匹配的节点来搜索祖先。嵌套集是一种复杂但巧妙的技术，由 Joe Celko 在他的文章和他的书“Smarties SQL 中的树和层次结构”中推广。也有许多关于它的在线博客和文章。查询树很容易，但查询直接子节点或父节点很难，插入或删除节点也很难。
闭包表涉及将每个祖先/后代关系存储在单独的表中。如果添加 pathlength 列，则可以轻松查询树，轻松插入和删除，并且轻松查询直系父级或子级。
您可以在我的演示文稿中查看比较这些方法的更多信息 实用对象- SQL 中的面向模型 或我即将出版的书 SQL Antipatterns Volume 1：避免数据库编程的陷阱。
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).
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:
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
LIKEpredicate.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.
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.
You can see more information comparing these methods in my presentation Practical Object-Oriented Models in SQL or my upcoming book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.
也许管理 MySQL 中的分层数据有所帮助。
Perhaps Managing Hierarchical Data in MySQL helps.