从博客管理分层数据在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一样)，而只是一个平面列表。层次数据具有亲子关系，在关系数据库表中不能自然地表示这种关系。 阅读更多

更多细节请参考博客。

编辑:

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中做递归SELECT查询?

这是一个有点棘手的问题，检查一下它是否适合你

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

用字段名和表名替换。

我能想到的最好方法是

使用沿袭存储\排序\跟踪树。这已经足够了，而且阅读速度比其他任何方法都要快数千倍。 它还允许即使DB将改变也保持该模式(因为任何DB将允许使用该模式) 使用为特定ID确定谱系的函数。 您可以随心所欲地使用它(在选择中，或在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)

希望它能帮助到一些人:)

对另一个问题也是这样吗

Mysql选择递归获取所有子级别

查询将是:

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;

我发现更容易做到:

1)创建一个函数，检查一个项目是否在另一个项目的父层次结构中的任何地方。就像这样(我不会写函数，用WHILE DO):

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

对于MySQL 8+:使用带语法的递归。 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.倍

对于不支持通用表表达式的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语法:变量在执行过程中被赋值和修改。对执行顺序做了一些假设:

The from clause is evaluated first. So that is where @pv gets initialised. The where clause is evaluated for each record in the order of retrieval from the from aliases. So this is where a condition is put to only include records for which the parent was already identified as being in the descendant tree (all descendants of the primary parent are progressively added to @pv). The conditions in this where clause are evaluated in order, and the evaluation is interrupted once the total outcome is certain. Therefore the second condition must be in second place, as it adds the id to the parent list, and this should only happen if the id passes the first condition. The length function is only called to make sure this condition is always true, even if the pv string would for some reason yield a falsy value.

总而言之，人们可能会发现这些假设风险太大，无法依赖。文档警告:

你可能会得到你期望的结果，但这并不能保证…包含用户变量的表达式的求值顺序未定义。

因此，即使它与上面的查询一致，求值顺序仍然可能发生变化，例如当您添加条件或将此查询用作较大查询中的视图或子查询时。这个“特性”将在未来的MySQL版本中被移除:

以前的MySQL版本允许在语句中为用户变量赋值，而不是SET。为了向后兼容，MySQL 8.0支持这个功能，但在MySQL的未来版本中可能会被删除。

如上所述，从MySQL 8.0开始，您应该使用带有语法的递归。

效率

对于非常大的数据集，这个解决方案可能会很慢，因为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也支持它。请参阅答案顶部的语法。

有些数据库有用于层次结构查找的替代非标准语法，例如Oracle、DB2、Informix、CUBRID和其他数据库上可用的CONNECT BY子句。

MySQL 5.7版本不提供这样的特性。如果您的数据库引擎提供了这种语法，或者您可以迁移到提供这种语法的数据库引擎，那么这当然是最好的选择。如果不是，那么也要考虑以下备选方案。

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

如果您分配包含层次信息的id值(路径)，事情就会变得简单得多。例如，在你的例子中，它可能是这样的:

然后你的选择看起来像这样:

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条件指定要检索哪个父节点的后代。您可以根据需要使用更多级别扩展此查询。

您可以在其他数据库中使用递归查询(性能上的YMMV)很容易地做到这一点。

另一种方法是存储两个额外的数据位，一个左值和一个右值。左值和右值来自于对所表示的树结构的预序遍历。

这就是所谓的Modified Preorder Tree遍历，允许您运行一个简单的查询来一次性获得所有父值。它也被称为“嵌套集”。

如果需要快速读取速度，最好的选择是使用闭包表。闭包表为每个祖先/后代对包含一行。在你的例子中，闭包表是这样的

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

当然，无论何时使用这样的非规格化数据都有一个很大的缺点。您需要在类别表旁边维护闭包表。最好的方法可能是使用触发器，但是正确跟踪闭包表的插入/更新/删除有点复杂。与任何事情一样，您需要查看您的需求，并决定哪种方法最适合您。

