mysql - 路径枚举 mySQL 查询以创建面包屑

Question

我想从路径枚举列创建面包屑。

这是我拥有的数据集的示例。

https://spreadsheets.google.com/ccc?key=0AsGYQbeSAIgFdGRscFpsZFJpQUtfWGIwYWNUY2ktRHc&hl=en_GB&authkey=CPOuuogF

id woeid parent_woeid country_code name 语言 place_type 祖先

ancestry是枚举的路径，比如1/23424975/24554868/12602167/12696151是英国布莱顿的路径。

我希望能够通过查询 name 列来检索面包屑，并获取所有父项。

即。世界、欧洲、英格兰、[县]、[镇]、[地区]、[地点]

([] = 占位符)

数据永远不会改变，这就是为什么这张表使用邻接表和路径枚举的原因。

Answer 1

几种可能的方法。我将从你想做的，到我建议你做的。

物化路径

不幸的是，枚举路径祖先(例如亲子关系)is expensive and tricky to do with SQL only (参见关于物化路径的部分，找到“福特员工及其主管链”)。如果您提前找到了路径并且可以使用某种编程语言，那么最简单的方法是使用 IN 子句:

SELECT *
FROM woe
WHERE ancestry IN (
    '1', 
    '1/23424975', 
    '1/23424975/24554868', 
    '1/23424975/24554868/12602167', 
    '1/23424975/24554868/12602167/12696151'
)
ORDER BY LEN(ancestry)

邻接表

或者，您可以利用数据的邻接列表方面，并根据数据库使用公用表表达式或CONNECT BY(这会忽略 ancestry 列) ).查看我关于 representing hierarchical data in an RDBMS 的不同方式的问题有关使用邻接列表查询祖先的信息(请参阅问题末尾的数据库特定说明部分)。

嵌套集

您写入的数据不会改变并且您想要查询祖先。在这种情况下，嵌套集表示是完美的方法，因为仅使用 SQL 获取所需信息很容易，适用于任何数据库，而且操作成本非常低。缺点是移动和插入很昂贵。 Managing Hierarchical Data in MySQL可能是我能提供的关于它如何工作的最好解释。

我会使用 Nested Set 给定你的数据，你想做什么并且它不会改变。