目录
方法1:懒加载,首先加载根节点,然后点击节点时,根据点击节点的id加载子节点
准备工作
DROP TABLE IF EXISTS `test_recursive`;
CREATE TABLE `test_recursive` (
`id` int(11) NULL DEFAULT NULL,
`pid` int(11) NULL DEFAULT NULL,
`node_name` varchar(255) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `test_recursive`(`id`, `pid`, `node_name`) VALUES (1, NULL, '某某省1');
INSERT INTO `test_recursive`(`id`, `pid`, `node_name`) VALUES (101, 1, '某某市1_1');
INSERT INTO `test_recursive`(`id`, `pid`, `node_name`) VALUES (102, 1, '某某市1_2');
INSERT INTO `test_recursive`(`id`, `pid`, `node_name`) VALUES (103, 1, '某某市1_3');
INSERT INTO `test_recursive`(`id`, `pid`, `node_name`) VALUES (10101, 101, '某某县1_1_1');
INSERT INTO `test_recursive`(`id`, `pid`, `node_name`) VALUES (1010101, 10101, '某某区1_1_1_1');
INSERT INTO `test_recursive`(`id`, `pid`, `node_name`) VALUES (2, NULL, '某某省2');
INSERT INTO `test_recursive`(`id`, `pid`, `node_name`) VALUES (201, 2, '某某市2_1');
INSERT INTO `test_recursive`(`id`, `pid`, `node_name`) VALUES (20101, 201, '某某县2_1_1');
准备工作就是做了一个典型的树结构表,id代表主键,pid代表该id的父节点的id
某某省1
某某市1_1
某某县1_1_1
某某区1_1_1_1
某某市1_2
某某市1_3
某某省2
某某市2_1
某某县2_1_1
应用场景
问题1:我们需要加载这颗树
这就涉及到递归查询了
如果用代码写,我们需要多次请求数据库进行递归查询 组织这颗树结构
方法1:懒加载,首先加载根节点,然后点击节点时,根据点击节点的id加载子节点
findRoot();
findNodeByPid(String pid);
方法2:预加载,后台需要写递归查询,然后组装树结构
/**
逻辑代码
*/
loadTree(){
// 加载所有根节点
list<Node> roots= sqlMapper.selectList("select * from xx where pid is null")
// 遍历根节点组装子节点
for(root:roots){
flindChild(root)
}
return roots;
}
flindChild(node){
list<Node> childs = sqlMapper.selectList("select * from xx where pid = #{node.id}")
for(node:nodes){
node.childs.add(node)
// 递归调用
findChild(node)
}
}
问题2:查询101节点及其子节点⭐
正常之前我们需要写递归查询然后拼接起来,或者写存储过程递归调用,现在我们可以直接用recursive 语法,让mysql帮我们完成递归查询一次性返回。
-- 创建一个临时递归视图
with recursive tree(id,pid,node_name) as (
-- 根节点查询语句
select id,pid,node_name from test_recursive roots where id = 101
union all
-- 子节点 join 递归视图 查询语句
select c.id,c.pid,c.node_name from test_recursive c inner join tree on c.pid = tree.id
)
select * from tree;
问题3:列出1-100的所有数
with recursive rec(id) as (
select 1 as id
union all
select id +1 from rec where id <100
)
select id from rec
总结
with recursive xx(xx1,xx2,xx3) as () select xx1,xx2,xx3 from xx
这个语法就是创建一个临时递归视图(CTE 语法),mysql内置的递归查询语法,不同于后端写递归查询,我们不需要多次请求数据库,我们只需要根据查询结果进行组装即可,因此可以减轻mysql服务器负载,不过既然是递归,当数据量很大时,这样写压力依旧会很大
对递归出来的数据进行批量操作用这个语法比较方便
update xx where id in (with recursive xx(xx1,xx2,xx3) as () select id from xx)
另外该语法 mysql 8.x以上版本才有
防止递归次数过多导致的异常处理:
通过设置递归深度、最大执行时间来限制递归执行,能够强制结束递归
cte_max_recursion_depth 参数默认值为1000,限制递归深度,将被强制终止。
max_execution_time 参数限制查询的最大执行时间,超过该时间,也将被强制终止。
set xxx = x