mysql递归查询

根据子ID查询所有父级

创建表结构：

CREATE TABLE `authority_resource` (
  `id` int(11) NOT NULL COMMENT '权限id',
  `name` varchar(50) NOT NULL COMMENT '权限名称',
  `p_id` int(11) DEFAULT NULL COMMENT '父级权限id',
  `type` tinyint(4) NOT NULL COMMENT '权限类型 1 菜单权限 2 操作权限',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='权限资源表';

添加数据：

INSERT INTO `authority_resource`(`id`, `name`, `p_id`, `type`, `create_time`) VALUES (17, 'APP', NULL, 1, '2020-09-08 17:56:22');
INSERT INTO `authority_resource`(`id`, `name`, `p_id`, `type`, `create_time`) VALUES (72, '乡村振兴配置', 17, 1, '2021-09-08 16:11:37');
INSERT INTO `authority_resource`(`id`, `name`, `p_id`, `type`, `create_time`) VALUES (77, '频道搜索推荐', 72, 1, '2021-09-08 16:11:37');

INSERT INTO `authority_resource`(`id`, `name`, `p_id`, `type`, `create_time`) VALUES (18, '系统管理', NULL, 1, '2020-09-08 17:56:22');
INSERT INTO `authority_resource`(`id`, `name`, `p_id`, `type`, `create_time`) VALUES (19, '角色管理', 18, 1, '2021-09-08 16:11:37');
INSERT INTO `authority_resource`(`id`, `name`, `p_id`, `type`, `create_time`) VALUES (20, '权限管理', 18, 1, '2021-09-08 16:11:37');

添加递归查询父级函数：

CREATE DEFINER=`root`@`%` FUNCTION `getParentList`(rootId INT) RETURNS varchar(1000) CHARSET latin1
    DETERMINISTIC
BEGIN
      DECLARE sParentList varchar(1000);
      DECLARE sParentTemp varchar(1000);
      SET sParentTemp =cast(rootId as CHAR);
      WHILE sParentTemp!=0 DO
    IF (sParentList!=0) THEN
         SET sParentList = concat(sParentTemp,',',sParentList);
    ELSE
     SET sParentList = concat(sParentTemp);
    END IF;
        SELECT group_concat(p_id) INTO sParentTemp FROM authority_resource where FIND_IN_SET(id,sParentTemp)>0;
      END WHILE;
      RETURN sParentList;
    END

执行查询语句：

SELECT
	* 
FROM
	authority_resource 
WHERE
	FIND_IN_SET(
	id,
	getParentList ( 77 ))

查询结果：

根据父ID递归查询所有子类函数：

CREATE DEFINER=`sxyall`@`%` FUNCTION `queryChildrenCate`(p_id INT) RETURNS varchar(4000) CHARSET latin1
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = CAST(p_id AS CHAR);

WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(id) INTO sTempChd FROM authority_resource WHERE FIND_IN_SET(p_id,sTempChd)>0;
END WHILE;
RETURN sTemp;
END