MySQL操作分层数据 [英] MySQL operating hierarchical data

问题描述

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

这篇关于MySQL操作分层数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！