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 结构/数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 结构/数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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');

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

1
2
3
4
5
6
7
8
9
# 查询物理分类及其子级
select *
from question_type
where path regexp concat(
',', 7,
',|^', 7,
',|,', 7,
'$|^', 7,
'$');

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

在应用层递归查询完成

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

Domain 实体

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@TableName("question_type")
public class QuestionType implements Serializable {
/**
* 问题编号
*/
private Long id;
/**
* 问题父分类编号,根节点为 0
*/
private Long parentId;
/**
* 编号名称
*/
private String name;
/**
* 全路径,每级使用 , 分割
*/
private String path;
// getter / setter
}

Service 及其实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
@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);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?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"
1
2
3
4
5
6
7
8
9
10
@Repository
public interface QuestionTypeDao {
/**
* 根据父分类 id 递归查询子分类(不包含父分类本身)
*
* @param parentId 分类 id
* @return 查询到的分类树
*/
List<QuestionType> selectRecursiveByParentId(@Param("parentId") Long parentId);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?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 递归查询
https://blog.rxliuli.com/p/040c6e4f06dc481f9ea294bbc6f6c53a/
作者
rxliuli
发布于
2020年4月18日
许可协议