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

问题描述

最近开发过程中遇到一个问题,每次添加代理关系都要去递归查询一下它在不在这个代理关系树上.很麻烦也很浪费资源.想着把代理关系的父类全部存起来

先看结果

表结构展示

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

实现递归查询集合

因为没有对应函数,需要自行编写递归查询函数

createdefiner = root@`%` function getParentIds(child_id int) returns varchar(255)
BEGINDECLARE parent_id varchar(255) default '';DECLARE parent_ids VARCHAR(1500) default '';DECLARE count int;DECLARE sum int default 0;-- 获取当前子类的父类IDSELECT superior_user_id INTO parent_id FROM t_agent_user_relation WHERE current_user_id = child_id;-- 递归查询所有父类IDWHILE parent_id is not null && sum < 3DOSET 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 thenset parent_id = null;elseSELECT 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) > 0thenset sum = sum + 1;elseset 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; 错误原因我就不在这里分析了,有兴趣的自己查询一下

查询结果

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