对于数据库中的树形结构数据，我们经常会有一种需求，给定一个父节点，查询这个父节点下所有的子节点，或者给定一个子节点，查询这个子节点上的所有父节点。
接下来，我将介绍如何在MySql中使用函数来实现递归。
1.创建表
DROP TABLE IF EXISTS `address`;
CREATE TABLE `address` (
`id` bigint(20) AUTO_INCREMENT COMMENT '主键',
`address_name` varchar(500) DEFAULT NULL,
`parent_id` bigint(20) DEFAULT 0 COMMENT '父节点',
`level_path` varchar(2000) DEFAULT NULL COMMENT '地址路径',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.初始化数据
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('中国',0, '中国');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('河南省',3 , '中国/河南省');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('郑州市',4 , '中国/河南省/郑州市');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('1级县城',5 , '中国/河南省/郑州市/1级县城');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('2级县城',5 , '中国/河南省/郑州市/2级县城');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('3级县城',5 , '中国/河南省/郑州市/3级县城');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('A乡',6, '中国/河南省/郑州市/1级县城/A乡');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('B乡',6, '中国/河南省/郑州市/1级县城/B乡');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('C乡',6, '中国/河南省/郑州市/1级县城/C乡');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('A-1村',9, '中国/河南省/郑州市/1级县城/A乡/A-1村');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('B-1村',10, '中国/河南省/郑州市/1级县城/B乡/B-1村');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('C-1村',11, '中国/河南省/郑州市/1级县城/C乡/C-1村');
3.查询郑州市下所有的子地区
- 在这里多说一句，如果是Oracle我们直接可以根据start with connect by prior递归来实现
SELECT *
FROM address
START WITH address_name='郑州市'
CONNECT BY PRIOR ID=parent_id
- 如果是mysql的话，我们可以使用函数来实现递归查询
在可视化工具navicat下创建函数步骤如下：
-
打开数据库连接找到函数
image.png -
右键->新建函数->选择函数
image.png -
输入参数列表，这里是函数的参数，可以是多个
image.png
-然后如数返回值类型和长度
image.png
-
然后点击完成，接下来就可以写自定义函数的逻辑了
image.png 函数逻辑如下，然后保存起一个函数名称即可 selectChildByParentId
BEGIN
# 定义一个变量用来返回结果
DECLARE finalVar VARCHAR(2000);
# 定义一个临时变量
DECLARE tempVar VARCHAR(2000);
# 设置默认值
SET finalVar='$';
# 转换入参类型
SET tempVar = CAST(parent_id AS CHAR);
# 循环体，如果当前的临时变量中没有值，为空的情况下跳出循环，也就是说没有子节点了
WHILE tempVar IS NOT NULL DO
# 将得到的子节点保存到变量中
SET finalVar= CONCAT(finalVar,',',tempVar);
# 根据父Id查询所有的子节点
SELECT GROUP_CONCAT(t.id) INTO tempVar FROM address t WHERE FIND_IN_SET(t.parent_id,tempVar)>0;
# 结束循环
END WHILE;
# 返回结果,得到的是包含入参以及下面的所有子节点
RETURN finalVar;
END
- 开始调用该函数：
select selectChildByParentId(5);
得到父节点5下面的所有子节点
image.png
- 此时可以查询父节点为郑州市下面的所有子区域了
SELECT * FROM address WHERE FIND_IN_SET(id,selectChildByParentId(5));
image.png
4. 当然了我们也可以查询郑州市上的所有父节点
同样的我们写一个函数用来获取郑州市的所有父节点。
- 函数如下：
BEGIN
# 定义一个变量用来返回结果
DECLARE finalVar VARCHAR(2000);
# 定义一个临时变量
DECLARE tempVar BIGINT;
# 设置默认值
SET finalVar='$';
# 转换入参类型
SET tempVar =son_id;
# 循环体，如果当前的父节点为0，那么说明已经查询完毕了
WHILE tempVar <> 0 DO
# 将得到的子节点保存到变量中
SET finalVar= CONCAT(finalVar,',',tempVar);
# 根据子Id查询父Id
SELECT t.parent_id INTO tempVar FROM address t WHERE t.id=tempVar;
# 结束循环
END WHILE;
# 返回结果,得到的是包含入参以及下面的所有子节点
RETURN finalVar;
END
- 查询郑州市的所有父节点
SELECT * FROM address WHERE FIND_IN_SET(id,selectParentByChildId(5));
结果如下：
image.png
本文中出现的find_in_set()函数可以参考：
mysql中find_in_set()函数的使用
