Hierarchical queries in Oracle are replaced with MySQL

Oracle's start with ... Connect by implements a recursive query for the tree, but now requires MySQL to implement the same recursive query tree function. This function is I have never used before, so I surf the internet and find some information began to do up.

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 does not have a corresponding method to implement the function of recursive query tree, so we have to write a function according to the internet to achieve:

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 are NOT NULL does

Set Ptemp=concat (ptemp, ', ', ctemp);

SELECT Group_concat (seq_cate) to ctemp from Osr_category

WHERE Find_in_set (parent_id,ctemp) >0;

END while;

RETURN ptemp;

END

then its SQL language The sentence should read:

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

from Osr_category C

where Find_in_set (C.seq_cate, Getchildlist (#serviceCategory #))

Hierarchical queries in Oracle are replaced with MySQL