mysql 简单递归_mysql处理递归的一个例子

只是一个例子，简单记录一下！

delimiter //

drop procedure if exists getChildModel //

CREATE PROCEDURE getChildModel(IN startId INT,in inAdmin int)

BEGIN

DECLARE _id varchar(4) DEFAULT '0';

DECLARE _path VARCHAR(2000);

DECLARE _Last bigint DEFAULT 0;

CREATE TEMPORARY TABLE IF NOT EXISTS temp_table (

id varchar(4) NOT NULL ,

path varchar(2000) default NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB TYPE = HEAP;

CREATE TABLE IF NOT EXISTS pp_table (

topid int not null,

id varchar(4) NOT NULL ,

path varchar(2000) default NULL,

PRIMARY KEY (topid,id)

) ENGINE=InnoDB;

delete from temp_table;

insert into temp_table(id, path)

select startId, model_parent_Id from gweb_model src where model_aiid = startId;

set _id = startId;

set _path = startId;

WHILE ( _id <> '0') DO

insert into temp_table(id, path)

select model_aiid, model_parent_Id

from gweb_model src where src.model_parent_Id=_id ;

set _last = _id;

set _id = 0;

select id, path into _id, _path from temp_table where id>_last limit 1;

END WHILE;

insert into pp_table select startId,id,path from temp_table;

insert into dzy_permission_all select inAdmin,id from temp_table;

truncate table temp_table;

END

//[@more@]