前段時間沒有給出SQLServer轉(zhuǎn)到MySQL的通用存儲過程，本著共享的精神，為大家奉獻這段Mysql分頁查詢通用存儲過程，假設(shè)所用數(shù)據(jù)庫為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;
可以存儲為數(shù)據(jù)庫腳本，然后用命令導(dǎo)入：

mysql -u root -p < pageResult.sql;

調(diào)用：call prc_page_result(1, "*", "Tablename", "", "columnname", 1, "PKID", 25);

http://blog.csdn.net/fcrpg2005/archive/2007/03/07/1522713.aspx