Mysql 递归查询子类Id的所有父类Id

🐔问题描述

---

最近开发过程中遇到一个问题,每次添加代理关系都要去递归查询一下它在不在这个代理关系树上.
很麻烦性能也比较差.想着把代理关系的父类全部存起来,
但是有涉及修复历史数据,不想写java代码去实现,干脆写函数解决.
所以函数中部分代码也是根据业务情况所写,可行判断增减

🐧先看结果

---

🐧表结构展示

---

表名(t_agent_user_relation)
字段名	字段类型	描述
current_user_id	int	当前用户id
superior_user_id	int	代理用户id
parent_ids	varchar	父类Id集合以逗号隔开
parent_id	int	父类ID
id	int	主键

🐧实现递归查询集合

---

🐙函数实现过程分析

---

函数实现过程:

• 根据子类Id查询父类Id
• 根据父类Id 查询 父类的父类ID 以此循环,终止条件如下
  • 查询到的父类为空
  • 出现三次重复父类Id 避免 A=>B=>C=>A 等情况出现死循环

🐙函数具体代码

---

create
    definer = root@`%` function getParentIds(child_id int) returns varchar(255)
BEGIN
    DECLARE parent_id varchar(255) default '';
    DECLARE parent_ids VARCHAR(1500) default '';
    DECLARE count int;
    DECLARE sum int default 0;

    -- 获取当前子类的父类ID
    SELECT superior_user_id INTO parent_id FROM t_agent_user_relation WHERE current_user_id = child_id;


    -- 递归查询所有父类ID
    WHILE parent_id is not null && sum < 3
        DO
            SET parent_ids = CONCAT_WS(',', parent_ids, parent_id);
            SELECT count(1) into count FROM t_agent_user_relation WHERE current_user_id = parent_id;
            if count = 0 then
                set parent_id = null;
            else
                SELECT superior_user_id INTO parent_id FROM t_agent_user_relation WHERE current_user_id = parent_id;
            end if;
            -- 防止出现死循环
            if parent_id is not null && find_in_set(parent_id, parent_ids) > 0
            then
                set sum = sum + 1;
            else
                set sum = sum;
            end if;
        END WHILE;
    RETURN parent_ids;
END;

如果出现以下错误
[HY000][1418] This function has none of DETERMINISTIC, NO SQL, or READS SQL

• 执行set global log_bin_trust_function_creators = 1;
• 简单介绍一下，当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。 设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。 如果变量设置为1，MySQL不会对创建存储函数实施这些限制。 此变量也适用于触发器的创建。

🐧查询结果

---

select  current_user_id,superior_user_id,getParentIds(current_user_id) from t_agent_user_relation

🐧修复数据

---

update t_agent_user_relation set    parent_ids=  getParentIds(current_user_id)  where parent_ids is null