如何创建MySQL分层递归查询-Java 学习之路

164

我有一个MySQL表，如下所示：

id | name        | parent_id
19 | category1   | 0
20 | category2   | 19
21 | category3   | 20
22 | category4   | 21
......

现在，我想要一个MySQL查询，我只提供id [例如说'id = 19']然后我应该得到它的所有子ID [即结果应该有ids '20，21,22'] ....而且，孩子的等级不知道它可以变化....

另外，我已经有了使用for循环的解决方案.....如果可能的话，让我知道如何使用单个MySQL查询来实现相同的功能 .

14 回答

71
如果您使用的是MySql 8，则使用递归 with 子句：
```
with recursive cte (id, name, parent_id) as (
  select     id,
             name,
             parent_id
  from       products
  where      parent_id = 19
  union all
  select     p.id,
             p.name,
             p.parent_id
  from       products p
  inner join cte
          on p.parent_id = cte.id
)
select * from cte;
```
parent_id = 19 中指定的值应设置为要选择所有后代的父级的 id .

在MySql之前8

对于不支持公用表表达式（最高版本为5.7）的MySql版本，您可以使用以下查询来实现此目的：
```
select  id,
        name,
        parent_id 
from    (select * from products
         order by parent_id, id) products_sorted,
        (select @pv := '19') initialisation
where   find_in_set(parent_id, @pv)
and     length(@pv := concat(@pv, ',', id))
```
这是fiddle .

这里， @pv := '19' 中指定的值应设置为要选择所有后代的父级的 id .

如果父母有多个孩子，这也可以 . 但是，要求每条记录满足条件 parent_id < id ，否则结果将不完整 .

此查询使用特定的MySql语法：在执行期间分配和修改变量 . 对执行顺序做了一些假设：
- 首先评估 from 子句 . 这就是 @pv 被初始化的地方 .
- where 子句按照从 from 别名中检索的顺序对每条记录进行评估 . 因此，这是一个条件，只包括父项已被识别为在后代树中的记录（主要父项的所有后代逐渐添加到 @pv ） .
- 此 where 子句中的条件按顺序进行评估，一旦总结果确定，评估就会中断 . 因此，第二个条件必须位于第二位，因为它将 id 添加到父列表，并且只有在 id 传递第一个条件时才会发生这种情况 . 仅调用 length 函数以确保此条件始终为true，即使 pv 字符串由于某种原因会产生假值 .
总而言之，人们可能会发现这些假设风险太大而无法依赖 - 它们没有文件保证，即使它一致地工作，当您将此查询用作视图或子视图时，评估顺序在理论上仍然可能会发生变化查询更大的查询 .

另请注意，对于非常大的数据集，此解决方案可能会变慢，因为 find_in_set 操作不是在列表中查找数字的最理想方式，当然不是在列表中达到与数字相同数量级的大小记录的返回 .

Alternative 1: WITH RECURSIVE, CONNECT BY

越来越多的数据库为递归查询实现SQL:1999 ISO standard WITH [RECURSIVE] syntax（例如Postgres 8.4+，SQL Server 2005+，DB2，Oracle 11gR2+，SQLite 3.8.4+，Firebird 2.1+，H2，HyperSQL 2.1.0+，Teradata，MariaDB 10.2.2+） . 截至version 8.0, also MySql supports it . 有关要使用的语法，请参阅此答案的顶部 .

某些数据库具有用于分层查找的替代非标准语法，例如Oracle和DB2数据库上可用的CONNECT BY子句 .

MySql 5.7版不提供这样的功能 . 当您的数据库引擎提供此语法时，那肯定是最佳选择 . 如果没有，那么还要考虑以下备选方案 .

Alternative 2: Path-style Identifiers

如果要分配包含分层信息的 id 值，事情会变得容易得多：路径 . 例如，在您的情况下，这可能如下所示：
```
ID       | NAME
19       | category1   
19/1     | category2  
19/1/1   | category3  
19/1/1/1 | category4
```
然后你的 select 看起来像这样：
```
select  id,
        name 
from    products
where   id like '19/%'
```
Alternative 3: Repeated Self-joins

