基于左右值无限分类数据库操作

mysql> SELECT node.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND parent.name = ‘ELECTRONICS‘
ORDER BY node.lft;
+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| TELEVISIONS          |
| TUBE                 |
| LCD                  |
| PLASMA               |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
| CD PLAYERS           |
| 2 WAY RADIOS         |
+----------------------+
10 rows in set

mysql> SELECT name
FROM nested_category
WHERE rgt = lft + 1;
+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+
6 rows in set

mysql> SELECT parent.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = ‘FLASH‘
ORDER BY parent.lft ASC;
+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
+----------------------+
4 rows in set

mysql> SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| ELECTRONICS          |     0 |
| TELEVISIONS          |     1 |
| TUBE                 |     2 |
| LCD                  |     2 |
| PLASMA               |     2 |
| PORTABLE ELECTRONICS |     1 |
| MP3 PLAYERS          |     2 |
| FLASH                |     3 |
| CD PLAYERS           |     2 |
| 2 WAY RADIOS         |     2 |
+----------------------+-------+
10 rows in set

mysql> SELECT CONCAT( REPEAT(‘|--
‘, COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-------------------------+
| name                    |
+-------------------------+
| ELECTRONICS             |
| |--TELEVISIONS          |
| |--|--TUBE              |
| |--|--LCD               |
| |--|--PLASMA            |
| |--PORTABLE ELECTRONICS |
| |--|--MP3 PLAYERS       |
| |--|--|--FLASH          |
| |--|--CD PLAYERS        |
| |--|--2 WAY RADIOS      |
+-------------------------+
10 rows in set
mysql> SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND node.name = ‘PORTABLE ELECTRONICS‘
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
ORDER BY node.lft;
+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| FLASH                |     2 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+
5 rows in set

mysql> SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND node.name = ‘PORTABLE ELECTRONICS‘
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
ORDER BY node.lft;
+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| FLASH                |     2 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+
5 rows in set

mysql> SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                        nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                        AND node.name = ‘PORTABLE ELECTRONICS‘
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;
+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+
4 rows in set

mysql> SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                        nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                        AND node.name = ‘PORTABLE ELECTRONICS‘
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth = 1
ORDER BY node.lft;
+--------------+-------+
| name         | depth |
+--------------+-------+
| MP3 PLAYERS  |     1 |
| CD PLAYERS   |     1 |
| 2 WAY RADIOS |     1 |
+--------------+-------+
3 rows in set

mysql> CREATE TABLE product
(
        product_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(40),
        category_id INT NOT NULL
);

INSERT INTO product(name, category_id) VALUES(‘20" TV‘,3),(‘36" TV‘,3),
(‘Super-LCD 42"‘,4),(‘Ultra-Plasma 62"‘,5),(‘Value Plasma 38"‘,5),
(‘Power-MP3 5gb‘,7),(‘Super-Player 1gb‘,8),(‘Porta CD‘,9),(‘CD To go!‘,9),
(‘Family Talk 360‘,10);
Query OK, 0 rows affected

Query OK, 10 rows affected
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT parent.name, COUNT(product.name)
FROM nested_category AS node ,
        nested_category AS parent,
        product
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.category_id = product.category_id
GROUP BY parent.name
ORDER BY node.lft;
+----------------------+---------------------+
| name                 | COUNT(product.name) |
+----------------------+---------------------+
| ELECTRONICS          |                  10 |
| TELEVISIONS          |                   5 |
| TUBE                 |                   2 |
| LCD                  |                   1 |
| PLASMA               |                   2 |
| PORTABLE ELECTRONICS |                   5 |
| MP3 PLAYERS          |                   2 |
| FLASH                |                   1 |
| CD PLAYERS           |                   2 |
| 2 WAY RADIOS         |                   1 |
+----------------------+---------------------+
10 rows in set

mysql> LOCK TABLE nested_category WRITE;

SELECT @myRight := rgt FROM nested_category
WHERE name = ‘TELEVISIONS‘;

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(‘GAME CONSOLES‘, @myRight + 1, @myRight + 2);

UNLOCK TABLES;
Query OK, 0 rows affected

+-----------------+
| @myRight := rgt |
+-----------------+
|               9 |
+-----------------+
1 row in set

Query OK, 6 rows affected
Rows matched: 6  Changed: 6  Warnings: 0

Query OK, 5 rows affected
Rows matched: 5  Changed: 5  Warnings: 0

Query OK, 1 row affected

Query OK, 0 rows affected

mysql> SELECT CONCAT( REPEAT(‘|--‘, COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-------------------------+
| name                    |
+-------------------------+
| ELECTRONICS             |
| |--TELEVISIONS          |
| |--|--TUBE              |
| |--|--LCD               |
| |--|--PLASMA            |
| |--GAME CONSOLES        |
| |--PORTABLE ELECTRONICS |
| |--|--MP3 PLAYERS       |
| |--|--|--FLASH          |
| |--|--CD PLAYERS        |
| |--|--2 WAY RADIOS      |
+-------------------------+
11 rows in set

mysql> LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft FROM nested_category

WHERE name = ‘2 WAY RADIOS‘;

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;

INSERT INTO nested_category(name, lft, rgt) VALUES(‘FRS‘, @myLeft + 1, @myLeft + 2);

UNLOCK TABLES;
Query OK, 0 rows affected

+----------------+
| @myLeft := lft |
+----------------+
|             19 |
+----------------+
1 row in set

Query OK, 3 rows affected
Rows matched: 3  Changed: 3  Warnings: 0

Query OK, 0 rows affected
Rows matched: 0  Changed: 0  Warnings: 0

Query OK, 1 row affected

Query OK, 0 rows affected

mysql> SELECT CONCAT( REPEAT(‘|--‘, COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-------------------------+
| name                    |
+-------------------------+
| ELECTRONICS             |
| |--TELEVISIONS          |
| |--|--TUBE              |
| |--|--LCD               |
| |--|--PLASMA            |
| |--GAME CONSOLES        |
| |--PORTABLE ELECTRONICS |
| |--|--MP3 PLAYERS       |
| |--|--|--FLASH          |
| |--|--CD PLAYERS        |
| |--|--2 WAY RADIOS      |
| |--|--|--FRS            |
+-------------------------+
12 rows in set

mysql> LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = ‘GAME CONSOLES‘;

DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

UNLOCK TABLES;
Query OK, 0 rows affected

+----------------+-----------------+---------------------------+
| @myLeft := lft | @myRight := rgt | @myWidth := rgt - lft + 1 |
+----------------+-----------------+---------------------------+
|             10 |              11 |                         2 |
+----------------+-----------------+---------------------------+
1 row in set

Query OK, 1 row affected

Query OK, 7 rows affected
Rows matched: 7  Changed: 7  Warnings: 0

Query OK, 6 rows affected
Rows matched: 6  Changed: 6  Warnings: 0

Query OK, 0 rows affected

mysql> SELECT CONCAT( REPEAT(‘|--‘, COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-------------------------+
| name                    |
+-------------------------+
| ELECTRONICS             |
| |--TELEVISIONS          |
| |--|--TUBE              |
| |--|--LCD               |
| |--|--PLASMA            |
| |--PORTABLE ELECTRONICS |
| |--|--MP3 PLAYERS       |
| |--|--|--FLASH          |
| |--|--CD PLAYERS        |
| |--|--2 WAY RADIOS      |
| |--|--|--FRS            |
+-------------------------+
11 rows in set

mysql> LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = ‘MP3 PLAYERS‘;

DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

UNLOCK TABLES;
Query OK, 0 rows affected

+----------------+-----------------+---------------------------+
| @myLeft := lft | @myRight := rgt | @myWidth := rgt - lft + 1 |
+----------------+-----------------+---------------------------+
|             11 |              14 |                         4 |
+----------------+-----------------+---------------------------+
1 row in set

Query OK, 2 rows affected

Query OK, 5 rows affected
Rows matched: 5  Changed: 5  Warnings: 0

Query OK, 3 rows affected
Rows matched: 3  Changed: 3  Warnings: 0

Query OK, 0 rows affected

mysql> SELECT CONCAT( REPEAT(‘|--‘, COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-------------------------+
| name                    |
+-------------------------+
| ELECTRONICS             |
| |--TELEVISIONS          |
| |--|--TUBE              |
| |--|--LCD               |
| |--|--PLASMA            |
| |--PORTABLE ELECTRONICS |
| |--|--CD PLAYERS        |
| |--|--2 WAY RADIOS      |
| |--|--|--FRS            |
+-------------------------+
9 rows in set

mysql> LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = ‘PORTABLE ELECTRONICS‘;

DELETE FROM nested_category WHERE lft = @myLeft;

UPDATE nested_category SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - 2 WHERE lft > @myRight;

UNLOCK TABLES;
Query OK, 0 rows affected

+----------------+-----------------+---------------------------+
| @myLeft := lft | @myRight := rgt | @myWidth := rgt - lft + 1 |
+----------------+-----------------+---------------------------+
|             10 |              17 |                         8 |
+----------------+-----------------+---------------------------+
1 row in set

Query OK, 1 row affected

Query OK, 3 rows affected
Rows matched: 3  Changed: 3  Warnings: 0

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 0 rows affected
Rows matched: 0  Changed: 0  Warnings: 0

Query OK, 0 rows affected

mysql> SELECT CONCAT( REPEAT(‘|--‘, COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------+
| name            |
+-----------------+
| ELECTRONICS     |
| |--TELEVISIONS  |
| |--|--TUBE      |
| |--|--LCD       |
| |--|--PLASMA    |
| |--CD PLAYERS   |
| |--2 WAY RADIOS |
| |--|--FRS       |
+-----------------+
8 rows in set

mysql> SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = ‘LCD‘;
+----------------+-----------------+---------------------------+
| @myLeft := lft | @myRight := rgt | @myWidth := rgt - lft + 1 |
+----------------+-----------------+---------------------------+
|              5 |               6 |                         2 |
+----------------+-----------------+---------------------------+
1 row in set

mysql>