mysql之递归sql

递归sql在一些公司是不允许使用的，会涉及数据库压力，所以会在代码里递归查询，但有些公司开发流程没有规定，且数据库数据量不大，之前写过好几遍了，老是记不住，记录一下

通过父级id查询所有子集（不包括父级）

方案一：不带查询的本层级

# 0.4s
select  id,
        name,
        parent_id 
from    (select * from products # 表名
         order by parent_id, id) products_sorted,
        (select @pv := '你的id') initialisation
where   find_in_set(parent_id, @pv)
and     length(@pv := concat(@pv, ',', id))

方案二：带查询本层级

# 1.55s
SELECT
	ID.LEVEL,
	org.* 
FROM
	(
	SELECT
		@ids AS _ids,
		( SELECT @ids := GROUP_CONCAT( id ) FROM sys_org WHERE FIND_IN_SET(parent_id, @ids ) ) AS cids,
		@l := @l + 1 AS LEVEL 
	FROM
		sys_org,
		( SELECT @ids := '查询的id', @l := 0 ) b 
	WHERE
		@ids IS NOT NULL 
	) ID,
	sys_org org 
WHERE
	FIND_IN_SET( org.id, ID._ids ) 
ORDER BY
	LEVEL

从子集向上递归

方案一：

SELECT T2.id, T2.name, T2.parent_id,T1.lvl
FROM
	(
	SELECT
		@r AS rid,
	  (SELECT @r := parent_id FROM sys_org WHERE id = rid) AS parent_id,
		@l := @l + 1 AS lvl 
	FROM
		( SELECT @r := '你要查询的id', @l := 0 ) vars,
		sys_org h #递归表
	WHERE
		@r <> 0 #父id条件
	) T1
	JOIN sys_org T2 ON T1.rid = T2.id 
ORDER BY
	T1.lvl DESC

方案二：

SELECT T2.id, T2.parent_id, T2.name
FROM (SELECT @r AS _id, (SELECT @r := parent_id FROM sys_org WHERE id = _id) AS parent_id
        FROM (SELECT @r := '你要查询的id') vars,
        sys_org h WHERE @r <> 0) T1 JOIN sys_org T2 ON T1._id = T2.id

mysql8.0神级递归

 
WITH RECURSIVE org_tree (id,parent_id) AS (

# 初始查询，选择根节点（例如，没有父节点的节点）  
  SELECT id, parent_id
  FROM sys_org #要递归的表明
  WHERE parent_id = 0 #递归的父id
 
  UNION ALL
 
  -- 递归查询，选择当前层级的子节点
  SELECT o.id, o.parent_id
  FROM sys_org o
  INNER JOIN org_tree ot ON o.parent_id = ot.id
)
SELECT * FROM org_tree;

# 查询mysql版本
SELECT VERSION();

这里 org_tree为虚拟表，在递归部分，我们先通过一个初始查询(sys_org )得到一些初始的结果。然后我们通过UNION ALL运算将初始结果集合并到递归查询结果中。接下来，在每次递归查询中，我们使用前一次递归的结果(org_tree)与递归查询(sys_org )进行运算，并使用WHERE条件过滤掉不需要的数据。最后，在终止条件部分中，我们使用一个条件来判断递归查询何时停止。当递归查询到终止条件时，递归查询结束，最终结果被返回

WITH RECURSIVE：
# 表示要使用递归查询的方式处理数据。
UNION：
# 表示将两个查询结果集进行联合，使用UNION ALL则表示保留重复数据。
SELECT * FROM 临时表：
# 表示最终返回的查询结果集，可以通过临时表查询表中的列名进行指定。

如果还是不是很理解可以参考：MySQL递归查询超详细–保姆级别讲解