MySQL数据库中，自定义存储过程查询表中的数据，带有分页功能。具体实例如下代码：
代码语言：javascript复制 1 DROP PROCEDURE IF EXISTS `sampledb`.`proc_GetPagedDataSet`;
2
3 CREATE DEFINER=`root`@`%` PROCEDURE `proc_GetPagedDataSet`(
4 IN tableName VARCHAR (20), /*表名
5 IN pageIndex INT, /*当前页*/
6 IN pageSize INT, /*每页记录数*/
7 OUT pageCount INT,/*总记录分页数*/
8 out totalRecordCount int /*总记录数*/)
9 BEGIN
10 /*获取表中的记录数*/
11 set @recordCount=0;
12 set @sql='';
13 set @sql=CONCAT('select count(*) into @recordCount from ',tableName);
14 prepare stmt from @sql; /*预处理 自定义sql字符串*/
15 execute stmt; /*执行自定义sql语句*/
16 deallocate prepare stmt; /*释放预处理资源*/
17
18 set totalRecordCount = @recordCount; /*总记录数*/
19
20 /*计算返回多少页*/
21 set @tmp=1;
22 set @tmp=@recordCount mod pageSize; /*取余数*/
23 if @tmp=0 then
24 set pageCount=@recordCount div pageSize;
25 else
26 set pageCount=@recordCount div pageSize 1;
27 end if;
28
29 /*分页显示数据*/
30 set @sql=CONCAT('select * from ',tableName,' limit ',(pageIndex-1)*pageSize,',',pageSize);
31 prepare stmt from @sql; /*预处理 自定义sql字符串*/
32 execute stmt;/*执行自定义sql语句*/
33 deallocate prepare stmt; /*释放预处理资源*/
34 END
===============================================================================================
MySQL大数据量分页性能优化
