MySQL操作分层数据

发布于 2024-11-18 16:17:31 字数 615 浏览 2 评论 0原文

我有 MySQL 表结构：

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 层深度树，例如：

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

我需要（问题）：如果我对于某些类别或子类别设置 is_working = 0，对于其所有子类别，is_working 将设置为 0。

原文

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',
);

which helds hierarchical data with relations of id and parent_id

I have 5 levels depth tree, like:

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

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一世旳自豪 2024-11-25 16:17:31

我使用的是不同的设计，虽然它有局限性，但如果你能忍受的话，它非常简单而且非常高效。

这是鸟类分类树的示例，因此层次结构为类/目/科/属/物种 - 物种是最低级别，1 行 = 1 个物种：

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                   |

这很棒，因为这样您就可以完成只要类别不改变其在树中的级别，就能以非常简单的方式完成所有所需的操作。

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

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 ''
);

and the example of the data:

+---------+---------+---------+----------+---------+-------------------------------+
| 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.

回复收藏 0 原文

青衫儰鉨ミ守葔 2024-11-25 16:17:31

您可以使用触发器来执行递归更新。

看看这个： http://dev.mysql.com/doc/refman /5.0/en/triggers.html

这只是一个想法，我不尝试这个代码。

delimiter //
CREATE TRIGGER categoriesUpdateTrg BEFORE UPDATE ON categories
    FOR EACH ROW BEGIN
    IF (NEW.is_working<>OLD.is_working) THEN
        UPDATE categories SET is_working=NEW.id_working WHERE parent_id=NEW.id;
        END IF;
    END;
//
delimiter ;

触发器在表“categories”的每次更新时执行。对于每个更新的行，都会询问 is_working 列是否已更改。如果条件为真，则更新所有子类别（递归）。

You can use triggers to execute the recursive update.

look at this: http://dev.mysql.com/doc/refman/5.0/en/triggers.html

This is just an idea, i don't try this code.

delimiter //
CREATE TRIGGER categoriesUpdateTrg BEFORE UPDATE ON categories
    FOR EACH ROW BEGIN
    IF (NEW.is_working<>OLD.is_working) THEN
        UPDATE categories SET is_working=NEW.id_working WHERE parent_id=NEW.id;
        END IF;
    END;
//
delimiter ;

the trigger is executed on every update over the table "categories". For each updated row is asking if the is_working column was changed. If the condition is true, then update all the child categories (the recursive).

回复收藏 0 原文

~没有更多了~