如果您知道层次树可以变深的上限，则可以使用标准 sql ，如下所示：
```
select      p6.parent_id as parent6_id,
            p5.parent_id as parent5_id,
            p4.parent_id as parent4_id,
            p3.parent_id as parent3_id,
            p2.parent_id as parent2_id,
            p1.parent_id as parent_id,
            p1.id as product_id,
            p1.name
from        products p1
left join   products p2 on p2.id = p1.parent_id 
left join   products p3 on p3.id = p2.parent_id 
left join   products p4 on p4.id = p3.parent_id  
left join   products p5 on p5.id = p4.parent_id  
left join   products p6 on p6.id = p5.parent_id
where       19 in (p1.parent_id, 
                   p2.parent_id, 
                   p3.parent_id, 
                   p4.parent_id, 
                   p5.parent_id, 
                   p6.parent_id) 
order       by 1, 2, 3, 4, 5, 6, 7;
```
看到这个fiddle

where 条件指定要检索其后代的父项 . 您可以根据需要使用更多级别扩展此查询 .
回复于 2024-04-26T05:04:51+08:00

来自博客 Managing Hierarchical Data in MySQL

表结构

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

查询：

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 | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+

大多数用户曾经在SQL数据库中处理过分层数据，毫无疑问，他们了解到分层数据的管理不是关系数据库的用途 . 关系数据库的表不是分层的（如XML），而只是一个平面列表 . 分层数据具有父子关系，这种关系不是自然的在关系数据库表中表示 . Read more

有关详细信息，请参阅博客 .

EDIT:

select @pv:=category_id as category_id, name, parent from category
join
(select @pv:=19)tmp
where parent=@pv

输出：

category_id name    parent
19  category1   0
20  category2   19
21  category3   20
22  category4   21

参考：How to do the Recursive SELECT query in Mysql?

回复于 2024-04-26T05:04:51+08:00

这里有另一个问题做同样的事情

Mysql select recursive get all child with multiple level

查询将是：

SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(SELECT GROUP_CONCAT(id SEPARATOR ',') FROM table WHERE parent_id IN (@pv)) AS lv FROM table 
JOIN
(SELECT @pv:=1)tmp
WHERE parent_id IN (@pv)) a;

回复于 2024-04-26T05:04:51+08:00

试试这些：

表定义：

DROP TABLE IF EXISTS category;
CREATE TABLE category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    parent_id INT,
    CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
    REFERENCES category (id)
) engine=innodb;

实验行：

INSERT INTO category VALUES
(19, 'category1', NULL),
(20, 'category2', 19),
(21, 'category3', 20),
(22, 'category4', 21),
(23, 'categoryA', 19),
(24, 'categoryB', 23),
(25, 'categoryC', 23),
(26, 'categoryD', 24);

递归存储过程：

DROP PROCEDURE IF EXISTS getpath;
DELIMITER $$
CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)
BEGIN
    DECLARE catname VARCHAR(20);
    DECLARE temppath TEXT;
    DECLARE tempparent INT;
    SET max_sp_recursion_depth = 255;
    SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent;
    IF tempparent IS NULL
    THEN
        SET path = catname;
    ELSE
        CALL getpath(tempparent, temppath);
        SET path = CONCAT(temppath, '/', catname);
    END IF;
END$$
DELIMITER ;

存储过程的包装函数：

DROP FUNCTION IF EXISTS getpath;
DELIMITER $$
CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
    DECLARE res TEXT;
    CALL getpath(cat_id, res);
    RETURN res;
END$$
DELIMITER ;

选择示例：

SELECT id, name, getpath(id) AS path FROM category;

输出：

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 19 | category1 | category1                               |
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
| 23 | categoryA | category1/categoryA                     |
| 24 | categoryB | category1/categoryA/categoryB           |
| 25 | categoryC | category1/categoryA/categoryC           |
| 26 | categoryD | category1/categoryA/categoryB/categoryD |
+----+-----------+-----------------------------------------+

过滤具有特定路径的行：

SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%';

输出：

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
+----+-----------+-----------------------------------------+

回复于 2024-04-26T05:04:51+08:00

-1

我想出的最佳方法是

使用lineage存储\ sort \ trace树 . 这绰绰有余，阅读速度比其他任何方法快数千倍 . 它也允许保持该模式，即使DB将改变（因为任何数据库将允许使用该模式）
使用确定特定ID的谱系的函数 .
根据需要使用它（在选择中，或在CUD操作中，甚至通过作业） .

谱系方法描述 . 可以在任何地方找到，例如Here或here . 至于功能 - that是什么让我感动 .

最终 - 获得了或多或少的简单，相对快速和简单的解决方案 .

