MySQL操作分层数据

本文介绍了MySQL操作分层数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有MySQL表结构:

I have MySQL table structure:

CREATE TABLE IF NOT EXISTS `categories` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `parent_id` int(10) unsigned NOT NULL DEFAULT '0', `name` varchar(255) NOT NULL DEFAULT '', `is_working` tinyint(1) unsigned NOT NULL DEFAULT '1', );

以id和parent_id

我有5个层次的深度树，例如:

I have 5 levels depth tree, like:

CATEGORY LEVEL 1 SUBCAT LEVEL 2 SUBCAT LEVEL 3 SUBCAT LEVEL 4 SUBCAT LEVEL 5

我需要(问题):如果我为某个类别或子类别设置is_working = 0，则将所有子类别的is_working设置为0.

I need (the question): if I'm setting is_working = 0 for some category or subcategory, is_working is being set to 0 for all of it's subcategories.

推荐答案

我使用的是不同的设计，尽管它有局限性，但如果您能忍受它们，它会非常简单且非常有效.

What I use is a different design, and though it has limitations, if you can bear them, it's very simple and very efficient.

这里是鸟类分类树的一个示例，因此层次结构是类别/顺序/家庭/属/种"-物种是最低级别，1行= 1种:

Here is an example of taxonomic tree of birds so the hierarchy is Class/Order/Family/Genus/Species - species is the lowest level, 1 row = 1 species:

CREATE TABLE `taxons` ( `TaxonId` smallint(6) NOT NULL default '0', `ClassId` smallint(6) default NULL, `OrderId` smallint(6) default NULL, `FamilyId` smallint(6) default NULL, `GenusId` smallint(6) default NULL, `Name` varchar(150) NOT NULL default '' );

和数据示例:

+---------+---------+---------+----------+---------+-------------------------------+ | TaxonId | ClassId | OrderId | FamilyId | GenusId | Name | +---------+---------+---------+----------+---------+-------------------------------+ | 254 | 0 | 0 | 0 | 0 | Aves | | 255 | 254 | 0 | 0 | 0 | Gaviiformes | | 256 | 254 | 255 | 0 | 0 | Gaviidae | | 257 | 254 | 255 | 256 | 0 | Gavia | | 258 | 254 | 255 | 256 | 257 | Gavia stellata | | 259 | 254 | 255 | 256 | 257 | Gavia arctica | | 260 | 254 | 255 | 256 | 257 | Gavia immer | | 261 | 254 | 255 | 256 | 257 | Gavia adamsii | | 262 | 254 | 0 | 0 | 0 | Podicipediformes | | 263 | 254 | 262 | 0 | 0 | Podicipedidae | | 264 | 254 | 262 | 263 | 0 | Tachybaptus |

这很好，因为只要您不改变类别在树中的级别，就可以通过这种方式非常轻松地完成所有必需的操作.

This is great because this way you accomplish all the needed operations in a very easy way, as long as the categories don't change their level in the tree.