Mysql8.0递归函数
2021-10-20 本文已影响0人
Jiangyouhua
Hi，大家好，我是姜友华。
在Mysql8.0以前，实现树状结构表是一件非常的费力的事情。也就是说本示例要求数据库版本在8.0以上。
前言，树状结构表的一般实现。
一般比较普遍的就是四种方法：（具体见 SQL Anti-patterns这本书）
- Adjacency List：每一条记录存parent_id；
- Path Enumerations：每一条记录存整个tree path经过的node枚举；
- Nested Sets：每一条记录存 nleft 和 nright，子级在父级的包含中；
- Closure Table：维护一个表，所有的tree path作为记录进行保存。
因为不支持递归查询，所以Mysql官方当时推荐的是第三种方式：Nested Sets。我是用过的，非常难受。现在支持递归查询，我们可以使用第一种。
其实我还用了一种，一列维持顺序，一列维持层级，有空可以把实现写出来看看。
好，我们正式开始。
英文好的朋友请移步到这里：Managing Hierarchical Data in MySQL Using the Adjacency List Model，不好的同学跟我一起来。
一、第归的结构。
直接语句说明：
-- 递归CTE遍历最简示例。
WITH RECURSIVE cte_count (n)
AS (
SELECT 1 -- 参与者
UNION ALL -- 使用方式
SELECT n + 1 FROM cte_count WHERE n < 3 -- 判断并执行
)
SELECT n FROM cte_count; -- 输出结果集。
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.01 sec)
二、添加树状结构表：
-
树状结构如图：
树状结构
- 在Shop库中创建数据表category.
CREATE TABLE category (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL,
parent_id int(10) unsigned DEFAULT NULL,
PRIMARY KEY (id),
FOREIGN KEY (parent_id) REFERENCES category (id)
ON DELETE CASCADE ON UPDATE CASCADE
);
- 填充数据：
INSERT INTO category(title,parent_id)
VALUES('Electronics',NULL)
, ('Laptops & PC',1)
, ('Laptops',2)
, ('PC',2)
, ('Cameras & photo',1)
, ('Camera',5)
, ('Phones & Accessories',1)
, ('Smartphones',7)
, ('Android',8)
, ('iOS',8)
, ('Other Smartphones',8)
, ('Batteries',7)
, ('Headsets',7)
, ('Screen Protectors',7);
- 查询所有数据：
select * from category;
+----+----------------------+-----------+
| id | title | parent_id |
+----+----------------------+-----------+
| 1 | Electronics | NULL |
| 2 | Laptops & PC | 1 |
| 3 | Laptops | 2 |
| 4 | PC | 2 |
| 5 | Cameras & photo | 1 |
| 6 | Camera | 5 |
| 7 | Phones & Accessories | 1 |
| 8 | Smartphones | 7 |
| 9 | Android | 8 |
| 10 | iOS | 8 |
| 11 | Other Smartphones | 8 |
| 12 | Batteries | 7 |
| 13 | Headsets | 7 |
| 14 | Screen Protectors | 7 |
+----+----------------------+-----------+
14 rows in set (0.00 sec)
- 查询叶节点。
SELECT c1.id, c1.title
FROM category c1
LEFT JOIN category c2 ON c2.parent_id = c1.id
WHERE c2.id IS NULL;
+----+-------------------+
| id | title |
+----+-------------------+
| 3 | Laptops |
| 4 | PC |
| 6 | Camera |
| 9 | Android |
| 10 | iOS |
| 11 | Other Smartphones |
| 12 | Batteries |
| 13 | Headsets |
| 14 | Screen Protectors |
+----+-------------------+
9 rows in set (0.00 sec)
- 查询支节点。
上面SQL条件改为非空。
使用递归函数
- 所有节点。
WITH RECURSIVE category_path (id, title, path) AS
(
SELECT id, title, title as path
FROM category
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.title, CONCAT(cp.path, ' > ', c.title)
FROM category_path AS cp JOIN category AS c
ON cp.id = c.parent_id
)
SELECT * FROM category_path ORDER BY path;
+------+----------------------+----------------------------------------------------------------------+
| id | title | path |
+------+----------------------+----------------------------------------------------------------------+
| 1 | Electronics | Electronics |
| 5 | Cameras & photo | Electronics > Cameras & photo |
| 6 | Camera | Electronics > Cameras & photo > Camera |
| 2 | Laptops & PC | Electronics > Laptops & PC |
| 3 | Laptops | Electronics > Laptops & PC > Laptops |
| 4 | PC | Electronics > Laptops & PC > PC |
| 7 | Phones & Accessories | Electronics > Phones & Accessories |
| 12 | Batteries | Electronics > Phones & Accessories > Batteries |
| 13 | Headsets | Electronics > Phones & Accessories > Headsets |
| 14 | Screen Protectors | Electronics > Phones & Accessories > Screen Protectors |
| 8 | Smartphones | Electronics > Phones & Accessories > Smartphones |
| 9 | Android | Electronics > Phones & Accessories > Smartphones > Android |
| 10 | iOS | Electronics > Phones & Accessories > Smartphones > iOS |
| 11 | Other Smartphones | Electronics > Phones & Accessories > Smartphones > Other Smartphones |
+------+----------------------+----------------------------------------------------------------------+
14 rows in set (0.01 sec)
- 当前节点的子节点。如：
parent_id = 7
WITH RECURSIVE category_path (id, title, path) AS
(
SELECT id, title, title as path
FROM category
WHERE parent_id = 7
UNION ALL
SELECT c.id, c.title, CONCAT(cp.path, ' > ', c.title)
FROM category_path AS cp JOIN category AS c
ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY path;
+------+-------------------+---------------------------------+
| id | title | path |
+------+-------------------+---------------------------------+
| 12 | Batteries | Batteries |
| 13 | Headsets | Headsets |
| 14 | Screen Protectors | Screen Protectors |
| 8 | Smartphones | Smartphones |
| 9 | Android | Smartphones > Android |
| 10 | iOS | Smartphones > iOS |
| 11 | Other Smartphones | Smartphones > Other Smartphones |
+------+-------------------+---------------------------------+
7 rows in set (0.00 sec)
- 当前节点的路径（到最顶节点）。
WITH RECURSIVE category_path (id, title, parent_id) AS
(
SELECT id, title, parent_id
FROM category
WHERE id = 10 -- child node
UNION ALL
SELECT c.id, c.title, c.parent_id
FROM category_path AS cp JOIN category AS c
ON cp.parent_id = c.id
)
SELECT * FROM category_path;
+------+----------------------+-----------+
| id | title | parent_id |
+------+----------------------+-----------+
| 10 | iOS | 8 |
| 8 | Smartphones | 7 |
| 7 | Phones & Accessories | 1 |
| 1 | Electronics | NULL |
+------+----------------------+-----------+
4 rows in set (0.00 sec)
- 节点的层级。
WITH RECURSIVE category_path (id, title, lvl) AS
(
SELECT id, title, 0 lvl
FROM category
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.title,cp.lvl + 1
FROM category_path AS cp JOIN category AS c
ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY lvl;
+------+----------------------+------+
| id | title | lvl |
+------+----------------------+------+
| 1 | Electronics | 0 |
| 2 | Laptops & PC | 1 |
| 5 | Cameras & photo | 1 |
| 7 | Phones & Accessories | 1 |
| 3 | Laptops | 2 |
| 4 | PC | 2 |
| 6 | Camera | 2 |
| 8 | Smartphones | 2 |
| 12 | Batteries | 2 |
| 13 | Headsets | 2 |
| 14 | Screen Protectors | 2 |
| 9 | Android | 3 |
| 10 | iOS | 3 |
| 11 | Other Smartphones | 3 |
+------+----------------------+------+
14 rows in set (0.00 sec)
好，就这些吧。我是姜友华，下次见。
