mysql灵活分页存储过程

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

 

转载于:https://www.cnblogs.com/daxiongblog/p/5366599.html