本文正在参加「技能专题19期 闲谈数据库技能」活动

前语

  • 开发中树形结构应该是很常见的一种数据结构了。而在数据库方面往往也都伴随相应的树形设计。在 mysql 中通过 parent_id 来绑定其上游，从而达到树形结构的存储，可是在查询的进程中就需求咱们将 List 列表转成咱们抱负中的 Tree 树。

构建树

List<Location> locations = this.baseMapper.selectList(queryWrapper);
Map<String, List<Location>> collect = locations.stream().collect(Collectors.groupingBy(Location::getId));
List<QueryLocationDto> resultList = new ArrayList<>();
List<Location> parentLocation = getParentLocation(1, collect, id);
if (CollectionUtils.isNotEmpty(parentLocation)) {
  for (Location location : parentLocation) {
    QueryLocationDto dto = new QueryLocationDto();
    BeanUtils.copyProperties(location, dto);
    resultList.add(dto);
   }
}

private List<Location> getParentLocation(int level, Map<String, List<Location>> collect, String id) {
    List<Location> locationList = new ArrayList<>();
    if (collect.containsKey(id)) {
      Location location = collect.get(id).get(0);
      locationList.add(location);
      String superid = location.getSuperid();
      locationList.addAll(getParentLocation(level + 1, collect, superid));
     }
    return locationList;
   }
  • 信任大部分咱们 都是通过 Java 来处理的。 其间 getParentLocation 便是用递归不断的去构建上下级联系。这种办法也是我比较引荐的，由于这样就把责任分的很清楚 Java 负责处理事务 ，数据库 就只是用来做数据的耐久化，这也便利咱们对数据库切换与升级。否则在替换其他数据库时还需求考虑是否支撑递归属性。
public <T> List<OperatorTree> getTree(List<T> parentList , Map<?, List<T>> collectMap, OperatorTreeInterface operatorTreeInterface) throws InvocationTargetException, IllegalAccessException, NoSuchMethodException {
    if (org.apache.commons.collections4.CollectionUtils.isEmpty(parentList)) {
      return new ArrayList<>();
     }
    List<OperatorTree> list = new ArrayList<>();
    OperatorMapping operatorMapping = operatorTreeInterface.operatorMapping();
    for (T t : parentList) {
      OperatorTree tree = new OperatorTree();
      tree.setId(BeanUtils.getProperty(t, operatorMapping.getId()));
      tree.setLabel(BeanUtils.getProperty(t, operatorMapping.getLabel()));
      tree.setRealData(t);
      if (collectMap.containsKey(tree.getId())) {
        List<T> ts = collectMap.getOrDefault((tree.getId()), new ArrayList<>());
        tree.setChildren(getTree(ts, collectMap, operatorTreeInterface));
       } else {
        try {
          List<T> ts = collectMap.getOrDefault(Long.valueOf(tree.getId()), new ArrayList<>());
          tree.setChildren(getTree(ts, collectMap, operatorTreeInterface));
         } catch (Exception e) {
          tree.setChildren(getTree(new ArrayList<>(), collectMap, operatorTreeInterface));
         }
       }
      list.add(tree);
     }
    return list;
   }
  • 有的时分为了能够量产化，这儿我也针对项目的场景运用泛型进行一次封装。有兴趣能够自己针对自己的项目场景进行封装。这样树形与列表的转化就无须每次冗余了。可是今天咱们的重点是如何运用 mysql 来完成递归查询， 尽管这种办法个人不引荐运用，但咱们还是需求了解 mysql 的特性的。

mysql巧妙化解递归查询树形数据 | 纯sql

  • 如图咱们先在数据库中创立一张 test 表， 表里构建一棵树出来。 祖父→父亲→孙子 。

请依据选中节点查询其上游联系

  • 啥意思呢？便是依据你我想找到你家血脉联系。

mysql巧妙化解递归查询树形数据 | 纯sql

  • 这个事务自身没有难点，咱们只需求一步一步的去寻觅即可。可是从技能层面上看就有点搞头了。首要咱们不确定是多少层，这就无法通过代码堆砌的办法进行完成了。只能动态的去递归查询。
