mysql的分页存储过程_Mysql分页查询通用存储过程

use guestbook;

delimiter $$

drop procedure if exists prc_page_result $$

create procedure prc_page_result (

in currpage      int,

in columns       varchar(500),

in tablename     varchar(500),

in sCondition    varchar(500),

in order_field   varchar(100),

in asc_field     int,

in primary_field varchar(100),

in pagesize      int

)

begin

declare sTemp  varchar(1000);

declare sSql   varchar(4000);

declare sOrder varchar(1000);

if asc_field = 1 then

set sOrder = concat(' order by ', order_field, ' desc ');

set sTemp  = '

else

set sOrder = concat(' order by ', order_field, ' asc ');

set sTemp  = '>(select max';

end if;

if currpage = 1 then

if sCondition <> '' then

set sSql = concat('select ', columns, ' from ', tablename, ' where ');

set sSql = concat(sSql, sCondition, sOrder, ' limit ?');

else

set sSql = concat('select ', columns, ' from ', tablename, sOrder, ' limit ?');

end if;

else

if sCondition <> '' then

set sSql = concat('select ', columns, ' from ', tablename);

set sSql = concat(sSql, ' where ', sCondition, ' and ', primary_field, sTemp);

set sSql = concat(sSql, '(', primary_field, ')', ' from (select ');

set sSql = concat(sSql, ' ', primary_field, ' from ', tablename, sOrder);

set sSql = concat(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder);

set sSql = concat(sSql, ' limit ?');

else

set sSql = concat('select ', columns, ' from ', tablename);

set sSql = concat(sSql, ' where ', primary_field, sTemp);

set sSql = concat(sSql, '(', primary_field, ')', ' from (select ');

set sSql = concat(sSql, ' ', primary_field, ' from ', tablename, sOrder);

set sSql = concat(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder);

set sSql = concat(sSql, ' limit ?');

end if;

end if;

set @iPageSize = pagesize;

set @sQuery = sSql;

prepare stmt from @sQuery;

execute stmt using @iPageSize;

end;

$$

delimiter;