前段時間沒有給出SQLServer轉到Mysql的通用存儲過程，本著共享的精神，為大家奉獻這段MySQL分頁查詢通用存儲過程，假設所用數據庫為guestbook：
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 = '<(select min';
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;
可以存儲為數據庫腳本，然後用命令導入：
MySQL -u root -p < pageResult.sql;
調用：call prc_page_result(1, "*", "Tablename", "", "columnname", 1, "PKID", 25);
