Mysql的分页存储过程

CREATE PROCEDURE pages #创建一个分页查找的存储过程

(
tableName VARCHAR(50), #表名
columnName VARCHAR(200), #选择的列名,多个列名用,号隔开
whereStr VARCHAR(200), #选择条件,传入参数要自带where
orderBy VARCHAR(50), #排序依据,自带order by
currentPage int, #当前页
pageSize int, #每页记录条数
out totalPage int #计算出总页数，赋值给该变量
)
BEGIN
DECLARE temp int DEFAULT 0; #计算总页数的中间变量；
DECLARE step int DEFAULT 0; #声明要跳过的记录数


set @count = CONCAT('select count(*) into @total from ',tableName);
PREPARE count FROM @count;
EXECUTE count;
DEALLOCATE PREPARE count;


#通过记录总数计算出总页数
set temp = FLOOR(@total / pageSize);
if @total % pageSize != 0 then
set temp = temp + 1;
end if;
set totalPage = temp;


#分页查询
  set step = (currentPage - 1) * pageSize;
set @limitStr = CONCAT('limit ',step,',',pageSize);
set @mainSql = CONCAT('select ',columnName,' from ',tableName,' ',whereStr,
' ',orderBy,' ',@limitStr);
PREPARE mainSql FROM @mainSql;
EXECUTE mainSql;
DEALLOCATE PREPARE mainSql;
END