我记得我在MySQL性能博客上读到一篇关于
pagination
把这个例子搞得一团糟,可能会有帮助(@row_id是多余的)
drop table if exists gallery;
create table gallery
(
id int unsigned not null,
image varchar(255) not null,
rating tinyint unsigned default 0
)
engine=innodb;
insert into gallery values
(166,'6.png',9),(165,'8.png',9),(189,'1.png',8),
(171,'99.png',8),(169,'56.png',8),(155,'34.png',8),
(265,'7.png',7),(754,'86.png',6),(37,'37.png',4),
(342,'95.png',2),(99 ,'35.png',0),(76 ,'34.png',0),
(44 ,'3.png',0),(8 ,'22.png',0), (1001 ,'1001.png',0);
drop procedure if exists list_gallery_paged;
delimiter #
create procedure list_gallery_paged
(
in p_last_id int unsigned,
in p_last_rating tinyint unsigned
)
proc_main:begin
set @row_id = 0;
if p_last_id <= 0 then
select @row_id:=@row_id+1 as row_id, g.*
from gallery g order by rating desc, id desc limit 4;
else
select @row_id:=@row_id+1 as row_id, g2.*
from gallery g inner join gallery g2 on g.id = g2.id
where
g.rating <= p_last_rating and (g.id < p_last_id or g.rating < p_last_rating)
order by
g.rating desc, g.id desc limit 4;
end if;
end proc_main #
delimiter ;
-- in pages of 4 (use all rows)
select g.* from gallery g order by rating desc, id desc;
call list_gallery_paged(0,0);
call list_gallery_paged(171,8);
call list_gallery_paged(754,6);
call list_gallery_paged(99,0);
call list_gallery_paged(8,0);
-- one at a time (use top row only)
select g.* from gallery g order by rating desc, id desc;
call list_gallery_paged(265,7);
call list_gallery_paged(754,6);
call list_gallery_paged(37,4);
call list_gallery_paged(342,2);
call list_gallery_paged(1001,0);
call list_gallery_paged(99,0);
call list_gallery_paged(76,0);
call list_gallery_paged(44,0);
call list_gallery_paged(8,0);