MySql分页存储过程

begin

    declare sTemp  varchar(1000);

    declare sSql   varchar(4000);

    declare sOrder varchar(1000);

    declare s_primary_field varchar(100);

 

    IF currpage<1 THEN

SET currpage=1;

    END IF;

 

    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 sql_calc_found_rows ', fieldname, ' from ', tablename, ' where ');

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

        else

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

        end if;

    else

if instr(primary_field,'.')=0 then

set s_primary_field = primary_field;

else

set s_primary_field = substr(primary_field,inStr(primary_field,'.')+1,char_length(primary_field));

end if;

        if sCondition <> '' then

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

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

            set sSql = concat(sSql, '(tabtemp.', s_primary_field, ')', ' from (select ');

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

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

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

        else

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

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

            set sSql = concat(sSql, '(tabtemp.', s_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;

SET oRowsTotal=FOUND_ROWS();

 

    IF currpage!=1 THEN

SET oRowsTotal=oRowsTotal+(currpage-1)*pagesize;

    END IF;

end