【MySQL】MySQL8.0新特性CTE递归实战

1.使用场景

首先说一下使用场景，在项目的组织架构中是使用id 和parent_id 关联产生一级一级的父子关系。人员管理上是挂了组织的，但是只挂了最详细子组织，父级没有关联，所以在页面点击最高级组织或者次高级组织时按照平时代码中业务层使用递归查询处理效率很慢，经常使页面崩溃。查阅资料时候发现MySQL8.0有个新特性是CTE递归废话不多说直接上实战。

2.实战代码

 List<SysUser> selectStudentList(@Param("deptId") Long deptId);

<select id="selectStudentList" parameterType="long" resultMap="SysUserResult">
        WITH RECURSIVE DeptTree AS (
            SELECT id_
            FROM org_group
            WHERE id_ = #{deptId}
            UNION ALL
            SELECT og.id_
            FROM org_group og
                     INNER JOIN DeptTree dt ON og.parent_id_ = dt.id_
        )
        SELECT su.*
        FROM sys_user su
                 INNER JOIN DeptTree dt ON su.dept_id = dt.id_

    </select>

不需要业务层进行多余的处理，优雅！！！

3.介绍

• 形成 CTE 结构的基本结果集的初始查询（initial_query），初始查询部分被称为锚成员。
• 递归成员由一个 union all 或 union distinct 运算符与锚成员相连。
• 终止条件是当递归成员没有返回任何行时，确保递归停止。

执行顺序：
1.将成员分为两个：锚点和递归成员。

2.执行锚成员形成基本结果集，并使用该基本结果集进行下一次迭代。

3.将结果集作为输入执行递归成员，并将 id_作为输出。

4.重复第三步，直到递归成员返回一个空结果集。

5.使用 union all 运算符将结果集从dt 到 su组合。

4.限制

递归成功不能饱和以下结构

1. 聚合函数
2. group by子句
3. order by 子句
4. limit 子句
5. distinct

上述约束不适用于锚点成员。 另外，只有在使用 union 运算符时，要禁止 distinct 才适用。 如果使用 union distinct 运算符，则允许使用 distinct。递归成员只能在其子句中引用 CTE 名称，而不是引用任何子查询。