SELECT t2.id, t2.`name`,t1.lvl
FROM
  (
    SELECT 
    @r AS _id,
    (SELECT @r := parent_id FROM test WHERE id = _id) AS parent_id,
    @l := @l + 1 AS lvl
    FROM
    (SELECT @r := 3, @l := 0) vars, test AS h
    WHERE @r <> 0 
  ) t1
JOIN test t2
ON t1._id = t2.Id
order by t1.lvl

mysql巧妙化解递归查询树形数据 | 纯sql

  • 通过上述代码咱们即可完结宗族血脉的查询。能够清楚的查出宗族的详细支脉。

解析

  • 上面的 sql 尽管能够完成递归，可是如同和咱们平常触摸的 sql 不大一样。这儿咱们简略剖析下
  • 首要 mysql 是支撑咱们用户界说目标的。比如下面这个 sql 
set @name = '123';
select @name from dual;
  • 履行上面的sql 你会发现查出来的便是 123 。 这便是运用到 mysql 中变量界说功用。 通过 @ 来进行目标的界说以及赋值。

mysql巧妙化解递归查询树形数据 | 纯sql

  • 首要 mysql的履行次序是由里及外 。 便是说越在内部越先履行。所以针对上面的 SQL 咱们将它收拾下次序能够得出。

mysql巧妙化解递归查询树形数据 | 纯sql

  • 留意 (SELECT @r := parent_id FROM test WHERE id = _id) AS parent_id, 这段sql 实际上归属于第二层。图示中并没有列出，太占地方了。

第三层

  • 有了上面的两个知识点铺垫，现在咱们直接看 SELECT @r := 3, @l := 0 是不是就很容易理解了呢？ r、l 便是 mysql 中界说出来的两个变量。分别是3,0 。和咱们上面演示案列中 select @name from dual 是一个意思。
  • 至于 (SELECT @r := 3, @l := 0) vars, test AS h这段就更好理解了。test自身便是一张物理表 ， 咱们界说出来的两个变量也能够理解成一张表。在mysql中查询两种表是为了区别开来正常都是需求各自取别号的。所以咱们界说的变量便是 vars 别号 。 test 别号 h。

第二层

SELECT
    @r AS _id,
    (SELECT @r := parent_id FROM test WHERE id = _id) AS parent_id,
    @l := @l + 1 AS lvl
    FROM
    (SELECT @r := 3, @l := 0) vars, test AS h
    WHERE @r <> 0
  • 第二层能够说是递归的中心 。这儿咱们需求理解 上述代码为什么会发生递归的效果呢？首要咱们知道 mysql 存在 左外衔接、右外衔接、内衔接 。除了这三种还有一种便是 笛卡尔积衔接。什么意思呢？

mysql巧妙化解递归查询树形数据 | 纯sql

  • 也便是说上面咱们的sql FROM (SELECT @r := 3, @l := 0) vars, test AS h 便是为了给咱们构建出一个笛卡尔积 ，而咱们界说的变量其实便是一张表里的一条数据，所以这儿便是将 test 表所有记载都提取出来。

mysql巧妙化解递归查询树形数据 | 纯sql

mysql巧妙化解递归查询树形数据 | 纯sql

  • 也便是说咱们第二层的sql 便是将 test表悉数查出来，然后将其字段进行扩展。说白了这种办法并不是一种真正的递归。可是由于引入了一个变量，所以在扩大的时分和递归一个效果。
  • 可是笛卡尔积查询出来的数据是无法保证两张表的关联性的，所以咱们并没有将 h 表相关字段查出来，由于那底子没用。剩下的便是咱们一开始说的一步一步的查询扩大字段了

第一层

  • 第一层就简略很多了 ，由于第二层就现已查询出来相关的树形数据了，可是由于第二层运用的笛卡尔积 h 表信息没法运用，只是运用了其数量。那么咱们的称号这个时分还没有，第一层的效果便是将节点称号扩大出来。

总结

  • 最后咱们简略总结下，mysql 的查询递归正常运用存储进程来完成。可是上面提到的办法奇妙的完成了递归的效果。理论上上述办法和存储进程相比存在一个优点便是不会死循环。

本文正在参加「技能专题19期 闲谈数据库技能」活动