how to use mysql_How to use recursive in mysql.

I have a trouble about recursive in mysql. the key is I use mysql not long time. I don't know much about MySql.So it is a big trouble of me.

Ok, go to the business now. If you want to use recursive in mysql,you need to do the three step first:

1. Open your mysql config file from the mysql installation location.

2. Set the recursive depth to the max value:

max_sp_recursion_depth=255

3. Set thread stack size:        thread_stack=64M    I  don't know how much size of the thread_stack is logical,but the value of the thread_stack is worked.

there is my script:

CREATE DEFINER = 'root'@'localhost' PROCEDURE `proc_GetCategoryRecursive`(

cid int

)

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT ''

begin

declare curid int;

insert into tblCategory (categoryid)

select id from productcategory where  parentid=cid and isdetail=true;

insert into tblUndetail (categoryid)

select id from productcategory where  parentid=cid and isdetail=false;

WHILE EXISTS(select * from tblundetail ) do

select categoryid into curid from tblundetail limit 1;

delete from tblundetail where categoryid=curid;/*Don't forget delete the current record before invoke recursive*/

call proc_GetCategoryRecursive(curid);

end while;

end;