Summary of MySQL Recursive query

Description:

The parent_id value is associated with the value of the id column of the table itself. If the value is-1, it means that there is no parent record for the record. Otherwise, it means that the record has a parent record (assuming that the parent_ id value is 5, then the parent record id is 5). For the time being, the record itself is called a child record, the parent and parent records are called ancestral records, and the child and child records are called descendant records.

Query demand

1) query all ancestral records associated with the specified record according to the id of the specified record, and return the ancestral record name by level

2) query all the associated descendant records id according to the specified parent_id

Query implementation

Realized by function call

1) query all ancestral records associated with the specified record according to the id of the specified record, and return the ancestral record name by level

# Recursive downward

DROP FUNCTION IF EXISTS queryChildrenSuiteIds

DELIMITER

CREATE FUNCTION queryChildrenSuiteIds (suiteId INT)

RETURNS VARCHAR (4000)

BEGIN

DECLARE childSuiteIds VARCHAR (4000)

DECLARE parentSuiteIds VARCHAR (4000)

SET childSuiteIds=''

SET parentSuiteIds=CAST (suiteId AS CHAR)

WHILE parentSuiteIds IS NOT NULL DO

SET childSuiteIds=CONCAT (parentSuiteIds,',',childSuiteIds)

SELECT GROUP_CONCAT (id) INTO parentSuiteIds FROM tb_testcase_suite WHERE FIND_IN_SET (parent_id,parentSuiteIds) > 0

END WHILE

RETURN childSuiteIds

END

DELIMITER

# call

SELECT queryChildrenSuiteIds (5)