mysql存储过程通用分页

关键字：mysql存储过程通用分页


sql代码如下：


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;





调用方式如下：


call prc_page_result(1, "*", "test", "1=1", "name", 0, "sys_id", 25);


下面是解释：
call prc_page_result(当前页, "要查询的字段列表或*", "表名称", "条件", "排序字段", 排序方式0正序1倒序, "主键字段", 每页长度);




附件是 mysql   测试数据：欢迎下载使用。