SQL 递归 ---- WITH RECURSIVE 的用法

开发中遇到了一个需求，传递一个父类id，获取父类的信息，同时获取其所有子类的信息。 首先想到的是通过程序中去递归查，但这种方法着实孬了一点，于是想，sql能不能递归查询呢？ 这不，一查发现还真能

一、说明

WITH RECURSIVE 是 SQL 中的一个强大特性，通常用于处理递归查询，常见于 PostgreSQL、MySQL 8.0 及以上、SQL Server 等数据库系统。它允许你在一个查询中引用其自身的结果集，这对于处理具有层次结构的数据（如树状结构或图结构）非常有用。

二、语法

sql">WITH RECURSIVE tmp_table (column_list) AS (-- 初始查询（非递归部分）initial_queryUNION (ALL)-- 递归查询部分recursive_query
)
SELECT * FROM tmp_table;

WITH RECURSIVE：递归关键字
tmp_table：可以理解为一个临时表
column_list：定义结果集中的列列表，也可省略
initial_query：初始查询，它是递归的基础，通常是一个非递归的查询，为递归查询提供起始数据。
UNION ALL：将初始查询和递归查询的结果集合并。注意，使用 UNION ALL 是为了保留重复行，而 UNION 会去除重复行。
recursive_query：递归查询部分，会引用 tmp_table 自身的结果集。

三、举例说明

建表，初始化一部分数据 (数据库环境： MYSQL 8.0 以上)

sql">CREATE TABLE `geographic_info` (`id` int NOT NULL AUTO_INCREMENT COMMENT '自增编号',`name` varchar(20) NOT NULL COMMENT '名称',`parent_id` int  NULL COMMENT '父节点id',PRIMARY KEY (`id`)
) COMMENT='地理信息表';-- init data
insert into geographic_info(id,name,parent_id) value (1,'安徽省',null);
insert into geographic_info(id,name,parent_id) value (2,'合肥市',1);
insert into geographic_info(id,name,parent_id) value (3,'高新区',2);
insert into geographic_info(id,name,parent_id) value (4,'某某小区',3);

创建递归查询

sql">-- 自下而上进行递归
-- 通过某某小区的id，查询出其父类及以上层级的数据
WITH RECURSIVE descendants AS (SELECT gi.id, gi.name,gi.parent_id from geographic_info giWHERE gi.id = 4UNION SELECT gi.id, gi.name,gi.parent_id from geographic_info gi join descendants d on gi.id = d.parent_id
)
SELECT * FROM descendants order by id;

返回数据

sql">-- 自上而下进行递归
-- 通过安徽省的id，查询出其所有子类的数据（注：此处不合理查询请忽略，仅仅为了举例）
WITH RECURSIVE descendants AS (SELECT gi.id, gi.name from geographic_info giWHERE gi.id = 1UNION SELECT gi.id, gi.name from geographic_info gi 
join descendants d on gi.parent_id = d.id
)
SELECT * FROM descendants order by id;

返回数据

四、注意事项

终止条件：递归最需要关注的地方就是终止条件，处理不当就会导致无限递归。
性能：对于深度较大的树结构或图结构，递归查询可能会影响性能。在某些情况下，使用其他存储方式（如闭包表）可能会更高效
数据库支持：并非所有数据库都支持 WITH RECURSIVE。例如，MySQL 从 8.0 开始支持，而一些较旧的版本不支持，使用前请确认你的数据库是否支持。