MYSQL分页存储过程代码:
DROP PROCEDURE IF EXISTS `pro_pager`;
CREATE DEFINER = `root`@`%` PROCEDURE `pro_pager`(
in p_pageNo int, /*当前页*/
in p_perPageCnt int, /*每页记录数*/
in p_sql VARCHAR(2000), /*查询sql语句*/
out v_totalRowsCnt int, /*记录总条数*/
out v_totalPageCnt int /*记页数*/ )
BEGIN
/*当传入查询页数为null或者<1时,赋p_pageNo=1 */
IF p_pageNo IS NULL OR p_pageNo < 1 THEN
SET p_pageNo = 1;
END IF;
SET @rowsCnt = 0;
SET @pagesCnt = 0;
SET @sqlCnt = CONCAT('select count(1) into @rowsCnt from (',p_sql,') as t'); -- 统计总记录数sql
/*统计总记录数-预处理*/
PREPARE s_cnt from @sqlCnt;
EXECUTE s_cnt;
DEALLOCATE PREPARE s_cnt;
SET v_totalRowsCnt = @rowsCnt;
-- SET @pagesCnt = floor((@rowsCnt + p_perPageCnt - 1) / p_perPageCnt); -- 计算总页数
SET @pagesCnt = ceil(@rowsCnt / p_perPageCnt); -- 计算总页数
/*当传入查询页数>总页数时,赋p_pageNo=总页数 */
IF p_pageNo > @pagesCnt THEN
SET p_pageNo = @pagesCnt;
END IF;
SET v_totalPageCnt = @pagesCnt;
SET @limitStart = (p_pageNo - 1) * p_perPageCnt; -- 查询记录起始行
SET @limitEnd = p_perPageCnt; -- 查询记录结束行
SET @sqlQry = CONCAT(p_sql, ' limit ', @limitStart, ',', @limitEnd); -- 查询记录集sql
/*查询记录集-预处理*/
PREPARE record from @sqlQry;
EXECUTE record;
DEALLOCATE PREPARE record;
END ;