MySQL 命令之 WITH Recursive 递归查询

with(Common Table Expressions/CTE) 在 MySQL 中被称为公共表达式,可以作为一个临时表然后在其他结构中调用.如果是自身调用那么就是后面讲的递归.

理论原理

MySQL with Recursive是什么？

MySQL with Recursive是一种基于递归思想的 MySQL 查询方式，可以实现对数据的递归查询和处理，返回符合条件的数据。在MySQL 8.0版本中，该功能被正式引入。
MySQL with Recursive有什么作用？

MySQL with Recursive的作用是基于一组初始数据，进行递归查询，返回符合条件的数据集。这种递归查询方式可以应用在很多场景下，比如对于树形结构、层级结构的数据处理，以及对数据进行分类汇总等。
MySQL with Recursive的使用限制？

MySQL with Recursive的使用限制主要在于查询语句的复杂性和效率。递归查询的复杂度随着层数的增加而增加，如果递归层数过多可能会导致查询效率低下甚至出现死循环的情况。因此，在使用MySQL with Recursive时需要注意数据量大小和递归层数。

语法

WITH RECURSIVE cte_name (column_list) AS (
    SELECT initial_query_result
    UNION [ALL]
    SELECT recursive_query
    FROM cte_name
    WHERE condition
)
SELECT * FROM cte_name;

WITH RECURSIVE：表示要使用递归查询的方式处理数据。
cte_name：给这个临时的递归表取个名字，可以在初始查询和递归查询中引用。
column_list：表示 cte_name 查询表中包含的列名，列名之间用逗号分隔。
initial_query_result：表示初始的查询结果，应该与 column_list 中的列名对应。
UNION：表示将两个查询结果集进行联合，使用UNION ALL则表示保留重复数据。
recursive_query：表示递归查询语句，应当与 column_list 中的列名对应。
condition：表示递归查询的终止条件，需要使用 cte_name 中的列进行判断。
SELECT * FROM cte_name：表示最终返回的查询结果集，可以通过 cte_name 查询表中的列名进行指定。

简单例子

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte 
  WHERE n < 5
)
SELECT * FROM cte;

运行结果

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

用 Python 实现就是

def cte(n):
	print(n)
	if n<5:
		cte(n+1)

具体实例：（高级使用）

准备工作：

首先准备一张递归表：这里使用用户邀请记录表，A可以邀请B，B可以邀请C，C可以……依次随意邀请。

CREATE TABLE `sz_promotion_tree` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint DEFAULT NULL COMMENT '用户id',
  `parent_id` bigint DEFAULT NULL COMMENT '推荐者id',
  `create_time` datetime DEFAULT NULL COMMENT '推广时间',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`) USING BTREE,
  KEY `parent_id` (`parent_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='推广记录表';

准备一张用户表比如

