· 8 years ago · Aug 26, 2018, 07:36 PM
1MySQL operating hierarchical data
2CREATE TABLE IF NOT EXISTS `categories` (
3 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
4 `parent_id` int(10) unsigned NOT NULL DEFAULT '0',
5 `name` varchar(255) NOT NULL DEFAULT '',
6 `is_working` tinyint(1) unsigned NOT NULL DEFAULT '1',
7);
8
9CATEGORY LEVEL 1
10 SUBCAT LEVEL 2
11 SUBCAT LEVEL 3
12 SUBCAT LEVEL 4
13 SUBCAT LEVEL 5
14
15delimiter //
16CREATE TRIGGER categoriesUpdateTrg BEFORE UPDATE ON categories
17 FOR EACH ROW BEGIN
18 IF (NEW.is_working<>OLD.is_working) THEN
19 UPDATE categories SET is_working=NEW.id_working WHERE parent_id=NEW.id;
20 END IF;
21 END;
22//
23delimiter ;
24
25CREATE TABLE `taxons` (
26 `TaxonId` smallint(6) NOT NULL default '0',
27 `ClassId` smallint(6) default NULL,
28 `OrderId` smallint(6) default NULL,
29 `FamilyId` smallint(6) default NULL,
30 `GenusId` smallint(6) default NULL,
31 `Name` varchar(150) NOT NULL default ''
32);
33
34+---------+---------+---------+----------+---------+-------------------------------+
35| TaxonId | ClassId | OrderId | FamilyId | GenusId | Name |
36+---------+---------+---------+----------+---------+-------------------------------+
37| 254 | 0 | 0 | 0 | 0 | Aves |
38| 255 | 254 | 0 | 0 | 0 | Gaviiformes |
39| 256 | 254 | 255 | 0 | 0 | Gaviidae |
40| 257 | 254 | 255 | 256 | 0 | Gavia |
41| 258 | 254 | 255 | 256 | 257 | Gavia stellata |
42| 259 | 254 | 255 | 256 | 257 | Gavia arctica |
43| 260 | 254 | 255 | 256 | 257 | Gavia immer |
44| 261 | 254 | 255 | 256 | 257 | Gavia adamsii |
45| 262 | 254 | 0 | 0 | 0 | Podicipediformes |
46| 263 | 254 | 262 | 0 | 0 | Podicipedidae |
47| 264 | 254 | 262 | 263 | 0 | Tachybaptus |