MYSQL:为每一行构建一个字符串的递归过程

Question

我想创建一个递归遍历表并为每一行构建一个字符串的过程。除了位于层次结构顶部的少数行外，每一行都与另一行相关。

这是我的:

CREATE TABLE item (
    id              INT         NOT NULL    AUTO_INCREMENT,
    name            VARCHAR(30) NOT NULL,
    category        INT             NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (category)
        REFERENCES item(id)
);

CREATE PROCEDURE get_item()
    SELECT *
    FROM item;

-- main categories --
INSERT INTO item (name, category) VALUES 
    ('groceries',   NULL),
    ('retail',      NULL),
    ('service',     NULL),
    ('grains',       1),
    ('produce',      1),
    ('meats',        1),
    ('dairy',        1),
    ('snacks',       1),
    ('beverages',    1),
    ('car',          2),
    ('home',         2),
    ('clothing',     2),
    ('electronics',  2),
    ('chips',        8), 
    ('dip',          8), 
    ('snack bars',   8), 
    ('haircut',      3);

当 get_item() 被调用时，它应该给出这个表:

| id | name         | category  |
|----|--------------|-----------|
|  1 | groceries    | NULL      |
|  2 | retail       | NULL      |
|  3 | service      | NULL      |
|  4 | grains       | 1         |
|  5 | produce      | 1         |
|  6 | meats        | 1         |
|  7 | dairy        | 1         |
|  8 | snacks       | 1         |
|  9 | beverages    | 1         |
| 10 | car          | 2         |
| 11 | home         | 2         |
| 12 | clothing     | 2         |
| 13 | electronics  | 2         |
| 14 | chips        | 8         |
| 15 | dip          | 8         |
| 16 | snack bars   | 8         |
| 17 | haircut      | 3         |

我希望它看起来像这样:

| id | name         | category  | path                              |
|----|--------------|-----------|-----------------------------------|
|  1 | groceries    | NULL      | groceries                         |
|  2 | retail       | NULL      | retail                            |
|  3 | service      | NULL      | service                           |
|  4 | grains       | 1         | groceries > grains                |
|  5 | produce      | 1         | groceries > produce               |
|  6 | meats        | 1         | groceries > meats                 |
|  7 | dairy        | 1         | groceries > dairy                 |
|  8 | snacks       | 1         | groceries > snacks                |
|  9 | beverages    | 1         | groceries > beverages             |
| 10 | car          | 2         | retail > car                      |
| 11 | home         | 2         | retail > home                     |
| 12 | clothing     | 2         | retail > clothing                 |
| 13 | electronics  | 2         | retail > electronics              |
| 14 | chips        | 8         | groceries > snacks > chips        |
| 15 | dip          | 8         | groceries > snacks > dip          |
| 16 | snack bars   | 8         | groceries > snacks > snack bars   |
| 17 | haircut      | 3         | service > haircut                 |

我不知道该怎么做。

Answer 1

为此使用存储过程是 PITA:

DROP PROCEDURE get_item;
DELIMITER //
CREATE PROCEDURE get_item(IN p_category INT, IN p_names TEXT)
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE v_id, v_category INT;
  DECLARE v_name VARCHAR(30);
  DECLARE v_path TEXT;
  DECLARE cur CURSOR FOR
    SELECT id, name, category, CONCAT(COALESCE(CONCAT(p_names, ' > '), ''), name) AS path
    FROM item WHERE category <=> p_category;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_id, v_name, v_category, v_path;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT v_id, v_name, v_category, v_path;
    CALL get_item(v_id, v_path);
  END LOOP;
  CLOSE cur;
END//
DELIMITER ;

结果作为一系列 17 个不同的结果集返回，每个结果集一行:

mysql> CALL get_item(NULL, NULL);

+------+-----------+------------+-----------+
| v_id | v_name    | v_category | v_path    |
+------+-----------+------------+-----------+
|    1 | groceries |       NULL | groceries |
+------+-----------+------------+-----------+
1 row in set (0.00 sec)

+------+--------+------------+--------------------+
| v_id | v_name | v_category | v_path             |
+------+--------+------------+--------------------+
|    4 | grains |          1 | groceries > grains |
+------+--------+------------+--------------------+
1 row in set (0.00 sec)

+------+---------+------------+---------------------+
| v_id | v_name  | v_category | v_path              |
+------+---------+------------+---------------------+
|    5 | produce |          1 | groceries > produce |
+------+---------+------------+---------------------+
1 row in set (0.00 sec)

