Hierarchical queries in oracle are replaced with mysql

Oracle's Start with...Connect By implements the recursive query of the tree, but now it is required to use MYSQL to implement the same recursive query tree. This function is something I have never used before, so I searched the Internet and found some information and began to do it.

The original oracle statement is

Select'|'| | c.seq_cate | |'|'

From osr_category c

Start with c.seq_cate = # serviceCategory#

Connect by prior c.seq_cate = c.parent_id)

Mysql has no corresponding method to implement the function of recursive query tree, so we have to write a function to implement it according to what is said on the Internet:

CREATE FUNCTION getChildList (rootId VARCHAR (1000))

RETURNS VARCHAR (1000)

BEGIN

DECLARE pTemp VARCHAR (1000)

DECLARE cTemp VARCHAR (1000)

SET pTemp='$'

SET cTemp=rootId

WHILE cTemp is not null DO

Set pTemp=CONCAT (pTemp,',',cTemp)

SELECT GROUP_CONCAT (SEQ_CATE) INTO cTemp from osr_category

WHERE FIND_IN_SET (PARENT_ID,cTemp) > 0

END WHILE

RETURN pTemp

END

Then its sql statement should be changed to:

Select'|'| | c.seq_cate | |'|'

From osr_category c

Where FIND_IN_SET (c.seq_cate, getChildList (# serviceCategory#))