MySQL 递归查询

本文最后更新于：2020年12月30日凌晨

场景

最近需要将根据父级分类查询出所有的自己分类，所以却是需要使用 MySQL 实现递归查询的功能。

对于以下数据表（此处简化了）

id	parentId	name
1	0	数学
2	1	高等数学
3	1	线性代数
4	0	英语
5	4	即时翻译
6	4	口语阅读
7	0	物理
8	7	高能物理
9	8	无限能量
10	9	迪克拉之海

SQL 结构/数据

create table question_type (
  id       bigint primary key  not null
  comment '问题编号',
  parentId bigint              not null
  comment '问题父分类编号，根节点为 0',
  name     varchar(20)         not null
  comment '编号名称'
)
  comment '问题编号';
insert into question_type values (1, 0, '数学');
insert into question_type values (2, 1, '高等数学');
insert into question_type values (3, 1, '线性代数');
insert into question_type values (4, 0, '英语');
insert into question_type values (5, 4, '即时翻译');
insert into question_type values (6, 4, '口语阅读');
insert into question_type values (7, 0, '物理');
insert into question_type values (8, 7, '高能物理');
insert into question_type values (9, 8, '无限能量');
insert into question_type values (10, 9, '迪克拉之海');

吾辈只有一个 id，想要查询出所有的子级

解决

这个问题在网络上流传着各种各样的解决方案

使用额外的字段存储节点全路径
在应用层递归查询完成
使用 Mybatis collection 标签
使用存储过程
使用 SQL 视图
使用单条 SQL 实现

吾辈目前只尝试了其中三种

使用额外的字段存储节点全路径

有人提出使用一个额外的字段记录当前节点的全路径，每一级使用 , 进行分割，所以吾辈的数据表变成了下面这样

id	parentId	name	path
1	0	数学	0,1
2	1	高等数学	0,1,2
3	1	线性代数	0,1,3
4	0	英语	0,4
5	4	即时翻译	0,4,5
6	4	口语阅读	0,4,6
7	0	物理	0,7
8	7	高能物理	0,7,8
9	8	无限能量	0,7,8,9
10	9	迪克拉之海	0,7,8,9,10

SQL 结构/数据

create table question_type (
  id       bigint primary key  not null
  comment '问题编号',
  parentId bigint              not null
  comment '问题父分类编号，根节点为 0',
  name     varchar(20)         not null
  comment '编号名称',
  path     varchar(100)        not null
  comment '全路径，每级使用 , 分割'
)
  comment '问题编号';
insert into question_type values (1, 0, '数学', '0,1');
insert into question_type values (2, 1, '高等数学', '0,1,2');
insert into question_type values (3, 1, '线性代数', '0,1,3');
insert into question_type values (4, 0, '英语', '0,4');
insert into question_type values (5, 4, '即时翻译', '0,4,5');
insert into question_type values (6, 4, '口语阅读', '0,4,6');
insert into question_type values (7, 0, '物理', '0,7');
insert into question_type values (8, 7, '高能物理', '0,7,8');
insert into question_type values (9, 8, '无限能量', '0,7,8,9');
insert into question_type values (10, 9, '迪克拉之海', '0,7,8,9,10');

现在，我们可以很简单的查询了子级信息了

# 查询物理分类及其子级
select *
from question_type
where path regexp concat(
    ',', 7,
    ',|^', 7,
    ',|,', 7,
    '$|^', 7,
    '$');

这里使用正则是为了避免出现部分重复的情况，例如 1 和 10，直接使用 like 的话可能会出现错误查询额外的数据。

在应用层递归查询完成

在不修改数据表结构的情况下有什么方法能递归查询么？答案是可以的！我们可以在程序中递归查询数据库，虽然效率上会低点，但对于不能修改的数据库而言还是相当有用的。

Domain 实体

@TableName("question_type")
public class QuestionType implements Serializable {
    /**
     * 问题编号
     */
    private Long id;
    /**
     * 问题父分类编号，根节点为 0
     */
    private Long parentId;
    /**
     * 编号名称
     */
    private String name;
    /**
     * 全路径，每级使用 , 分割
     */
    private String path;
    // getter / setter
}

