valuelist mysql分页_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;