mysql递归查询，mysql创建函数

1、需求背景：

如有以下数据，需要根据COMID获取对应COMLEVEL的父级PARENTID，

如传入COMID‘21010101’ 获取对应COMLEVEL为‘3’的父级PARENTID，则返回2101

如传入COMID‘21010101’ 获取对应COMLEVEL为‘2’的父级PARENTID，则返回21

2、编写递归查询sql

SELECT  T2.PARENTID   
FROM ( 
    SELECT 
        @r AS _comcode, 
        (SELECT @r := PARENTID FROM test WHERE COMID = _comcode) AS PARENTID, 
        @l := @l + 1 AS lvl 
    FROM 
        (SELECT @r := '21010101', @l := 0) vars, 
        test h 
    WHERE @r <> 0) T1 
JOIN test T2 
ON T1._comcode = T2.COMID where T2.COMLEVEL='3'  ;

查询结果如下：

 3、编写函数

由于使用改递归sql的地方比较多，所以打算写个函数来实现

CREATE DEFINER=`test`@`localhost` FUNCTION `getComcodeByLevel`(in_comcode varchar(50),comlevel varchar(50)) RETURNS varchar(100) CHARSET utf8mb3
BEGIN
 
  declare v_comcode varchar(100);
 
SELECT  T2.PARENTID   into  v_comcode
FROM ( 
    SELECT 
        @r AS _comcode, 
        (SELECT @r := PARENTID FROM test WHERE COMID = _comcode) AS PARENTID, 
        @l := @l + 1 AS lvl 
    FROM 
        (SELECT @r := in_comcode, @l := 0) vars, 
        test h 
    WHERE @r <> 0) T1 
JOIN test T2 
ON T1._comcode = T2.COMID where T2.COMLEVEL=comlevel ;
 
   return v_comcode; 
END

写好之后保存的时候遇到以下错误

1418- This function has none of DETERMINISTIC, NO SQL or READS SQL DATA in its declaration and binary logging is enabled (you*might* want to use the less safe log_bin_trust_function_creators variable)

        log_bin_trust_function_creators 这个是MySQL的一个内置变量，系统需要信任生成函数才能创建，系统默认在binlog开启的时候是关闭生成函数的使用的。可以使用以下语句开启

set global log_bin_trust_function_creators=1;

可以使用以下命令查看开关状态，由于本地已经开启，所以是ON状态

show variables like 'log_bin_trust_function_creators';

4、测试

输入参数如下：

 函数返回

再次输入参数如下：

函数返回：

 基本可以实现需求

当然也可以使用 以下方式实现

with recursive temp as (
select * from sys_menu p  
union all 
 select t.* from sys_menu t inner join temp t2 on t2.id = t.parent_id 
)
select *  from temp