一、CTE

Common table expression (CTE)通用表表达式是MySQL8推出的新功能。它是一种临时表，使用“WITH”命令，可以执行递归查询。

先看一下如何使用WITH语句：

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a= cte2.c;

二、递归查询

递归查询的语法

WITH RECURSIVE cte_name AS
(
    SELECT ...      -- return initial row set
    UNION ALL / UNION DISTINCT
    SELECT ...      -- return additional row sets
)
SELECT * FROM cte;

定义一个CTE，这个CTE 最终的结果集就是我们想要的 ”递归得到的树结构"，RECURSIVE代表当前 CTE 是递归的
第一个SELECT 为 “初始结果集”
第二个SELECT 为递归部分，使用 "初始结果集或上一次递归返回的结果集" 进行查询得到 “新的结果集”
直到递归部分结果集返回为null，查询结束
最终UNION ALL 会将上述步骤中的所有结果集合并（UNION DISTINCT 会进行去重），再通过 SELECT * FROM cte; 拿到所有的结果集

递归部分不能包括：

聚合函数例如 SUM()
GROUP BY
ORDER BY
LIMIT
DISTINCT

举例：

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


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

初始结果集为 n =1
第一次执行 CTE结果集即是 n =1，条件满足 n < 5，返回 n + 1，然后数据会存储到临时表cte中
第二次执行递归部分，CTE结果集为 n = 2，递归... 直至条件不满足
最后合并结果集

范例

树结构的例子

CREATE TABLE `course_category` (
  `id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键',
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '分类名称',
  `label` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '分类标签默认和名称一样',
  `parentid` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '父结点id（第一级的父节点是0，自关联字段id）',
  `is_show` tinyint DEFAULT NULL COMMENT '是否显示',
  `orderby` int DEFAULT NULL COMMENT '排序字段',
  `is_leaf` tinyint DEFAULT NULL COMMENT '是否叶子',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC COMMENT='课程分类';

mysql> select * course_category; （这里只是截了部分数据）

结果：
1	根结点	根结点	0	1	1	0
1-1	前端开发	前端开发	1	1	1	0
1-1-1	HTML/CSS	HTML/CSS	1-1	1	1	1
1-1-10	其它	其它	1-1	1	10	1
1-1-2	JavaScript	JavaScript	1-1	1	2	1
1-1-3	jQuery	jQuery	1-1	1	3	1
1-1-4	ExtJS	ExtJS	1-1	1	4	1
1-1-5	AngularJS	AngularJS	1-1	1	5	1
1-1-6	ReactJS	ReactJS	1-1	1	6	1
1-1-7	Bootstrap	Bootstrap	1-1	1	7	1
1-1-8	Node.js	Node.js	1-1	1	8	1
1-1-9	Vue	Vue	1-1	1	9	1
1-10	研发管理	研发管理	1	1	10	0
1-10-1	敏捷开发	敏捷开发	1-10	1	1	1
1-10-2	软件设计	软件设计	1-10	1	2	1
1-10-3	软件测试	软件测试	1-10	1	3	1
1-10-4	研发管理	研发管理	1-10	1	4	1
1-10-5	其它	其它	1-10	1	5	1
1-11	系统运维	系统运维	1	1	11	0
1-11-1	Linux	Linux	1-11	1	1	1
1-11-10	其它	其它	1-11	1	10	1
1-11-2	Windows	Windows	1-11	1	2	1
1-11-3	UNIX	UNIX	1-11	1	3	1
1-11-4	Mac OS	Mac OS	1-11	1	4	1
1-11-5	网络技术	网络技术	1-11	1	5	1
1-11-6	路由协议	路由协议	1-11	1	6	1
1-11-7	无线网络	无线网络	1-11	1	7	1
1-11-8	Ngnix	Ngnix	1-11	1	8	1
1-11-9	邮件服务器	邮件服务器	1-11	1	9	1
1-12	产品经理	产品经理	1	1	12	0
1-12-1	交互设计	交互设计	1-12	1	1	1
1-12-2	产品设计	产品设计	1-12	1	2	1
1-12-3	原型设计	原型设计	1-12	1	3	1

mysql> 
with recursive t1 as (
    select * from  course_category p where  id= '1'
    union all
    select t.* from course_category t inner join t1 on t1.id = t.parentid
)
select *  from t1 order by t1.id, t1.orderby

结果： （这里只是截了部分数据）
1	根结点	根结点	0	1	1	0
1-1	前端开发	前端开发	1	1	1	0
1-1-1	HTML/CSS	HTML/CSS	1-1	1	1	1
1-1-10	其它	其它	1-1	1	10	1
1-1-2	JavaScript	JavaScript	1-1	1	2	1
1-1-3	jQuery	jQuery	1-1	1	3	1
1-1-4	ExtJS	ExtJS	1-1	1	4	1
1-1-5	AngularJS	AngularJS	1-1	1	5	1
1-1-6	ReactJS	ReactJS	1-1	1	6	1
1-1-7	Bootstrap	Bootstrap	1-1	1	7	1
1-1-8	Node.js	Node.js	1-1	1	8	1
1-1-9	Vue	Vue	1-1	1	9	1
1-10	研发管理	研发管理	1	1	10	0
1-10-1	敏捷开发	敏捷开发	1-10	1	1	1
1-10-2	软件设计	软件设计	1-10	1	2	1
1-10-3	软件测试	软件测试	1-10	1	3	1
1-10-4	研发管理	研发管理	1-10	1	4	1
1-10-5	其它	其它	1-10	1	5	1
1-11	系统运维	系统运维	1	1	11	0
1-11-1	Linux	Linux	1-11	1	1	1
1-11-10	其它	其它	1-11	1	10	1
1-11-2	Windows	Windows	1-11	1	2	1
1-11-3	UNIX	UNIX	1-11	1	3	1
1-11-4	Mac OS	Mac OS	1-11	1	4	1
1-11-5	网络技术	网络技术	1-11	1	5	1

初始结果集 = select * from course_category p where id= '1'
递归部分，第一次与 t1 inner join 得到 d1
d1 再与 t1 inner join 得到 d2
...
合并所有结果集 d1 + ... + d2

官方文档

dev.mysql.com/doc/refman/…

MySQL8 递归方法查询树型数据

一、CTE

二、递归查询

范例

官方文档