CREATE  PROCEDURE `SP_Pagination`(IN _TableName varchar(1000),            -- 表名

IN _OrderString varchar(200),         -- 排序字段(必须!支持多字段不用加order by)

IN _PageIndex int,               -- 指定当前为第几页

IN _PageSize int,                     -- 每页多少条记录

IN _ReFieldsStr varchar(200),   -- 字段名(全部字段为*)

IN _WhereString varchar(500),  -- 条件语句(不用加where)

INOUT _TotalRecord int)

BEGIN

    -- 处理开始点和结束点

    Declare P_StartRecord int;

    Declare P_TotalCountSql varchar(500);

    Declare P_SqlString varchar(2000);

    set P_StartRecord = (_PageIndex-1)*_PageSize;

    SET P_TotalCountSql=concat('select count(*) into @totalRecord from ',_TableName);-- 总记录数语句

    SET P_SqlString =concat('select ',_ReFieldsStr,' from ',_TableName);-- 查询语句

    --

    IF (_WhereString<>'' AND _WhereString is NOT null) THEN

            SET P_TotalCountSql=concat(P_TotalCountSql,' where ',_WhereString);

            SET P_SqlString =concat(P_SqlString,' where ',_WhereString);

    END IF;

        SET @sqlcounts=P_TotalCountSql;

    prepare stmt from @sqlcounts;

    execute stmt;

        deallocate prepare stmt;

   #获取动态SQL语句返回值

  set _TotalRecord = @totalRecord; -- 返回总记录数

    -- 执行主语句

    set P_SqlString =CONCAT(P_SqlString,' order by ',_OrderString,' LIMIT ',P_StartRecord,',',_PageSize);

        set @sqlselect = P_SqlString;

    prepare stmtselect from @sqlselect;

    execute stmtselect;

    deallocate prepare stmtselect;

END