问题描述
我的MySQL数据库中有种像树一样的东西.
I've got a sort of tree like thing going on in my MySQL database.
我有一个包含类别的数据库,每个类别都有一个子类别.我将所有类别都保留在一个表中,所以这些列是这样的:
I have a database that has categories, and each category has a subcat. I'm keeping all the categories in one table, so the columns are like this:
*categories table*
id | name | parent_id
1 | Toys | 0
2 | Dolls | 1
3 | Bikes | 1
我数据库中的每个项目都被分配到以下类别之一:
Each item in my database is assigned to one of those categories:
*items table*
item | category_id
barbie | 2
schwinn| 3
问题是,如果有人想查看所有玩具(父类别),从商品数据库中获取信息的最佳方法是什么?我知道怎么做的唯一方法就是做类似的事情
The problem is if someone wants to see all TOYS (the parent category) what is the best way to fetch the info from the items database? The only way I know how is to do something like
SELECT *
FROM items
WHERE category_id = 2
JOIN SELECT *
FROM items
WHERE category_id = 3
etc...
但是,如果我喜欢Toys下的10个类别,那么我就必须进行这种联接并查询10次.
But if I had like 10 categories under Toys, then I'd have to do this join and query 10 times.
有没有更好的方法来解决这个问题?
Is there a better way to handle this?
推荐答案
您想获得父ID:
所以假设您被赋予
set @parentId = 1 /*toys*/
select
*
from
Items i
inner join Categories c on c.id = i.categoryId
where
c.parentId = @parentId
这将为您提供所需的项目-具有一个主要的设计缺陷:它不能处理多个层次的类别类别.
This will give you the items you want - with one major design flaw: it doesn't handle multiple levels of hierarchical categories.
假设您有此类别"表:
*Categories table*
id | name | parentId
1 | Toys | 0
2 | Dolls | 1
3 | Bikes | 1
4 | Models | 2
5 | Act.Fig.| 2
6 | Mountain| 3
7 | BMX | 3
和项目:
*items table*
item | category_id
Barbie | 4
GIJoe | 5
Schwinn| 6
Huffy | 7
获取所有相关项的唯一方法是进行自我联接:
The only way to get all the relevant Items is do a self join:
select
*
from
Items i
inner join Categories c on c.id = i.categoryId
inner join Categories c2 on c.parentId = c2.id
where
c2.parentId = @parentId
此模式不可扩展-因为您可以具有多个层次结构.
This pattern is not scalable - since you can have MULTIPLE levels of hierarchy.
处理层次结构的一种常见方法是构建扁平化"表:将每个节点链接到其所有后代的行.
One common way to deal with hierarchies is to build a "flattened" table: a row that links each node to ALL it's descendants.
除了类别"表,您还构建了第二个表:
In addition to a Categories table, you build a second table:
*CategoriesFlat table* The Name column is here only for readability
id | name | parentId
1 | Toys | 1
-----------------
2 | Dolls | 1
2 | Dolls | 2
-----------------
4 | Models | 1
4 | Models | 2
4 | Models | 4
5 | Act.Fig.| 1
5 | Act.Fig.| 2
5 | Act.Fig.| 5
-----------------
3 | Bikes | 1
3 | Bikes | 3
-----------------
6 | Mountain| 1
6 | Mountain| 3
6 | Mountain| 6
7 | BMX | 1
7 | BMX | 3
7 | BMX | 7
所以你可以这样写:
select
*
from
Items i
inner join CategoriesFlat c on c.id = i.categoryId
where
c.parentId = @parentId
并获取所有相关的类别和项目.
And get ALL the relevant Categories and Items.
这是关于SQL反模式的大型幻灯片和解决方案他们. (SQL中的分层数据是一种反模式,但不要灰心,我们都会遇到这种情况)
Here's a great slideshow about SQL anti-patterns and solutions to them. (Hierarchical data in SQL is an anti-pattern, but don't be disheartened - we all run into this one)
这篇关于MySQL中的分层数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!