使用邻接表模型管理MySQL中的分层数据

Question

I would like to retrieve all categories with parent in order to create a breadcrumb path. for that reason I create the following schema:

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)

With the example I follow I can retrieve the information using the following 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';

The problem I found is before being able to see the full path of a category we have to know the level at which it resides.

my question, is there a way to retrieve the information as the following example but not defining the exact amount of levels?

+-------------+----------------------+-------------+-------+
| lev1        | lev2                 | lev3        | lev4  |
+-------------+----------------------+-------------+-------+
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |

my idea is to retrieve lev1 or lev2 etc taking into consideration the depth level.

UPDATE:

EXAMPLE OUTPUT

ELECTRONICS
ELECTRONICS / TELEVISIONS
ELECTRONICS / TELEVISIONS / TUBE
ELECTRONICS / TELEVISIONS / LCD
ETC
ETC

Answer 1

There is a workaround.

You can add collateral PATH column. The column should have chain of ids from the elemetn to parent. Thus for the root the column empty. All the children of root has _

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

On insert a new node you just copy parent node path and add '_' So to retrieve all children of a node you just use

SELECT * 
FROM THE_TABLE
WHERE PATH LIKE '<parent node path>%'

There is a restriction of the field size and amount of levels though