关于项目中-----多级目录/菜单接口的写法（第一章SQL篇）

多级目录/菜单接口的写法（第一章SQL篇）

例如：

再一般项目中或多或少都会遇到这种多级菜单的需求，于此我稍微总结了一种写法
基于springboot来构建的技术栈，测试工具用的swagger2

目录结构

1. 首先看到TestMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bobo.cd2006swagger.Dao.mapper.TestMapper">
    <resultMap id="test" type="com.bobo.cd2006swagger.pojo.Test">
        <id property="id" column="id"></id>
        <result property="name" column="name"></result>
        <result property="parent_id" column="parent_id"></result>
    </resultMap>
    <select id="findAllTestData" resultMap="test" parameterType="int">
        SELECT t3.id,t3.name,t3.parent_id
        FROM
        (SELECT t1.*,t2.*,
          IF(FIND_IN_SET(t1.parent_id, @pid) > 0, @pid := CONCAT(@pid, ',', t1.id), 0) AS isChild
        FROM test AS t1 ,
        (SELECT @pid := #{id}) AS t2) AS t3
        WHERE t3.isChild != 0
        UNION
        SELECT * FROM test WHERE id = @pid
    </select>
</mapper>

2. 这里的SQL语句使用了递归，查询了所有本类和子类的所有数据出来
   测试数据表是这样的：
   
   构成多级目录结构通过一张表实现。

#递归查询父节点下的所有子节点 包括自己
SELECT * FROM test WHERE id = @pid
UNION 
SELECT t3.id,t3.name,t3.parent_id
FROM
(SELECT t1.*,t2.*,
IF(FIND_IN_SET(t1.parent_id, @pid) > 0, @pid := CONCAT(@pid, ',', t1.id), 0) AS isChild
FROM test AS t1 ,
(SELECT @pid := 1) AS t2) AS t3
WHERE t3.isChild != 0

SQL语句查询结果为：

这里将所有广东省一级目录下的子类全部查询出来了，相信有一部分人看到这里感觉到懵懵的。

我们来解析一下这个SQL语句
首先：

#1. @pid 全局变量
SELECT t1.*,t2.*
FROM test AS t1 , 
(SELECT @pid := 1) AS t2

查询结果为

然后：

#2.   IF(x,y,z)相当于三目运算符 
#     FIND_IN_SET()函数:如果t1.parent_id被包含在@pid中时
#     则这样子显示@pid := CONCAT(@pid, ',', t1.id)否则显示0
SELECT t1.*,t2.*,
IF(FIND_IN_SET(t1.parent_id, @pid) > 0, @pid := CONCAT(@pid, ',', t1.id), 0) AS isChild
FROM test AS t1 ,
(SELECT @pid := 1) AS t2

查询结果为：递归查询出和id=1相关的数据

再然后：

#3.查询父ID下的所有子ID不包括父类
SELECT t3.id,t3.name,t3.parent_id
FROM
(SELECT t1.*,t2.*,
IF(FIND_IN_SET(t1.parent_id, @pid) > 0, @pid := CONCAT(@pid, ',', t1.id), 0) AS isChild
FROM test AS t1 ,
(SELECT @pid := 1) AS t2) AS t3
WHERE t3.isChild != 0

查询结果为：将上边的数据当成一张表 在次查询 id=1 下的所有子类

最后把父类添加进去：

SELECT * FROM test WHERE id = @pid
UNION 
SELECT t3.id,t3.name,t3.parent_id
FROM
(SELECT t1.*,t2.*,
IF(FIND_IN_SET(t1.parent_id, @pid) > 0, @pid := CONCAT(@pid, ',', t1.id), 0) AS isChild
FROM test AS t1 ,
(SELECT @pid := 1) AS t2) AS t3
WHERE t3.isChild != 0

查询结果：

总体思路就是通过递归并且判断所有与id=1有关的行做上标记，然后取出想要的值。
这里的@pid=X ，X就是你要查询的一级目录ID。