MySQL无限层级

之前看到过两种MySQL的递归思路,
这里记录下

MySQL8的CTE

非递归 CTE

派生表

select * from (select 1) as dt;

通用表表达式

with cte as (select 1)
select * from cte;

e.g.

with cte(id) as (select 1)
select * from cte;

with cte(id) as (select 1),
cte2 as (select  id+1 as id from cte)
select * from cte2;

递归 CTE

使用recursive建立递归cte

with recursive cte(n) as (select 1
                          union all
                          select n + 1
                          from cte
                          where n < 5)
select *
from cte;

Untitled

with recursive
    employee_paths(id, name, manager_id) as (select id, name, cast(id as char)
                                             from employee
                                             where manager_id is null
                                             union all select e.id, e.name, concat(ep.path, ',', e.id)
from employee_paths as ep join employees as e
on ep.id=e.manager_id)
select *
from employee_paths
order by path;

递归限制

相关的sql参数

cte_max_recusion_depth;

max_execution_time;

嵌套层级

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

这是原文地址

先提供sql

CREATE TABLE nested_category (
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        lft INT NOT NULL,
        rgt INT NOT NULL
);

INSERT INTO nested_category VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
 (4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
 (9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);

SELECT * FROM nested_category ORDER BY category_id;

这里使用的文章里提供的sql

这里的sql建立了如下的关系

image

查看所有节点的数据

SELECT node.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;

image-1659508800531

这里也可以查询对应节点的子节点

SELECT node.name
FROM nested_category AS node,
     nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
  AND parent.name = 'TELEVISIONS'
ORDER BY node.lft;

image-1659508834096

只不过这里依旧会有父节点

查询所有的叶子节点

叶子节点按上述的规则

左右节点之差为一

SELECT name
FROM nested_category
WHERE rgt = lft + 1;

image-1659508857162

获取某个节点之前的路径节点

SELECT parent.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'FLASH'
ORDER BY parent.lft;

image-1659508871486

获取整个树的深度

SELECT node.name, (COUNT(parent.name) - 1) AS depth 
FROM nested_category AS node, 
        nested_category AS parent 
WHERE node.lft BETWEEN parent.lft AND parent.rgt 
GROUP BY node.name 
ORDER BY node.lft;

image-1659508884091

获取子树深度

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth 
FROM nested_category AS node, 
        nested_category AS parent, 
        nested_category AS sub_parent, 
        ( 
                SELECT node.name, (COUNT(parent.name) - 1) AS depth 
                FROM nested_category AS node, 
                nested_category AS parent 
                WHERE node.lft BETWEEN parent.lft AND parent.rgt 
                AND node.name = 'PORTABLE ELECTRONICS' 
                GROUP BY node.name 
                ORDER BY node.lft 
        )AS sub_tree 
WHERE node.lft在 parent.lft 和 parent.rgt
        和 node.lft 在 sub_parent.lft 和 sub_parent.rgt 之间
        AND sub_parent.name = sub_tree.name 
GROUP BY node.name 
ORDER BY node.lft;

添加子节点

image-1659508894143

这里在TELEVISIONS和PORTABLE ELECTRONICS之间添加一个新的节点

LOCK TABLE nested_category WRITE;

SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);

UNLOCK TABLES;

如果要在2 WAY RADIOS添加节点

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft FROM nested_category

WHERE name = '2 WAY RADIOS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;

INSERT INTO nested_category(name, lft, rgt) VALUES('FRS', @myLeft + 1, @myLeft + 2);

UNLOCK TABLES;

删除节点

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'GAME CONSOLES';

DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

UNLOCK TABLES;

删除上述添加的节点

Q.E.D.