我想检索带有父级的所有类别,以便创建面包屑路径。 因此,我创建了以下架构:
CREATE TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent INT DEFAULT NULL
);
INSERT INTO category VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7),
(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);
SELECT * FROM category ORDER BY category_id;
+-------------+----------------------+--------+
| category_id | name | parent |
+-------------+----------------------+--------+
| 1 | ELECTRONICS | NULL |
| 2 | TELEVISIONS | 1 |
| 3 | TUBE | 2 |
| 4 | LCD | 2 |
| 5 | PLASMA | 2 |
| 6 | PORTABLE ELECTRONICS | 1 |
| 7 | MP3 PLAYERS | 6 |
| 8 | FLASH | 7 |
| 9 | CD PLAYERS | 6 |
| 10 | 2 WAY RADIOS | 6 |
+-------------+----------------------+--------+
10 rows in set (0.00 sec)
通过我遵循的示例,我可以使用以下SQL检索信息:
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';
我发现的问题是,在能够看到类别的完整路径之前,我们必须知道它所在的级别。
我的问题,有没有办法检索信息,如以下示例所示,但不定义确切的水平数量?
+-------------+----------------------+-------------+-------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+-------------+-------+
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
我的想法是在考虑深度级别的情况下检索 lev1 或 lev2 等。
更新:
示例输出
ELECTRONICS
ELECTRONICS / TELEVISIONS
ELECTRONICS / TELEVISIONS / TUBE
ELECTRONICS / TELEVISIONS / LCD
ETC
ETC
有一个解决方法。
您可以在列PATH
添加附属品。该列应具有从 elemetn 到父级的 ID 链。因此,对于根,列为空。根的所有子级都有 _
+-------------+----------------------+--------+--------+
| category_id | name | parent | path |
+-------------+----------------------+--------+--------+
| 1 | ELECTRONICS | NULL | |
| 2 | TELEVISIONS | 1 |1_ |
| 3 | TUBE | 2 |1_2_ |
| 4 | LCD | 2 |1_2_ |
| 5 | PLASMA | 2 |1_2_ |
| 6 | PORTABLE ELECTRONICS | 1 |1_ |
| 7 | MP3 PLAYERS | 6 |1_6_ |
| 8 | FLASH | 7 |1_6_7_ |
| 9 | CD PLAYERS | 6 |1_6_ |
| 10 | 2 WAY RADIOS | 6 |1_6_ |
+-------------+----------------------+--------+--------+
插入新节点时,您只需复制父节点路径并添加"_"因此,要检索节点的所有子节点,您只需使用
SELECT *
FROM THE_TABLE
WHERE PATH LIKE '<parent node path>%'
不过,字段大小和级别数量存在限制