MySQL树结构数据操作——WITH RECURSIVE

树结构层级查询

WITH RECURSIVE

注：mysql 8.0版本以上；8.0以下需要使用下属 resultMap+collection 的方法进行递归
Mysql 递归

WITH RECURSIVE cte AS (
    SELECT *, 1 as level
    FROM test_simple
    WHERE id = 1
    UNION ALL
    SELECT t.id, t.parent_id, cte.level + 1
    FROM test_simple t
    JOIN cte ON t.parent_id = cte.id
)
SELECT *
FROM cte

首先获取初始值：
SELECT *, 1 as level
FROM test_simple
WHERE id = 1

再将初始值传入递归中：
UNION ALL
SELECT t.id, t.parent_id, cte.level + 1
FROM test_simple t
JOIN cte ON t.parent_id = cte.id
cte即为上一级递归过去的值，需要保证每次返回类型与初始值相同；

示例

-- 查询表中指定项目及其子项目层级
-- 查询当前项目最深层级，查其子，并取最大值
WITH RECURSIVE cte AS (
    SELECT *, 1 as level
    FROM test_simple
    WHERE id = 1
    UNION ALL
    SELECT t.id, t.parent_id, cte.level + 1
    FROM test_simple t
    JOIN cte ON t.parent_id = cte.id
)
-- SELECT max(level) --获取1的子的最深层级
SELECT * --获取1的所有子并显示层级
FROM cte


-- 查询表中指定项目及其父项目层级
-- 查询当前项目层级，查其父，并取最大值
WITH RECURSIVE cte AS (
    SELECT *, 1 as level
    FROM test_simple
    WHERE id = 2
    UNION ALL
    SELECT t.id, t.parent_id, cte.level + 1
    FROM test_simple t
    JOIN cte ON t.id = cte.parent_id
)
SELECT max(level) --获取2的层级
FROM cte

-- 获取项目子项目数，查所有子--不含自身
WITH RECURSIVE cte AS (
    SELECT id, parent_id
    FROM test_simple
    WHERE parent_id = 1
    UNION ALL
    SELECT t.id, t.parent_id
    FROM test_simple t
    JOIN cte ON t.parent_id = cte.id
)
SELECT COUNT(*) AS total_count
FROM cte;

对应mybatis xml语句：

  <select id="xxx" resultType="Integer">
    <!-- 以上sql语句 -->
  </select>

树结构子数量查询

-- 获取项目子项目数，查所有子--含自身
WITH RECURSIVE cte AS (
    SELECT id, parent_id
    FROM test_simple
    WHERE id = 3337037
    UNION ALL
    SELECT t.id, t.parent_id
    FROM test_simple t
    JOIN cte ON t.parent_id = cte.id
)
SELECT COUNT(*) AS total_count
FROM cte;

获取树结构数据

resultMap+collection

collection的select属性可以指定子查询；column将父查询返回属性传入子查询中
子查询类型与父查询类型一致时形成多层级树结构数据

<resultMap id="tree" type="Type">
    <id property="id" column="id" javaType="java.lang.Integer"/>
    <id property="parent_id" column="parentId" javaType="java.lang.Integer"/>
    <collection property="children" select="getChildren" column="{parentId=id}"/>
</resultMap>
<select id="getTree" resultMap="tree">
    select * from test_simple where id = 1
</select>
<select id="getChildren" resultMap="tree">
    select * from test_simple where parent_id = #{parentId}
</select>

父查询参数传递

提取查询参数作为临时表；Type中需要增加这个参数

<resultMap id="tree" type="Type">
    <id property="id" column="id" javaType="java.lang.Integer"/>
    <id property="parent_id" column="parentId" javaType="java.lang.Integer"/>
    <id property="implParam " column="implParam " javaType="java.lang.Integer"/>
    <collection property="children" select="getChildren" column="{parentId=id, implParam=implParam}"/>
</resultMap>
<select id="getTree" resultMap="tree">
    select *，#{param} as implParam from test_simple where id = 1
</select>
<select id="getChildren" resultMap="tree">
    select * from test_simple where parent_id = #{parentId} and my_param=implParam
</select>