+------+--------+------------+-------------------+
| v_id | v_name | v_category | v_path            |
+------+--------+------------+-------------------+
|    6 | meats  |          1 | groceries > meats |
+------+--------+------------+-------------------+
1 row in set (0.00 sec)

+------+--------+------------+-------------------+
| v_id | v_name | v_category | v_path            |
+------+--------+------------+-------------------+
|    7 | dairy  |          1 | groceries > dairy |
+------+--------+------------+-------------------+
1 row in set (0.01 sec)

+------+--------+------------+--------------------+
| v_id | v_name | v_category | v_path             |
+------+--------+------------+--------------------+
|    8 | snacks |          1 | groceries > snacks |
+------+--------+------------+--------------------+
1 row in set (0.01 sec)

+------+--------+------------+----------------------------+
| v_id | v_name | v_category | v_path                     |
+------+--------+------------+----------------------------+
|   14 | chips  |          8 | groceries > snacks > chips |
+------+--------+------------+----------------------------+
1 row in set (0.01 sec)

+------+--------+------------+--------------------------+
| v_id | v_name | v_category | v_path                   |
+------+--------+------------+--------------------------+
|   15 | dip    |          8 | groceries > snacks > dip |
+------+--------+------------+--------------------------+
1 row in set (0.01 sec)

+------+------------+------------+---------------------------------+
| v_id | v_name     | v_category | v_path                          |
+------+------------+------------+---------------------------------+
|   16 | snack bars |          8 | groceries > snacks > snack bars |
+------+------------+------------+---------------------------------+
1 row in set (0.01 sec)

+------+-----------+------------+-----------------------+
| v_id | v_name    | v_category | v_path                |
+------+-----------+------------+-----------------------+
|    9 | beverages |          1 | groceries > beverages |
+------+-----------+------------+-----------------------+
1 row in set (0.01 sec)

+------+--------+------------+--------+
| v_id | v_name | v_category | v_path |
+------+--------+------------+--------+
|    2 | retail |       NULL | retail |
+------+--------+------------+--------+
1 row in set (0.01 sec)

+------+--------+------------+--------------+
| v_id | v_name | v_category | v_path       |
+------+--------+------------+--------------+
|   10 | car    |          2 | retail > car |
+------+--------+------------+--------------+
1 row in set (0.01 sec)

+------+--------+------------+---------------+
| v_id | v_name | v_category | v_path        |
+------+--------+------------+---------------+
|   11 | home   |          2 | retail > home |
+------+--------+------------+---------------+
1 row in set (0.02 sec)

+------+----------+------------+-------------------+
| v_id | v_name   | v_category | v_path            |
+------+----------+------------+-------------------+
|   12 | clothing |          2 | retail > clothing |
+------+----------+------------+-------------------+
1 row in set (0.02 sec)

+------+-------------+------------+----------------------+
| v_id | v_name      | v_category | v_path               |
+------+-------------+------------+----------------------+
|   13 | electronics |          2 | retail > electronics |
+------+-------------+------------+----------------------+
1 row in set (0.02 sec)

+------+---------+------------+---------+
| v_id | v_name  | v_category | v_path  |
+------+---------+------------+---------+
|    3 | service |       NULL | service |
+------+---------+------------+---------+
1 row in set (0.02 sec)

+------+---------+------------+-------------------+
| v_id | v_name  | v_category | v_path            |
+------+---------+------------+-------------------+
|   17 | haircut |          3 | service > haircut |
+------+---------+------------+-------------------+
1 row in set (0.02 sec)

在应用程序代码中调用它时，您需要将其作为多结果集语句进行循环。

我几乎从不使用 MySQL 中的存储过程。这在应用程序代码中会简单很多。

我也更喜欢使用替代方法来查询分层数据集，而不是使用递归。幸运的是，在 MySQL 8.0 中，您可以进行递归查询(就像我们已经在几乎所有其他 SQL 数据库中所做的那样)。您可以在 MySQL 8.0 中执行此操作而无需使用存储过程:

WITH RECURSIVE MyTree AS (
    SELECT id, name, category, name AS path FROM item WHERE category IS NULL
    UNION ALL
    SELECT i.id, i.name i.category, CONCAT(t.path, ' > ', i.name)
    FROM item AS i JOIN MyTree AS t ON i.category = t.id
)
SELECT * FROM MyTree;