MySQL 中的分层数据 [英] Hierarchical Data in MySQL

问题描述

我的 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.

除了 Categories 表之外，您还构建了第二个表:

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 中的分层数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！