mysql有递归函数吗

mysql是可以使用递归函数的,如下例子:

mysql树查询，sql递归函数

select cateID from goodscategory where FIND_IN_SET(cateID ,getGoodsChildLst('43242'))

CREATE FUNCTION `getGoodsChildLst`(rootId INT)

RETURNS varchar(1000)

BEGIN

DECLARE sTemp VARCHAR(1000)

DECLARE sTempChd VARCHAR(1000)

SET sTemp = '$'

SET sTempChd =cast(rootId as CHAR)

WHILE sTempChd is not null DO

SET sTemp = concat(sTemp,',',sTempChd)

SELECT group_concat(cateID) INTO sTempChd FROM goodscategory where FIND_IN_SET(parentCateID,sTempChd)>0

END WHILE

RETURN sTemp

END

给你个网上写的比较好的例子：

方法一：利用函数来得到所有子节点号。

创建一个function

getChildLst,

得到一个由所有子节点号组成的字符串.

mysql>

delimiter

//

mysql>

mysql>

CREATE

FUNCTION

`getChildLst`(rootId

INT)

->

RETURNS

varchar(1000)

->

BEGIN

->

DECLARE

sTemp

VARCHAR(1000)

->

DECLARE

sTempChd

VARCHAR(1000)

->

->

SET

sTemp

=

'$'

->

SET

sTempChd

=cast(rootId

as

CHAR)

->

->

WHILE

sTempChd

is

not

null

DO

->

SET

sTemp

=

concat(sTemp,',',sTempChd)

->

SELECT

group_concat(id)

INTO

sTempChd

FROM

treeNodes

where

FIND_IN_SET(pid,sTempChd)>0

->

END

WHILE

->

RETURN

sTemp

->

END

->

//

Query

OK,

0

rows

affected

(0.00

sec)

mysql>

mysql>

delimiter

使用我们直接利用find_in_set函数配合这个getChildlst来查找

mysql>

select

getChildLst(1)

+-----------------+

|

getChildLst(1)

|

+-----------------+

|

$,1,2,3,4,5,6,7

|

+-----------------+

1

row

in

set

(0.00

sec)

mysql>

select

*

from

treeNodes

->

where

FIND_IN_SET(id,

getChildLst(1))

+----+----------+------+

|

id

|

nodename

|

pid

|

+----+----------+------+

|

1

|

A

|

0

|

|

2

|

B

|

1

|

|

3

|

C

|

1

|

|

4

|

D

|

2

|

|

5

|

E

|

2

|

|

6

|

F

|

3

|

|

7

|

G

|

6

|

+----+----------+------+

7

rows

in

set

(0.01

sec)

mysql>

select

*

from

treeNodes

->

where

FIND_IN_SET(id,

getChildLst(3))

+----+----------+------+

|

id

|

nodename

|

pid

|

+----+----------+------+

|

3

|

C

|

1

|

|

6

|

F

|

3

|

|

7

|

G

|

6

|

+----+----------+------+

3

rows

in

set

(0.01

sec)

---