MySQL function - recursive function

find_in_set Function USES

FIND_IN_SET(str,strlist)

str String to query
strlist Field name Parameter with ”,” Separate Such as (1,2,6,8)

Exactly match , Separated data

mysql in find_in_set() Use of functions - Ordinary hope - Blog Garden

MySQL function function - Recursive function use

MySql Using functions to realize recursive query - Simple books

Mysql according to id Check all parents or children

Mysql according to id Check all parents or children - Ruiyang intelligence - Blog Garden

mysql recursive query ,mysql From subclass ID Query all parent classes （ It's often used in infinite classification ）
 
 
 because mysql  Does not support similar  oracle with ...connect Of   Recursive query syntax 
 It has always been thought that similar queries can either be stored procedures or recursive queries can only be written by programs .
 
 
 Now I found the original one sql Statements can also be done 
 
 
 Let's first look at the structure of the data table as follows ：
 
 
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
 My request is based on a classification ID（ This category ID It may be a subcategory ）, Get all the parent categories , Here is the corresponding 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
 
 
 
 
 Code @r := 5 Label query id by 5 All the superclasses of . give the result as follows 
1, ‘Home’
2, ‘About’
4, ‘Legal’
5, ‘Privacy’

###  Declaration header file    Participation is   123,12343,145435  In the form of , Recursively find the parent node on all paths    If it is single, you can remove while loop , Leave only select part 
CREATE DEFINER=`user_name` FUNCTION `getFromTree`(
	selectIds VARCHAR (1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,      ###  Participation is   123,12343,145435  In the form of , Recursively find the parent node on all paths 
	workId VARCHAR (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci       ###  Individual other conditions 
) RETURNS varchar(1000) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci


BEGIN
  DECLARE selectId VARCHAR (50);  ###  Single selectId
  DECLARE result VARCHAR (1000);     ###  Single selectId Parent node found 
  DECLARE resultListList VARCHAR (1000);   ###  All parent nodes 
  DECLARE count INT DEFAULT 0;  ###  End of calculation 
set result = '';
set resultListList = '';
WHILE count = 0 DO                     ### for The loop processes one at a time  ID Find the node 
SET selectId = SUBSTRING_INDEX(selectIds, ',', 1);
IF LENGTH(selectId) = LENGTH(selectIds) THEN
  SET count = 1;                    ###  Stop conditions , The intercepted length is the same as that of a single 
ELSE
  SET selectIds = SUBSTRING(......);        ###  Cut off the front part each time 
END IF;


SELECT
	GROUP_CONCAT(T2.result_id) into result  ###  Put the result of the query into a temporary variable ,GROUP_CONCAT yes   Additional ,
FROM
	(
		SELECT
			@r AS _id,
			(
				SELECT
					@r := parent_id
				FROM
					work_tree
				WHERE
					id = _id
			) AS parent_id
		FROM
			(SELECT @r := selectId) vars,    ###  Defining variables @r
			work_tree
		WHERE
			@r <> 0       ###   The parent node is not 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);  ###   Add once per cycle 
	END IF;
END
WHILE;

RETURN resultListList;
END

Single recursion

###  Declaration header file    Single 
CREATE DEFINER=`user_name` FUNCTION `getFromTree`(
	selectIds VARCHAR (1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,      ###  Participation is   123  In the form of , Recursively find the parent node on all paths 
	workId VARCHAR (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci       ###  Individual other conditions 
) RETURNS varchar(1000) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci


BEGIN
  DECLARE selectId VARCHAR (50);  ###  Single selectId
  DECLARE result VARCHAR (1000);     ###  Single selectId Parent node found 

SELECT
	GROUP_CONCAT(T2.result_id) into result  ###  Put the result of the query into a temporary variable ,GROUP_CONCAT yes   Additional ,
FROM
	(
		SELECT
			@r AS _id,
			(
				SELECT
					@r := parent_id
				FROM
					work_tree
				WHERE
					id = _id
			) AS parent_id
		FROM
			(SELECT @r := selectId) vars,    ###  Defining variables @r
			work_tree
		WHERE
			@r <> 0       ###   The parent node is not 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