【转】mysql通用分页存储过程

dropprocedureifexists prc_page_result;
createprocedure 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 =1then
        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';
    endif;
    
    if currpage =1then
        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 ?');
        endif;
    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 ?');
        endif;
    endif;
    set@iPageSize= pagesize;
    set@sQuery= sSql;
    prepare stmt from@sQuery;
    execute stmt using @iPageSize;
end;

 

//调用

 

call prc_page_result(1, "字段列表", "表名", "条件", "排序字段", 1, "id", 25);
参数说明:

 

第几页,显示的字段(如name,id),表名,Where后面的条件,排序的字段,1表示降序,id主键,25每页显示条数

 

 

转载于:https://www.cnblogs.com/wuqingqiang/archive/2012/05/08/2490265.html