一、为什么要使用MySQL递归查询
在很多场景下有树形表需要去遍历,若通过编程语言去递归遍历树将多次进行与数据库的交互操作,相当于100个葡萄去楼下超市买100次,在路上多走了99次浪费了大量时间,肯定不如一次买100葡萄省事。所以与其让编程语言递归遍历不如在MySQ历数L层面递归遍据,减少数据传输造成的资源浪费。
二、递归查询
前提:MySQL8.0及以上版本
语法:
with recursive 临时表 (临时字段1,临时字段2,. . .) as
(
select 初始化表数据...
union all
select 基于临时字段的新数据... from 临时表 ...
)
select ... from 临时表;
说明:
① 递归查询:相当于定义了一张临时表。
② 第一个括号:定义表的字段。
③ as之后的括号:定义表数据的获取过程。
④ 第一个select语句:用于给空表添加数据,即临时表数据初始化。
⑤ 第二个select语句:用于执行递归过程,将上一轮查出的结果集进行遍历,拿到新的结果集放到临时表中。
⑥ 用union all 进行连接:代表将每一次的结果集都放到临时表中;
用union连接:代表每次将结果集放到临时表时都会进行去重操作。
⑦ 最后一个select:用于对临时表的查询。
当然,语法并不固定,只要 在第一个select中进行表初始化,第二个select中基于上一次结果集进行递归查询 即可
案例:
① 初始化临时表t的num字段数据为1,递归获取到3
with recursive t(num) as (
select 1
union all
select num + 1 from t where num + 1 <= 3
)
select num from t;
递归查询过程如下:
初始化空表
遍历上一次结果集(1),在此基础上获取新数据(2)
遍历上一次结果集(2),在此基础上获取新数据(3)
自此,递归获取数据完毕!
可以看到每次迭代都是从上一次结果集中遍历取数据,然后将新的结果集放到表中,直到递归终止。
② 获取节点7的所有祖先
sql脚本:
create table category
(
id bigint auto_increment primary key,
name varchar(20) not null,
parent bigint
);
INSERT INTO `mysql-learn`.category (id, name, parent) VALUES (1, '1', null);
INSERT INTO `mysql-learn`.category (id, name, parent) VALUES (2, '2', 1);
INSERT INTO `mysql-learn`.category (id, name, parent) VALUES (3, '3', 1);
INSERT INTO `mysql-learn`.category (id, name, parent) VALUES (4, '4', 2);
INSERT INTO `mysql-learn`.category (id, name, parent) VALUES (5, '5', 2);
INSERT INTO `mysql-learn`.category (id, name, parent) VALUES (6, '6', 3);
INSERT INTO `mysql-learn`.category (id, name, parent) VALUES (7, '7', 6);
递归查询语句
with recursive ancestor(name, parent) as (
select name,parent from category where id = 7
union all
select c.name, c.parent from category c, ancestor a where c.id = a.parent
)
select * from ancestor;