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,
    '$');

这里使用正则是为了避免出现部分重复的情况,例如 110,直接使用 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 标签实现递归查询的功能。

  1. Mybatis collection 标签可以查询一个集合为字段赋值
  2. 那么我们可以使用 select 指向查询子分类本身
  3. 查询的参数 cloumn 设置为查询出来每一个对象的 id 字段
  4. 更改查询子分类的返回值为 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 递归查询暂且到此为止了。如果吾辈找到了更好的方法,也会继续更新这篇文章的!


本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 协议 ,转载请注明出处!