mysql8 recursive 用法、场景（orcale的connect by ）

准备工作

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