mysql父子节点分层_MySQL分层存储：搜索所有父/祖父母/等。给定一个子节点ID的节点？...

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.

解决方案