编辑:请参阅问题:在关系数据库中存储层次数据的选项是什么?更多选项。不同的情况有不同的最佳解决方案。

试试这些:

表定义:

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

使用BlueM/tree php类在mysql中创建一个自关系表的树。

Tree和Tree\Node是PHP类，用于处理使用父ID引用分层结构的数据。一个典型的例子是关系数据库中的一个表，其中每个记录的“父”字段引用另一个记录的主键。当然，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); 
...

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

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

这里没有提到的是，为每个项添加持久路径列，尽管它与第二种备选方案有点相似，但对于大型层次结构查询和简单的(插入、更新、删除)项来说不同且成本较低。

一些像:

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路径使用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/

TODO

构建一个函数或过程，以分割检索一个项的祖先的路径

我向你提出了一个问题。这将给你递归类别与一个单一的查询:

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

这是一把小提琴。

这对我有用，希望这对你也有用。它会给你一个记录集根到子为任何特定的菜单。根据您的需求更改字段名称。

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;

这是一个分类表。

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)

输出:

基于@trincot的答案，很好地解释了，我使用WITH RECURSIVE()语句使用当前页面的id创建一个面包屑，并在层次结构中反向查找路由表中的每个父节点。

因此，@trincot解决方案在相反的方向上进行了调整，以寻找父母而不是后代。

我还添加了深度值，这是有用的结果顺序颠倒(否则面包屑将上下颠倒)。

WITH RECURSIVE cte (
    `id`,
    `title`,
    `url`,
    `icon`,
    `class`,
    `parent_id`,
    `depth`
) AS (
    SELECT   
        `id`,
        `title`,
        `url`,
        `icon`,
        `class`,
        `parent_id`,
        1 AS `depth` 
    FROM     `route`
    WHERE    `id` = :id
      
    UNION ALL 
    SELECT 
        P.`id`,
        P.`title`,
        P.`url`,
        P.`icon`,
        P.`class`,
        P.`parent_id`,
        `depth` + 1
    FROM `route` P
        
    INNER JOIN cte
        ON P.`id` = cte.`parent_id`
)
SELECT * FROM cte ORDER BY `depth` DESC;

在升级到mySQL 8+之前，我正在使用vars，但它已弃用，并且不再在8.0.22版本上工作!

编辑2021-02-19: 分层菜单示例

在@david评论之后，我决定尝试制作一个包含所有节点的完整分层菜单，并按我想要的方式排序(用排序列在每个深度中排序项目)。对我的用户/授权矩阵页面非常有用。

这确实简化了我的旧版本，每个深度上都有一个查询(PHP循环)。

这个例子集成了一个INNER JOIN和url表来根据网站(多网站CMS系统)过滤路由。

您可以看到包含CONCAT()函数的基本路径列，以正确的方式对菜单进行排序。

SELECT R.* FROM (
    WITH RECURSIVE cte (
        `id`,
        `title`,
        `url`,
        `icon`,
        `class`,
        `parent`,
        `depth`,
        `sorting`,
        `path`
    ) AS (
        SELECT 
            `id`,
            `title`,
            `url`,
            `icon`,
            `class`,
            `parent`,
            1 AS `depth`,
            `sorting`,
            CONCAT(`sorting`, ' ' , `title`) AS `path`
        FROM `route`
        WHERE `parent` = 0
        UNION ALL SELECT 
            D.`id`,
            D.`title`,
            D.`url`,
            D.`icon`,
            D.`class`,
            D.`parent`,
            `depth` + 1,
            D.`sorting`,
            CONCAT(cte.`path`, ' > ', D.`sorting`, ' ' , D.`title`)
        FROM `route` D
        INNER JOIN cte
            ON cte.`id` = D.`parent`
    )
    SELECT * FROM cte
) R

INNER JOIN `url` U
    ON R.`id` = U.`route_id`
    AND U.`site_id` = 1

ORDER BY `path` ASC