功能的身体

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_lineage`(the_id INT) RETURNS text CHARSET utf8
    READS SQL DATA
BEGIN

 DECLARE v_rec INT DEFAULT 0;

 DECLARE done INT DEFAULT FALSE;
 DECLARE v_res text DEFAULT '';
 DECLARE v_papa int;
 DECLARE v_papa_papa int DEFAULT -1;
 DECLARE csr CURSOR FOR 
  select _id,parent_id -- @n:=@n+1 as rownum,T1.* 
  from 
    (SELECT @r AS _id,
        (SELECT @r := table_parent_id FROM table WHERE table_id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := the_id, @l := 0,@n:=0) vars,
        table m
    WHERE @r <> 0
    ) T1
    where T1.parent_id is not null
 ORDER BY T1.lvl DESC;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open csr;
    read_loop: LOOP
    fetch csr into v_papa,v_papa_papa;
        SET v_rec = v_rec+1;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- add first
        IF v_rec = 1 THEN
            SET v_res = v_papa_papa;
        END IF;
        SET v_res = CONCAT(v_res,'-',v_papa);
    END LOOP;
    close csr;
    return v_res;
END

然后你就是

select get_lineage(the_id)

希望它有助于某人:)

回复于 2024-04-26T05:04:51+08:00

7
如果您需要快速读取速度，最好的选择是使用闭包表 . 闭包表包含每个祖先/后代对的行 . 所以在你的例子中，闭包表看起来像
```
ancestor | descendant | depth
0        | 0          | 0
0        | 19         | 1
0        | 20         | 2
0        | 21         | 3
0        | 22         | 4
19       | 19         | 0
19       | 20         | 1
19       | 21         | 3
19       | 22         | 4
20       | 20         | 0
20       | 21         | 1
20       | 22         | 2
21       | 21         | 0
21       | 22         | 1
22       | 22         | 0
```
拥有此表后，分层查询变得非常简单快捷 . 获得所有类别20的后代：
```
SELECT cat.* FROM categories_closure AS cl
INNER JOIN categories AS cat ON cat.id = cl.descendant
WHERE cl.ancestor = 20 AND cl.depth > 0
```
当然，每当你使用像这样的非规范化数据时，都会有一个很大的缺点 . 您需要在类别表旁边维护闭包表 . 最好的方法可能是使用触发器，但正确跟踪闭包表的插入/更新/删除有点复杂 . 与任何事情一样，您需要查看您的要求并确定最适合您的方法 .

Edit ：有关更多选项，请参阅问题What are the options for storing hierarchical data in a relational database? . 针对不同情况有不同的最佳解决方案 .
回复于 2024-04-26T05:04:51+08:00
1

您可以使用递归查询（性能上的YMMV）轻松地在其他数据库中执行此操作 .

另一种方法是存储两个额外的数据位，左右值 . 左侧和右侧值来自您正在表示的树结构的预先遍历遍历 .

这称为Modified Preorder Tree Traversal，允许您运行简单查询以立即获取所有父值 . 它也被称为“嵌套集” .

回复于 2024-04-26T05:04:51+08:00

简单查询列出第一次递归的子项：

select @pv:=id as id, name, parent_id
from products
join (select @pv:=19)tmp
where parent_id=@pv

结果：

id  name        parent_id
20  category2   19
21  category3   20
22  category4   21
26  category24  22

...左连接：

select
    @pv:=p1.id as id
  , p2.name as parent_name
  , p1.name name
  , p1.parent_id
from products p1
join (select @pv:=19)tmp
left join products p2 on p2.id=p1.parent_id -- optional join to get parent name
where p1.parent_id=@pv

@tincot的解决方案列出所有孩子的：

select  id,
        name,
        parent_id 
from    (select * from products
         order by parent_id, id) products_sorted,
        (select @pv := '19') initialisation
where   find_in_set(parent_id, @pv) > 0
and     @pv := concat(@pv, ',', id)

使用Sql Fiddle在线测试并查看所有结果 .

http://sqlfiddle.com/#!9/a318e3/4/0

回复于 2024-04-26T05:04:51+08:00

它有点棘手，检查它是否适合你

select a.id,if(a.parent = 0,@varw:=concat(a.id,','),@varw:=concat(a.id,',',@varw)) as list from (select * from recursivejoin order by if(parent=0,id,parent) asc) a left join recursivejoin b on (a.id = b.parent),(select @varw:='') as c  having list like '%19,%';

SQL小提琴链接http://www.sqlfiddle.com/#!2/e3cdf/2

适当地替换您的字段和表名称 .

回复于 2024-04-26T05:04:51+08:00

0
只需使用BlueM/tree php类在mysql中创建自关系表的树 .

Tree和Tree \ Node是用于处理使用父ID引用分层结构化的数据的PHP类 . 典型示例是关系数据库中的表，其中每个记录的“父”字段引用另一个记录的主键 . 当然，Tree不仅可以使用源自数据库的数据，还可以使用任何内容：您提供数据，Tree使用它，无论数据来自何处以及如何处理数据 . 阅读更多

以下是使用BlueM / tree的示例：
```
<?php 
require '/path/to/vendor/autoload.php'; $db = new PDO(...); // Set up your database connection 
$stm = $db->query('SELECT id, parent, title FROM tablename ORDER BY title'); 
$records = $stm->fetchAll(PDO::FETCH_ASSOC); 
$tree = new BlueM\Tree($records); 
...
```
回复于 2024-04-26T05:04:51+08:00
3
我发现它更容易：

1）创建一个函数，检查项是否在另一个项的父层次结构中的任何位置 . 像这样的东西（我不会写这个函数，用WHILE做它）：
```
is_related(id, parent_id);
```
在你的例子中
```
is_related(21, 19) == 1;
is_related(20, 19) == 1;
is_related(21, 18) == 0;
```
2）使用子选择，如下所示：
```
select ...
from table t
join table pt on pt.id in (select i.id from table i where is_related(t.id,i.id));
```
回复于 2024-04-26T05:04:51+08:00
1
这里没有提到的东西，虽然有点类似于接受的答案的第二个替代方案但是大层次查询和简单（插入更新删除）项目的不同且低成本，将为每个项目添加持久路径列 .

一些像：
```
id | name        | path
19 | category1   | /19
20 | category2   | /19/20
21 | category3   | /19/20/21
22 | category4   | /19/20/21/22
```
例：
```
-- get children of category3:
SELECT * FROM my_table WHERE path LIKE '/19/20/21%'
-- Reparent an item:
UPDATE my_table SET path = REPLACE(path, '/19/20', '/15/16') WHERE path LIKE '/19/20/%'
```
Optimise the path length and ORDER BY path using base36 encoding instead real numeric path id
```
// base10 => base36
 '1' => '1',
 '10' => 'A',
 '100' => '2S',
 '1000' => 'RS',
 '10000' => '7PS',
 '100000' => '255S',
 '1000000' => 'LFLS',
 '1000000000' => 'GJDGXS',
 '1000000000000' => 'CRE66I9S'
