我将尝试创建一个数据库,其中可以有n个类别及其子类别。
首先,我尝试创建这样的邻接模型数据库。
但是,当我删除一个节点时,我面临一个问题,例如如何管理已删除节点的子节点等。
然后,我尝试通过Joe Celko实现嵌套有序集。
每个图中的表结构:
首先,我尝试创建这样的邻接模型数据库。
+-------------+----------------------+--------+
| category_id | name | parent |
+-------------+----------------------+--------+
| 1 | Electronics | NULL |
| 2 | Mobile | 1 |
| 3 | Washing Machine | 1 |
| 4 | Samsung | 2 |
+-------------+----------------------+--------+
但是,当我删除一个节点时,我面临一个问题,例如如何管理已删除节点的子节点等。
然后,我尝试通过Joe Celko实现嵌套有序集。
![sample_structure](https://istack.dev59.com/EdbKr.webp)
Figure 1:
+----+-------------+-----+-----+
| id | name | lft | rgt |
+----+-------------+-----+-----+
| 1 | Electronics | 1 | 2 |
+----+-------------+-----+-----+
Figure 2:
+----+-------------+-----+-----+
| id | name | lft | rgt |
+----+-------------+-----+-----+
| 1 | Electronics | 1 | 4 |
+----+-------------+-----+-----+
| 2 | Mobile | 2 | 3 |
+----+-------------+-----+-----+
Figure 3:
+----+-----------------+-----+-----+
| id | name | lft | rgt |
+----+-----------------+-----+-----+
| 1 | Electronics | 1 | 6 |
+----+-----------------+-----+-----+
| 2 | Mobile | 2 | 3 |
+----+-----------------+-----+-----+
| 3 | Washing Machine | 4 | 5 |
+----+-----------------+-----+-----+
Figure 4:
+----+-----------------+-----+-----+
| id | name | lft | rgt |
+----+-----------------+-----+-----+
| 1 | Electronics | 1 | 8 |
+----+-----------------+-----+-----+
| 2 | Mobile | 2 | 5 |
+----+-----------------+-----+-----+
| 3 | Washing Machine | 6 | 7 |
+----+-----------------+-----+-----+
| 4 | Samsung | 3 | 4 |
+----+-----------------+-----+-----+
但我无法插入具有正确 rgt
和 lft
的新节点。我使用了这个,但它没有生成正确的 rgt
和 lft
值。
LOCK TABLE nested_category WRITE;
SELECT @myRight := rgt FROM nested_category
WHERE name = 'Mobile';
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;
INSERT INTO nested_category(name, lft, rgt) VALUES('LG', @myRight + 1, @myRight + 2);
UNLOCK TABLES;