mysql递归查询(分页版)代码

call getPictureList('402880e63789b63a013789b646dc0000',1,5); 
set max_sp_recursion_depth=12; 

CREATE PROCEDURE getPictureList (IN rootCategoryId varchar(32),IN m INT,IN n INT) 
BEGIN 
   CREATE TEMPORARY TABLE IF NOT EXISTS tempCategoryList 
   (sno int primary key auto_increment, 
    category_id varchar(32), 
    depth int 
   ); 
   DELETE FROM tempCategoryList; 
 
   CALL getCategoryList(rootCategoryId,0); 
    
   set  @query=concat( 
       ' select p.*', 
       ' from tempCategoryList t,picture p,picture_category pc ', 
       ' where t.category_id = pc.category_id and pc.pic_id = p.pid ',  
       ' order by p.hot_value desc  limit ',m,',',n); 
    
   select @query; 
    
   prepare stmt1 from @query; 
   execute stmt1; 
   deallocate prepare stmt1;   
 
END; 
 
CREATE PROCEDURE getCategoryList (IN rootCategoryId varchar(32),IN nDepth INT) 
BEGIN 
      DECLARE done varchar(32) DEFAULT ""; 
      DECLARE b varchar(32); 
      DECLARE cur1 CURSOR FOR SELECT category_id FROM category where parent=rootCategoryId; 
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 
     
      insert into tempCategoryList values (null,rootCategoryId,nDepth); 
     
      OPEN cur1; 
     
      FETCH cur1 INTO b; 
      WHILE done="" DO 
              CALL getCategoryList(b,nDepth+1); 
              FETCH cur1 INTO b; 
      END WHILE; 
     
      CLOSE cur1; 
END;