CREATE TABLE `sz_user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `nickname` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '昵称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户表';

造数据

INSERT INTO sz_user(id, nickname) VALUES (99, 'S');
INSERT INTO sz_user(id, nickname) VALUES (100, 'A');
INSERT INTO sz_user(id, nickname) VALUES (101, 'B');
INSERT INTO sz_user(id, nickname) VALUES (102, 'C');
INSERT INTO sz_user(id, nickname) VALUES (103, 'D');
INSERT INTO sz_user(id, nickname) VALUES (104, 'E');
INSERT INTO sz_user(id, nickname) VALUES (105, 'F');
INSERT INTO sz_user(id, nickname) VALUES (106, 'G');
INSERT INTO sz_user(id, nickname) VALUES (107, 'H');
INSERT INTO sz_user(id, nickname) VALUES (108, 'I');
INSERT INTO sz_user(id, nickname) VALUES (109, 'J');
 
INSERT INTO sz_promotion_tree(id, user_id, parent_id, create_time) VALUES (1, 100, 99, NULL);
INSERT INTO sz_promotion_tree(id, user_id, parent_id, create_time) VALUES (2, 101, 100, NULL);
INSERT INTO sz_promotion_tree(id, user_id, parent_id, create_time) VALUES (3, 102, 100, NULL);
INSERT INTO sz_promotion_tree(id, user_id, parent_id, create_time) VALUES (4, 103, 101, NULL);
INSERT INTO sz_promotion_tree(id, user_id, parent_id, create_time) VALUES (5, 104, 101, NULL);
INSERT INTO sz_promotion_tree(id, user_id, parent_id, create_time) VALUES (6, 105, 102, NULL);
INSERT INTO sz_promotion_tree(id, user_id, parent_id, create_time) VALUES (7, 106, 102, NULL);
INSERT INTO sz_promotion_tree(id, user_id, parent_id, create_time) VALUES (8, 107, 103, NULL);
INSERT INTO sz_promotion_tree(id, user_id, parent_id, create_time) VALUES (9, 108, 103, NULL);
INSERT INTO sz_promotion_tree(id, user_id, parent_id, create_time) VALUES (10, 109, 104, NULL);

造出来一份这样结构的关系：

S邀请了A，A邀请了B、C，B邀请了D、E，D邀请了H、I，E邀请了J

问题一（类似向上递归）

比如现在查询 J109 的所有上级，并且还要查出，这些上级和 J109 的关系是几级

WITH RECURSIVE promotion_tree AS (
  SELECT id, user_id, parent_id, 1 AS level
  FROM sz_promotion_tree
  WHERE user_id = 109
  UNION ALL
  SELECT pt.id, pt.user_id, pt.parent_id, pt2.level + 1
  FROM sz_promotion_tree pt
  JOIN promotion_tree pt2 ON pt.user_id = pt2.parent_id
  WHERE pt.parent_id IS NOT NULL
)
SELECT *
FROM promotion_tree;

查询结果：

+------+---------+-----------+-------+
| id   | user_id | parent_id | level |
+------+---------+-----------+-------+
|   10 |     109 |       104 |     1 |
|    5 |     104 |       101 |     2 |
|    2 |     101 |       100 |     3 |
|    1 |     100 |        99 |     4 |
+------+---------+-----------+-------+
4 rows in set (0.00 sec)

刨析SQL

WITH RECURSIVE promotion_tree AS (                          -- 首先，使用 WITH RECURSIVE 关键字声明了一个递归查询公共表表达式（CTE），命名为 promotion_tree
  SELECT id, user_id, parent_id, 1 AS level                 -- 在初始查询中，从 sz_promotion_tree 表中选择符合条件 user_id = 109 的记录，并为它们分配一个初始级别 1。这些记录作为递归查询的起始点。
  FROM sz_promotion_tree
  WHERE user_id = 109
  UNION ALL                                                 -- 使用 UNION ALL 运算符，将初始查询的结果与递归查询的结果连接起来。
  SELECT pt.id, pt.user_id, pt.parent_id, pt2.level + 1     -- 在递归查询中，选择 sz_promotion_tree 表中的记录，连接到前一级的递归结果（promotion_tree），并通过条件 pt.user_id = pt2.parent_id 进行连接。这样可以构建一个向上层级递归的结构。
  FROM sz_promotion_tree pt                                 -- 在递归查询的每一次迭代中，将前一级的层级 pt2.level 加上 1，并将结果作为当前级别 level。
  JOIN promotion_tree pt2 ON pt.user_id = pt2.parent_id
  WHERE pt.parent_id IS NOT NULL                            -- 递归查询继续迭代，直到不再满足条件 pt.parent_id IS NOT NULL，即没有上级用户时停止递归。
)
SELECT * FROM promotion_tree;                               -- 最后，从递归查询公共表表达式 promotion_tree 中选择所有列，并返回结果

问题二（类似向下递归）

如果再加一张消费记录表，每个人在平台的消费都会给记录到消费记录表中，比如A，A下面有B、C，A邀请的第一级有几个人，就算几个分支，现在需要查询出A下面有多少个分支，并且，每个分支的消费额（比如B分支，包含B和B的所有子节点）有多少。

准备数据：给刚才的A-I，没人插入一条1w的购物记录

CREATE TABLE `sz_order` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint DEFAULT NULL COMMENT '用户id',
  `recharge_amount` decimal(10,2) DEFAULT NULL COMMENT '花费金额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='购买记录';
 
INSERT INTO sz_order(id, user_id, recharge_amount) VALUES (8, 99, 10000);
INSERT INTO sz_order(id, user_id, recharge_amount) VALUES (9, 100, 10000);
INSERT INTO sz_order(id, user_id, recharge_amount) VALUES (10, 101, 10000);
INSERT INTO sz_order(id, user_id, recharge_amount) VALUES (11, 102, 10000);
INSERT INTO sz_order(id, user_id, recharge_amount) VALUES (12, 103, 10000);
INSERT INTO sz_order(id, user_id, recharge_amount) VALUES (13, 104, 10000);
INSERT INTO sz_order(id, user_id, recharge_amount) VALUES (14, 105, 10000);
INSERT INTO sz_order(id, user_id, recharge_amount) VALUES (15, 106, 10000);
INSERT INTO sz_order(id, user_id, recharge_amount) VALUES (16, 107, 10000);
INSERT INTO sz_order(id, user_id, recharge_amount) VALUES (17, 108, 10000);
INSERT INTO sz_order(id, user_id, recharge_amount) VALUES (18, 109, 10000);

解决：拿A100举例，他下面俩链路，B和C，分别对应6w和3w

WITH RECURSIVE promotion_tree AS (
  SELECT id, user_id, parent_id, CAST(user_id AS CHAR(200)) AS chain
  FROM sz_promotion_tree
  WHERE parent_id = (SELECT user_id FROM sz_promotion_tree WHERE user_id = 100)
  UNION ALL
  SELECT pt.id, pt.user_id, pt.parent_id, CONCAT(pt2.chain, '->', pt.user_id)
  FROM sz_promotion_tree pt
  JOIN promotion_tree pt2 ON pt.parent_id = pt2.user_id
),
chain_summary AS (
  SELECT pt.chain, SUM(so.recharge_amount) AS total_amount
  FROM promotion_tree pt
  LEFT JOIN sz_order so ON pt.user_id = so.user_id
  GROUP BY pt.chain
)
SELECT pt.user_id, sum(cs.total_amount) as total_amount
FROM promotion_tree pt
left JOIN chain_summary cs ON cs.chain LIKE CONCAT(pt.chain, '%')
WHERE pt.parent_id = (SELECT user_id FROM sz_promotion_tree WHERE user_id = 100)
group by pt.user_id;

查询结果：

+---------+--------------+
| user_id | total_amount |
+---------+--------------+
|     101 |     60000.00 |
|     102 |     30000.00 |
+---------+--------------+
2 rows in set (0.01 sec)

注意事项

递归的层数应该尽可能的少，过多的递归层数可能导致查询效率低下或者程序崩溃。
MySQL with Recursive功能在MySQL 8.0版本中才被正式引入，使用该功能建议使用该版本或者以上版本。
使用时需要考虑数据量的大小，如果数据量过大可能会影响递归查询的效率。

总结

MySQL with Recursive是一种基于递归思想的MySQL查询方式，可以实现对数据的递归查询和处理，应用广泛。在使用时需要注意递归的层数和数据量大小等因素。通过学习本文，相信大家已经对MySQL with Recursive有了深入的理解，并能够熟练运用该功能。

Reference

MySQL

#MySQL

MySQL 命令之 WITH Recursive 递归查询

https://flepeng.github.io/042-MySQL-21-命令-MySQL-命令之-WITH-Recursive-递归查询/

作者

Lepeng

发布于

2021年3月6日

许可协议

MySQL 命令之 WITH AS 公共表达式上一篇

MySQL 命令之数据去重只保留一条数据下一篇