如何创建mysql分层递归查询

66bbxpm5  于 2021-06-19  发布在  Mysql
关注(0)|答案(13)|浏览(389)

我有一个mysql表,如下所示:

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

现在,我想有一个mysql查询,我只需提供id[例如说'id=19'],然后我应该得到它的所有子id[即,result应该有id'20,21,22']。。。。而且,孩子们的等级制度还不清楚,它可能会有所不同。。。。
另外,我已经有了使用for循环的解决方案。。。。。如果可能的话,让我知道如何使用一个mysql查询实现同样的功能。

f8rj6qna

f8rj6qna1#

在mysql中管理分层数据的blog
表结构

+-------------+----------------------+--------+
| 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),而是简单的平面列表。层次数据具有一种父子关系,这种关系不是在关系数据库表中自然表示的。阅读更多
更多详情请参阅博客。
编辑:

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

参考:如何在mysql中进行递归选择查询?

t98cgbkg

t98cgbkg2#

通过递归查询(ymmv-on-performance),在其他数据库中可以很容易地做到这一点。
另一种方法是存储两个额外的数据位,一个左值和一个右值。left和right值是从所表示的树结构的预先顺序遍历中派生出来的。
这就是所谓的修改前序树遍历,它允许您运行一个简单的查询来一次获取所有父值。它也被称为“嵌套集”。

q5lcpyga

q5lcpyga3#

对于mysql 8+:使用递归 with 语法。
对于mysql 5.x:使用内联变量、路径id或自连接。

mysql 8版+

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 5.x版

对于不支持公共表表达式的mysql版本(版本5.7之前),您可以通过以下查询实现这一点:

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

这是一把小提琴。
这里,中指定的值 @pv := '19' 应设置为 id 要选择其所有子代的父级的。
如果一个父母有多个孩子,这也会起作用。但是,要求每个记录满足条件 parent_id < id ,否则结果将不完整。

查询中的变量赋值

这个查询使用特定的mysql语法:在执行过程中分配和修改变量。对执行顺序进行了一些假设:
这个 from 首先计算子句。所以这就是 @pv 初始化。
这个 where 子句按照从 from 别名。因此,这里的条件是只包含父级已被标识为在子代树中的记录(主父级的所有子级将逐步添加到子代树中) @pv ).
这里的条件 where 从句按顺序求值,一旦总结果确定,求值就中断。因此,第二个条件必须位于第二位,因为它添加了 id 只有在 id 通过第一个条件。这个 length 函数的调用只是为了确保此条件始终为真,即使 pv 字符串会因为某些原因产生错误的值。
总而言之,人们可能会发现这些假设太过冒险而无法依赖。文档警告:
您可能会得到预期的结果,但这并不能保证[…]涉及用户变量的表达式的求值顺序未定义。
因此,即使它与上面的查询一致,求值顺序仍然可能更改,例如,当您添加条件或将此查询用作较大查询中的视图或子查询时。这是一个“特性”,将在以后的mysql版本中删除:
mysql的早期版本允许在语句中为用户变量赋值,而不是 SET . 为了向后兼容,MySQL8.0支持此功能,但在将来的mysql版本中可能会删除此功能。
如上所述,从mysql 8.0开始,您应该使用递归 with 语法。

效率

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

备选方案1:使用递归,连接方式

越来越多的数据库实现了sql:1999 iso 标准 WITH [RECURSIVE] 递归查询的语法(例如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+)。从8.0版开始,mysql也支持它。有关要使用的语法,请参见此答案的顶部。
有些数据库有一种替代的、非标准的语法用于分层查找,例如 CONNECT BY 子句在oracle、db2、informix、cubrid和其他数据库上可用。
MySQL5.7版本没有提供这样的功能。如果您的数据库引擎提供了这种语法,或者您可以迁移到提供这种语法的数据库引擎,那么这无疑是最好的选择。如果没有,那么也考虑以下替代方案。

备选方案2:路径样式标识符

如果你愿意,事情会变得容易得多 id 包含层次结构信息的值:路径。例如,在您的案例中,这可能如下所示:
ID名称19类别119/1类别219/1/1类别319/1/1/1类别4
然后你的 select 看起来像这样:

select  id,
        name 
from    products
where   id like '19/%'

备选方案3:重复自联接

如果知道层次结构树的深度上限,可以使用标准 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;

看到这把小提琴了吗
这个 where 条件指定要检索其后代的父级。您可以根据需要使用更多级别扩展此查询。

nvbavucw

nvbavucw4#

对另一个问题也做了同样的事
mysql select recursive get all 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;
jm2pwxwz

jm2pwxwz5#

这对我有用,希望这对你也有用。它是w

beq87vna

beq87vna6#

列出第一个递归子级的简单查询:

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)

使用sqlfiddle在线测试它并查看所有结果。
http://sqlfiddle.com/#!9/a318e3/4/0型

kx1ctssn

kx1ctssn7#

只需使用bluem/tree php类在mysql中生成一个自关系表的树。
树 和 树\节点 是用于处理使用父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); 
...
v7pvogib

v7pvogib8#


这是一个分类表。

SELECT  id,
        NAME,
        parent_category 
FROM    (SELECT * FROM category
         ORDER BY parent_category, id) products_sorted,
        (SELECT @pv := '2') initialisation
WHERE   FIND_IN_SET(parent_category, @pv) > 0
AND     @pv := CONCAT(@pv, ',', id)

输出:

uqzxnwby

uqzxnwby9#

如果需要快速读取,最好的选择是使用闭包表。闭包表包含每个祖先/后代对的一行。在您的示例中,闭包表如下所示

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

当然,无论何时使用这样的非规范化数据,都有很大的缺点。您需要在categories表旁边维护闭包表。最好的方法可能是使用触发器,但是正确跟踪闭包表的插入/更新/删除有点复杂。与其他方法一样,您需要查看您的需求,并决定什么方法最适合您。
编辑:请参阅以下问题:在关系数据库中存储分层数据的选项是什么?更多选择。对于不同的情况有不同的最优解。

z6psavjg

z6psavjg10#

这里没有提到的东西,虽然有点类似于接受答案的第二个备选方案,但对于大层次结构查询和easy(insert update delete)项来说是不同的和低成本的,将为每个项添加一个持久路径列。
有些人喜欢:

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/%'

优化路径长度和 ORDER BY path 使用base36编码代替实数路径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/
待办事项
构建一个函数或过程来分割一个项目的检索祖先的路径

z8dt9xmd

z8dt9xmd11#

这是一个有点棘手的,检查这是否是为你工作

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号
适当地替换为字段和表名。

flmtquvp

flmtquvp12#

我想出的最好办法是
使用沿袭存储\排序\跟踪树。这就足够了,阅读速度比任何其他方法都快上千倍。它还允许保持该模式,即使db将更改(因为任何db都允许使用该模式)
使用确定特定id沿袭的函数。
可以随意使用(在selects中,或在cud操作中,甚至按作业使用)。
沿袭方法描述。可以在任何地方找到,例如这里或这里。作为功能-这是什么使我着迷。
最终得到了或多或少简单、相对快速、简单的解决方案。
函数体

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

希望它能帮助别人:)

os8fio9y

os8fio9y13#

试试这些:
表定义:

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 ;

存储过程的 Package 函数:

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

相关问题