Service 及其实现

public interface QuestionTypeService {
    /**
     * 递归查询节点及其子节点
     *
     * @param rootId 查询的根节点 id
     * @return 子节点列表
     */
    List<QuestionType> selectRecursiveById(Long rootId);
}

@Service
public class QuestionTypeServiceImpl implements QuestionTypeService {
    @Autowired
    private QuestionTypeDao baseMapper;

    @Override
    public List<QuestionType> selectRecursiveById(Long rootId) {
        final List<QuestionType> list = baseMapper.selectListByParentId(rootId).stream()
                .flatMap(qt -> selectRecursiveById(qt.getId()).stream())
                .collect(Collectors.toList());
        Optional.ofNullable(baseMapper.selectById(rootId)).ifPresent(list::add);
        return list;
    }
}

Dao/Mapper.xml

@Repository
public interface QuestionTypeDao {
    /**
     * 根据 id 查询分类
     *
     * @param id 分类 id
     * @return 查询到的分类数据
     */
    QuestionType selectById(@Param("id") Long id);

    /**
     * 根据父节点 id 查询一级子节点
     *
     * @param parentId 父节点 id
     * @return 一级子节点列表
     */
    List<QuestionType> selectListByParentId(@Param("parentId") Long parentId);
}

<?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.rxliuli.example.mybatisplussqlinjector.dao.QuestionTypeDao">
    <select id="selectListByParentId"
            resultType="com.rxliuli.example.mybatisplussqlinjector.entity.QuestionType">
        select *
        from question_type
        where parentId = #{parentId};
    </select>
    <select id="selectById" resultType="com.rxliuli.example.mybatisplussqlinjector.entity.QuestionType">
        select *
        from question_type
        where id = #{id};
    </select>
</mapper>

调用的时候只要传入一个根节点 id 就可以查找到所有节点及其所有子节点了

使用 Mybatis collection 标签

如果你使用的 ORM 是 Mybatis，那么也可以使用 Mybatis collection 标签实现递归查询的功能。

Mybatis collection 标签可以查询一个集合为字段赋值
那么我们可以使用 select 指向查询子分类本身
查询的参数 cloumn 设置为查询出来每一个对象的 id 字段
更改查询子分类的返回值为 resultMap="RecursiveMap"

@Repository
public interface QuestionTypeDao {
    /**
     * 根据父分类 id 递归查询子分类（不包含父分类本身）
     *
     * @param parentId 分类 id
     * @return 查询到的分类树
     */
    List<QuestionType> selectRecursiveByParentId(@Param("parentId") Long parentId);
}

<?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.rxliuli.example.mybatisplussqlinjector.dao.QuestionTypeDao">
    <!-- 定义一个结果映射 -->
    <resultMap id="RecursiveMap" type="com.rxliuli.example.mybatisplussqlinjector.entity.QuestionType">
        <result property="id" column="id"/>
        <result property="parentId" column="parentId"/>
        <result property="name" column="name"/>
        <result property="path" column="path"/>
        <!-- 这里是关键，定义集合字段，元素类型，查询函数以及对应的列 -->
        <collection property="childrenList" ofType="com.rxliuli.example.mybatisplussqlinjector.entity.QuestionType"
                    select="com.rxliuli.example.mybatisplussqlinjector.dao.QuestionTypeDao.selectRecursiveByParentId"
                    column="id"/>
    </resultMap>

    <!-- 正常查询子分类，唯一修改之处就是 resultMap -->
    <select id="selectRecursiveByParentId" resultMap="RecursiveMap">
        select *
        from question_type
        where parentId = #{parentId}
    </select>
</mapper>

那么，关于 MySQL 递归查询暂且到此为止了。如果吾辈找到了更好的方法，也会继续更新这篇文章的！

其他 > MySQL

#sql

MySQL 递归查询

https://blog.rxliuli.com/p/040c6e4f06dc481f9ea294bbc6f6c53a/

作者

rxliuli

发布于

2020年4月18日

许可协议

Windows 下 MongoDB 便携版安装与初始化上一篇

MySQL 行列转换下一篇