MySQL通过自定义函数以及存储过程实现递归查询父级ID
1.存储过程定义
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译
2.为什么用存储过程实现树结构数据查询父级节点
1、能完成较复杂的运算与判断
2、可编程行强，灵活
3、SQL编程的代码可重复使用
4、预编译机制
5、减少网络之间的数据传输，节省开销
3.使用存储过程和临时表完成业务(当参数为多个子节点id，都要去查找父级节点，并返回一棵完整的树结构给前端，需要做复杂的递归与数据拼接)
3.1需要用到的函数
# 计算传入字符串的总length
DELIMITER $$
DROP function IF EXISTS `func_split_TotalLength` $$
CREATE FUNCTION `func_split_TotalLength`
(f_string varchar(1000),f_delimiter varchar(5)) RETURNS int(11)
BEGIN
# 计算传入字符串的总length
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END$$
DELIMITER ;
3.2 拆分字符串成数组
# 拆分字符串成数组
DELIMITER $$
DROP function IF EXISTS `func_split` $$
CREATE FUNCTION `func_split`
(f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8
BEGIN
# 拆分传入的字符串，返回拆分后的新字符串
declare result varchar(255) default '';
set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
return result;
END$$
DELIMITER ;
3.3使用临时表存储父子节点信息(通过动态改变角色菜单权限，完成菜单列表的动态渲染)
# 传入参数roleId,字符串数组的子节点id
create
definer = dev@`%` procedure updateRoleMenu(IN roleId int,IN id varchar(100))
begin
-- 定义局部变量
declare oTempChild VARCHAR(4000);
# 拆分结果
declare cnt int default 0;
declare i int default 0;
declare oTemp varchar(100);
declare ids varchar(100);
set cnt = func_split_TotalLength(id,',');
-- cast 类型转换
set oTemp = '';
-- 每次执行存储过程时，检查是否存在 tmp_print临时表，存在删除
drop table if exists tmp_split;
-- 创建临时表
create temporary table tmp_split (status int not null);
# set oTempChild = cast(id AS char);
while i < cnt
do
set i = i + 1;
set oTempChild= func_split(id,',',i);
while oTempChild is not null
do
-- 循环获取子类的 id
-- 判断 oTemp 是否为空
if oTemp != '' then
insert into tmp_split(`status`) values (oTemp);
else
insert into tmp_split(`status`) values (oTempChild);
end if;
-- 搜索父类 id
select GROUP_CONCAT(menu_pid) INTO oTempChild
from mps_menu
-- find_in_set 是全表扫描的,find_in_set 是精确匹配，字段值以英文”,”分隔
-- 搜索 com_id
where FIND_IN_SET(menu_id, oTempChild) > 0;
end while;
end while;
# 将得到的父级结合和子集节点合并去重,拼接成字符串，并更新角色菜单权限
update mps_role a ,(select GROUP_CONCAT(DISTINCT(status)) as list from tmp_split) c set a.role_render=c.list where role_id = roleId;
end;
4.在dao层需要编写的接口
/**
* 动态查找当前节点列表的所有父节点
* @param roleId 角色编号
* @param ids 节点字符串数组
*/
void updateRoleMenu(@Param("roleId")Integer roleId,@Param("ids")String ids);
<select id="updateRoleMenu" parameterType="java.lang.Object" statementType="CALLABLE">
{call updateRoleMenu(#{roleId,jdbcType=INTEGER, mode=IN}, #{ids,jdbcType=VARCHAR, mode=IN})}
</select>
5.通过节点拼接所需要的数据结构(工具)
/**
* 通过数据列表，拼装成树结构数据
* @param nodes 数据结合列表
* @param <T>
* @return
*/
public static <T> List<TreeItem<T>> buildMenu(List<TreeItem<T>> nodes) {
if (nodes == null) {
return null;
}
List<TreeItem<T>> tree = new ArrayList<>();
nodes.forEach(children -> {
Integer pid = children.getParentId();
if (pid==1) {
//是父节点
tree.add(children);
return;
}
for (TreeItem<T> parent : nodes) {
Integer id = parent.getId();
if (id != null && id.equals(pid)) {
//说明是该节点是children的父节点
children.setHasParent(true);
parent.setHasChildren(true);
parent.getChildren().add(children);
return;
}
}
});
return tree;
}
public class TreeItem<T> implements Serializable {
/**
* 节点ID
*/
private Integer id;
/**
* 父节点ID
*/
private Integer parentId;
/**
* 是否有子节点
*/
private Boolean hasChildren;
/**
* 是否有父节点
*/
private Boolean hasParent;
/**
* 路径
*/
private String path;
/**
* 跳转路径
*/
private String redirect;
/**
* 组件
*/
private String component;
/**
* 名称
*/
private String name;
private Meta meta;
/**
* 子节点信息
*/
private List<TreeItem<T>> children = new ArrayList<>();
}
6.返回的数据结构
7：此外mysql存储过程如何遍历查询结构集(使用游标的方式)
-- 创建存储过程之前需判断该存储过程是否已存在，若存在则删除
DROP PROCEDURE IF EXISTS init_report;
-- 创建存储过程
CREATE PROCEDURE init_report(IN roleId int)
BEGIN
-- 定义变量
DECLARE s int DEFAULT 0;
DECLARE id varchar(256);
DECLARE str varchar(256);
# set str = '';
-- 定义游标，并将sql结果集赋值到游标中
DECLARE report CURSOR FOR select distinct status from tmp_split;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
-- 打开游标
open report;
-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
fetch report into id;
-- 当s不等于1，也就是未遍历完时，会一直循环
while s<>1 do
-- 执行业务逻辑
-- 当s等于1时表明遍历以完成，退出循环
end while;
update mps_role set role_render = str where role_id = roleId;
-- 关闭游标
close report;
END;
