mysql -- 递归查询所有子节点

背景

有个需求，查询一个文件中的所有子目录的文件及文件夹。

实现

1. 数据库设计

首先， 建立一张 t_files 表， 模拟文件树结构。

  create table `t_files` (
  id int auto_increment,
  file_name varchar(100) default '',
  file_type tinyint(4) default 0,
  parent_id int null,
  update_time timestamp null,
  primary key(id),
  unique key(file_name, parent_id) 
);

模拟数据：

表中数据对应文件树结构为： 根目录下有两个文件夹， 1个文件； 文件夹1下有一个文件夹3和一个文件；而文件夹3下又有一个文件03。

---

需求： 如何用sql语句， 递归查询到文件夹1下的所有文件夹及文件?

---

2. 实现分析

思路：
第一步： 先找到文件1下的子目录

select *from t_files where parent_id = 1;

结果：

第二步： 找到子目录下文件夹下的子目录
这时候， 我们遇到了两个难点：
（1） 子目录下文件夹有多个， 怎么查询？
（2） 子目录下如果还有文件夹， 那么就需要一直循环下去， 什么时候结束？

解决方法：
（1） 多个同时查询， 我们需要用到mysql函数;
group_concat（）: 多条记录合成一条记录

select group_concat(id) as ids from t_files where parent_id =1;

结果：

find_in_set（str， strlist） ： 在多条记录中查询特定列
str 要查询的字符串
strlist 字段名 参数以”,”分隔 如 (1,2,6,8)

select group_concat(id) as ids from t_files where find_in_set(parent_id, '1, 4');

结果：

（2） 循环如何结束， 需要用mysql FUNCTION 函数。
while id is null
– 自然语言
当所有子目录下没有文件夹就结束

3. mysql 实现完整语句

根据以上思路，我们可以通过以下mysql函数，完成递归查询。

DELIMITER //
CREATE FUNCTION `getChildLst`(rootId INT)

RETURNS varchar(1000) READS SQL DATA

BEGIN
  DECLARE sTemp VARCHAR(1000);

  DECLARE sTempChd VARCHAR(1000);

  SET sTemp = '$';

  SET sTempChd =cast(rootId as CHAR);

  WHILE sTempChd is not null DO

    SET sTemp = concat(sTemp,',',sTempChd);

    SELECT group_concat(id) INTO sTempChd FROM t_files where FIND_IN_SET(parent_id,sTempChd)>0;

  END WHILE;

  RETURN sTemp;

END  //
DELIMITER ;

调动语句：

select *from t_files where find_in_set(id, getChildLst(1));

结果：

是的， 我们完成了需求： 查询到文件夹1下的所有文件夹及文件。

4. 效果图

我们应用它，实现了一个h5发布系统， 即可以在线操作文件。 效果如下：

总结

1. 文件树结构， 数据库设计采用parent_id 这个字段， 来标识上一级目录。
2. mysql 中 group_concat() 函数实现多条记录形成一条。
3. mysql 中 find_in_set() 函数实现在集合中查询。
4. mysql 中 使用自定义函数 function 可以实现较复杂的功能。
5. 分享下，h5发布系统技术栈。
   前端： vue2 + element + axios
   后台： node + express + fs-extra + mysql

欢迎一起学习，交流。