mysql：为每一行构建一个字符串的递归过程

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

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                 |

我不知道该怎么做。

为此使用存储过程是一个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;

mysql：为每一行构建一个字符串的递归过程

1条答案

相关问题

热门标签

最新问答