MySQL分层存储：搜索所有父母/祖父母等。给定子节点 id 的节点？
我使用分层模型存储类别，如下所示：
CATEGORIES
id | parent_id | name
---------------------
1 | 0 | Cars
2 | 0 | Planes
3 | 1 | Hatchbacks
4 | 1 | Convertibles
5 | 2 | Jets
6 | 3 | Peugeot
7 | 3 | BMW
8 | 6 | 206
9 | 6 | 306
然后，我使用这些类别 ID 之一存储实际数据，如下所示：
CARS
vehicle_id | category_id | name
-------------------------------
1 | 8 | Really fast silver Peugeot 206
2 | 9 | Really fast silver Peugeot 306
3 | 5 | Really fast Boeing 747
4 | 3 | Another Peugeot but only in Hatchbacks category
在搜索任何此类数据时，我想找到所有子/孙/曾孙等。节点。因此，如果有人想查看所有“汽车”，他们会看到parent_id 为“Hatchbacks”的所有内容，以及parent_id 为“Peugeot”的所有内容，依此类推，达到任意级别。
因此，如果我列出类别 ID 为 1、3、6 或 8 的“真正快的标致 206”，我的查询应该能够“向上遍历”树并找到任何更高的类别，即该孩子的父母/祖父母类别。例如，在类别“8”中搜索标致的用户应该找到列出类别 6、3 或 1 的所有标致 - 所有这些都是类别 8 的后代。
例如，使用上述数据，在类别 3 中搜索“Peugeot”实际上应该找到车辆 1、2 和 4，因为车辆 1 和 2 具有返回到类别 3 的类别祖先踪迹。看到了吗？
抱歉，如果我没有解释清楚。它很难！不过还是谢谢你。
注意：我已阅读有关层次结构的 MySQL 开发文章。
I'm storing categories using a hierarchical model like so:
CATEGORIES
id | parent_id | name
---------------------
1 | 0 | Cars
2 | 0 | Planes
3 | 1 | Hatchbacks
4 | 1 | Convertibles
5 | 2 | Jets
6 | 3 | Peugeot
7 | 3 | BMW
8 | 6 | 206
9 | 6 | 306
I then store actual data with one of these category ids like so:
CARS
vehicle_id | category_id | name
-------------------------------
1 | 8 | Really fast silver Peugeot 206
2 | 9 | Really fast silver Peugeot 306
3 | 5 | Really fast Boeing 747
4 | 3 | Another Peugeot but only in Hatchbacks category
When searching for any of this data, I would like to find all child / grandchild / great grandchild etc. etc. nodes. So if someone wants to see all "Cars", they see everything with a parent_id of "Hatchbacks", and so everything with a parent_id of "Peugeot", and so on, to an arbitrary level.
So if I list a "really fast Peugeot 206" with a category_id of either 1, 3, 6, or 8, my query should be able to "travel up" the tree and find any higher categories which are parents/grandparents of that child category. E.g. a user searching for Peugeots in category "8" should find any Peugeots listed with categories 6, 3, or 1 - all of which category 8's descendants.
E.g. using the above data, searching for "Peugeot" in category 3 should actually find vehicles 1, 2 and 4, because vehicles 1 and 2 have a category ancestor trail which leads back up to category 3. See?
Sorry if I haven't explained this well. It's difficult! Thank you, though.
Note: I have read the MySQL dev article on hierarchies.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
规范化模型很棒，但当您实际需要查询它们时就不是这样了。
只需将类别的“路径”存储在类别表中即可。像这样：path = /1/3/4 并且当像“select .... where path like '/1/3/%'”这样查询数据库时，它将比多个分层查询更加简单和快速......
Normalized models are great, but not when you actually have to query them.
Just store the "path" to your category in category table. Like this: path = /1/3/4 and when query you database like "select .... where path like '/1/3/%'" It will be much more simple and fast than multiple hierarchical queries...
您已将数据表示为邻接列表模型，其 在 MySQL 中查询最好使用会话变量。现在，这不是唯一的方法 您可以在关系数据库中表示层次结构。对于您的特定问题，我可能会使用 具体化路径 方法，其中您取消了实际的类别表，而是在汽车表上有一列，每个记录看起来像
Cars/Hatchbacks/Peugeot，并使用LIKE查询。不幸的是，随着记录数量的增加，速度会很慢。现在，如果您知道层次结构的最大深度（例如四个级别），您可以将其分解为单独的列，这样您就可以利用索引。You've represented your data as an Adjacency List model, whose querying in MySQL is best done using session variables. Now, this is not the only way you can represent a hierarchy in a relational database. For your particular problem, I would probably use a materialized path approach instead, where you do away with the actual categories table and instead have a column on your cars table that looks like
Cars/Hatchbacks/Peugeoton a per record basis and useLIKEqueries. Unfortunately that would be slow as the number of records grew. Now, if you know the maximum depth of your hierarchy (e.g. four levels) you could break that out into separate columns instead, which you allow you to take advantage of indexing.