sql 分页存储过程

- 分页存储过程
CREATE PROCEDURE ProductPage (
_sqlWhere VARCHAR (4000),
_pageIndex INT,
_pageSize INT,
OUT rowCount INT
)
BEGIN
DECLARE startRow INT DEFAULT 0; -- 定义一个接收计算要跳过的数据量的变量
SET startRow = (_pageIndex - 1) * _pageSize;

-- 分页的sql语句
SET @sqlStr = CONCAT("SELECT SQL_CALC_FOUND_ROWS * from tb_product p join tb_store s on p.StoreId=s.SId ",_sqlWhere," LIMIT ",startRow,",",_pageSize);

PREPARE sqlQUery FROM @sqlStr;

EXECUTE sqlQUery;

DEALLOCATE PREPARE sqlQUery;-- 销毁预处理变量 清理内存
-- 取得总数据量
SET rowCount = FOUND_ROWS();
END;

-- 分页存储过程的调用测试
CALL ProductPage ('', 1, 2 ,@num);

SELECT @num;