我有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操作分层数据
发布评论