MySQL函数-递归函数

find_in_set函数使用

FIND_IN_SET(str,strlist)

str 要查询的字符串
strlist 字段名 参数以”,”分隔 如 (1,2,6,8)

精确匹配 ,分隔的数据

mysql中find_in_set()函数的使用 - 平凡希 - 博客园

MySQL function函数 - 递归函数使用

MySql中使用函数实现递归查询 - 简书

Mysql 根据id查所有父级或子级

Mysql 根据id查所有父级或子级 - 锐洋智能 - 博客园

mysql递归查询，mysql中从子类ID查询所有父类（做无限分类经常用到）
 
 
由于mysql 不支持类似 oracle with ...connect的 递归查询语法
之前一直以为类似的查询要么用存储过程要么只能用程序写递归查询.
 
 
现在发现原来一条sql语句也是可以搞定的
 
 
先来看数据表的结构如下：
 
 
id  name    parent_id
---------------------------
1   Home        0
2   About       1
3   Contact     1
4   Legal       2
5   Privacy     4
6   Products    1
7   Support     1
我要的要求是根据一个分类ID（这个分类ID可能是一个子分类），得到所有的父分类，下面是相应的SQL：
 
 
SELECT T2.id, T2.name
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 5, @l := 0) vars,
        table1 h
    WHERE @r <> 0) T1
JOIN table1 T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC
 
 
 
 
代码@r := 5标示查询id为5的所有父类。结果如下
1, ‘Home’
2, ‘About’
4, ‘Legal’
5, ‘Privacy’

### 声明头文件  入参是  123,12343,145435 的形式，递归找出所有路径上的父节点  如果是单个的可以去掉while循环，只留下select部分
CREATE DEFINER=`user_name` FUNCTION `getFromTree`(
	selectIds VARCHAR (1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,      ### 入参是  123,12343,145435 的形式，递归找出所有路径上的父节点
	workId VARCHAR (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci       ### 单个其他条件
) RETURNS varchar(1000) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci


BEGIN
  DECLARE selectId VARCHAR (50);  ### 单个selectId
  DECLARE result VARCHAR (1000);     ### 单个selectId找到的父节点
  DECLARE resultListList VARCHAR (1000);   ### 所有父节点
  DECLARE count INT DEFAULT 0;  ### 计算结束
set result = '';
set resultListList = '';
WHILE count = 0 DO                     ### for循环每次处理一个 ID去找节点
SET selectId = SUBSTRING_INDEX(selectIds, ',', 1);
IF LENGTH(selectId) = LENGTH(selectIds) THEN
  SET count = 1;                    ### 停止条件，截取到长度和单个一样
ELSE
  SET selectIds = SUBSTRING(......);        ### 每次截掉完成前面的一部分
END IF;


SELECT
	GROUP_CONCAT(T2.result_id) into result  ### 将查询的结果放入临时变量,GROUP_CONCAT是 追加,
FROM
	(
		SELECT
			@r AS _id,
			(
				SELECT
					@r := parent_id
				FROM
					work_tree
				WHERE
					id = _id
			) AS parent_id
		FROM
			(SELECT @r := selectId) vars,    ### 定义变量@r
			work_tree
		WHERE
			@r <> 0       ###  父节点不为0
	) T1
JOIN work_tree T2 ON T1._id = T2.id
WHERE
	T2.workId = workId and (T2.type !=2 or T2.parent_id = 0);
	IF result is not null THEN
		SET resultListList = concat(resultListList,',',result);  ###  每次循环添加一次
	END IF;
END
WHILE;

RETURN resultListList;
END

单个递归

### 声明头文件  单个
CREATE DEFINER=`user_name` FUNCTION `getFromTree`(
	selectIds VARCHAR (1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,      ### 入参是  123 的形式，递归找出所有路径上的父节点
	workId VARCHAR (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci       ### 单个其他条件
) RETURNS varchar(1000) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci


BEGIN
  DECLARE selectId VARCHAR (50);  ### 单个selectId
  DECLARE result VARCHAR (1000);     ### 单个selectId找到的父节点

SELECT
	GROUP_CONCAT(T2.result_id) into result  ### 将查询的结果放入临时变量,GROUP_CONCAT是 追加,
FROM
	(
		SELECT
			@r AS _id,
			(
				SELECT
					@r := parent_id
				FROM
					work_tree
				WHERE
					id = _id
			) AS parent_id
		FROM
			(SELECT @r := selectId) vars,    ### 定义变量@r
			work_tree
		WHERE
			@r <> 0       ###  父节点不为0
	) T1
JOIN work_tree T2 ON T1._id = T2.id
WHERE
	T2.workId = workId and (T2.type !=2 or T2.parent_id = 0);

RETURN result;
END