最近一直在想MYSQL做分页的想法,可是思路一直很乱,想不清楚,所以请各位高手指点一下。我是用delphi+mysql实现的,其他方法不讨论。
先说说我自己现在的思路: pageRow,pageSize : integer;
pageSize:=5;
pageRow:=0;
procedure TForm1.NextBtClick(Sender: TObject);
var
sqlNext : string;begin
sqlNext:='select * from Table limit '+inttostr(pageRow)+','+inttostr(pageSize)+'';
self.ADOQuery1.Close;
self.ADOQuery1.SQL.Clear;
self.ADOQuery1.SQL.Add(sqlNext);
self.ADOQuery1.Open;
pageRow:=pageRow+pageSize;
end;我是用这种方法实现的分页,但是这种方法非常的浪费内存,所以请教有没有什么更好的方法,比如说可以先用mysql语句查询预处理好,把所有的页数分好。然后点下button之后他会自己去调出那一页,而不用再去做一次全表查询。这样内存中就只占那一页的内存!或者有更好的方法,请教了!别拿其他开发工具的语言的方法给我说,看的就烦!
先说说我自己现在的思路: pageRow,pageSize : integer;
pageSize:=5;
pageRow:=0;
procedure TForm1.NextBtClick(Sender: TObject);
var
sqlNext : string;begin
sqlNext:='select * from Table limit '+inttostr(pageRow)+','+inttostr(pageSize)+'';
self.ADOQuery1.Close;
self.ADOQuery1.SQL.Clear;
self.ADOQuery1.SQL.Add(sqlNext);
self.ADOQuery1.Open;
pageRow:=pageRow+pageSize;
end;我是用这种方法实现的分页,但是这种方法非常的浪费内存,所以请教有没有什么更好的方法,比如说可以先用mysql语句查询预处理好,把所有的页数分好。然后点下button之后他会自己去调出那一页,而不用再去做一次全表查询。这样内存中就只占那一页的内存!或者有更好的方法,请教了!别拿其他开发工具的语言的方法给我说,看的就烦!
create procedure getpage(in curpage int,in perpage int)
begin
begin
declare total int default 0;
declare totalpage int default 0;
declare offset int default 0;
if perpage <= 0 then
set perpage = 20;
end if;
if curpage < 1 then
set curpage = 1;
end if;
select count(*) into total from tablename;
set totalpage = ceil(total/perpage);
set offset = (curpage-1) * perpage;
if offset >= total then
set offset = (totalpage-1)*perpage ;
end if;
set @str = concat('select * from tablename order by orderfield desc limit ',offset,',',perpage);
prepare stmt1 from @str;
execute stmt1;
deallocate prepare stmt1;
end
//
delimiter ;
create procedure getpage(in curpage int,in perpage int)
begin
begin
declare total int default 0;
declare totalpage int default 0;
declare offset int default 0;
if perpage <= 0 then
set perpage = 20;
end if;
if curpage < 1 then
set curpage = 1;
end if;
select count(*) into total from tablename;
set totalpage = ceil(total/perpage);
set offset = (curpage-1) * perpage;
if offset >= total then
set offset = (totalpage-1)*perpage ;
end if;
set @str = concat('select * from tablename order by orderfield desc limit ',offset,',',perpage);
prepare stmt1 from @str;
execute stmt1;
deallocate prepare stmt1;
end
//
delimiter ;
int end = 0;
...
select ...from tb limit begin,end;