```
https://en.wikipedia.org/wiki/Base36

通过使用固定长度和填充到编码的id来抑制斜杠'/'分隔符

详细的优化说明如下：https://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/

TODO

Build 一个功能或程序来分裂后遗症的祖先一个项目
回复于 2024-04-26T05:04:51+08:00

这对我有用，希望这也适合你 . 它将为您提供任何特定菜单的记录设置Root to Child . 根据您的要求更改字段名称 .

SET @id:= '22';

SELECT Menu_Name, (@id:=Sub_Menu_ID ) as Sub_Menu_ID, Menu_ID 
FROM 
    ( SELECT Menu_ID, Menu_Name, Sub_Menu_ID 
      FROM menu 
      ORDER BY Sub_Menu_ID DESC
    ) AS aux_table 
    WHERE Menu_ID = @id
     ORDER BY Sub_Menu_ID;

回复于 2024-04-26T05:04:51+08:00

201

我已经为你查了一下 . 这将为您提供单个查询的递归类别：

SELECT id,NAME,'' AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 WHERE prent is NULL
UNION 
SELECT b.id,a.name,b.name AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id WHERE a.prent is NULL AND b.name IS NOT NULL 
UNION 
SELECT c.id,a.name,b.name AS subName,c.name AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id WHERE a.prent is NULL AND c.name IS NOT NULL 
UNION 
SELECT d.id,a.name,b.name AS subName,c.name AS subsubName,d.name AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id LEFT JOIN Table1 AS d ON d.prent=c.id WHERE a.prent is NULL AND d.name IS NOT NULL 
ORDER BY NAME,subName,subsubName,subsubsubName

这是fiddle .

回复于 2024-04-26T05:04:51+08:00

如何创建MySQL分层递归查询

14 回答

在MySql之前8

相关问题