Mysql分页过程
来源:互联网 发布:知乎分享到朋友圈 编辑:程序博客网 时间:2024/06/07 02:26
BEGIN
/*
**************过程简介**************
说明:该过程实现了分页功能。支持where条件及order排序,但不支持group分组及多表级联查询
由于MYSQL目前无法获取动态SQL返回值(需临时表支持,实现起来太复杂了,晕一个先@@)
所以将总记录数及当前页数首先进行查询,然后再将总记录数及页数放到每一条记录中
**************参数说明**************
表名
IN tableName varchar(100),
字段名,多个字段以,分隔
IN filedsNames varchar(100),
每页显示记录数
IN pageSize int,
当前页
IN pageIndex int,
排序,多个排序以,分隔
IN sortName varchar(500),
where条件,多个条件以,分隔
IN strWhere varchar(500)
**************调用方法**************
call PageDiv('t_user','vcName,vcPassword',3,1,'','')
*/
DECLARE filedlist varchar(500);/*字段列表*/
DECLARE counts int default 0;/*总记录数*/
DECLARE intPages int default 0;/*总页数*/
DECLARE strPages varchar(20);/*总页数*/
/*获取总记录数*/
if strWhere=''||strWhere=null then
set @sqlStr=concat("select count(*) into @counts from ",tableName);
else
set @sqlStr=concat("select count(*) into @counts from ",tableName," where ",strWhere);
end if;
PREPARE STMT FROM @sqlStr;
EXECUTE STMT;
/*获取总页数*/
if strWhere=''||strWhere=null then
set @sqlStr=concat("select count(*)/",pageSize," into @intPages from ",tableName);
else
set @sqlStr=concat("select count(*)/",pageSize," into @intPages from ",tableName," where ",strWhere);
end if;
PREPARE STMT FROM @sqlStr;
EXECUTE STMT;
/*总页数返回值小于1的,均按1取值;大于1但有小数位的,则通过截取字符串的方式加1*/
if(@intPages<1) then
set @intPages=1;
elseif(@intPages>1) then
set @strPages=cast(@intPages as char);
if(INSTR(@strPages,".")>=0) then
set @strPages=LEFT(@strPages,INSTR(@strPages,".")-1);
set @intPages=cast(@strPages as char)+1;
end if;
end if;
/*分页操作*/
if filedsNames=''||filedsNames=null THEN
set filedlist='*';
else
set filedlist=filedsNames;
end if;
if strWhere=''||strWhere=null then
if sortName=''||sortName=null then
set @strSQL=concat('SELECT ',filedlist,',',@counts,' as totalRecord ,',@intPages,' as totalPage FROM
',tableName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
else
set @strSQL=concat('SELECT ',filedlist,',',@counts,' as totalRecord ,',@intPages,' as totalPage FROM
',tableName,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
end if;
else
if sortName=''||sortName=null then
set @strSQL=concat('SELECT ',filedlist,',',@counts,' as totalRecord ,',@intPages,' as totalPage FROM
',tableName,' WHERE ',strWhere,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
else
set @strSQL=concat('SELECT ',filedlist,',',@counts,' as totalRecord ,',@intPages,' as totalPage FROM
',tableName,' WHERE ',strWhere,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
end if;
end if;
PREPARE stmt_strSQL FROM @strSQL;
EXECUTE stmt_strSQL;
DEALLOCATE PREPARE stmt_strSQL;
END
/*
**************过程简介**************
说明:该过程实现了分页功能。支持where条件及order排序,但不支持group分组及多表级联查询
由于MYSQL目前无法获取动态SQL返回值(需临时表支持,实现起来太复杂了,晕一个先@@)
所以将总记录数及当前页数首先进行查询,然后再将总记录数及页数放到每一条记录中
**************参数说明**************
表名
IN tableName varchar(100),
字段名,多个字段以,分隔
IN filedsNames varchar(100),
每页显示记录数
IN pageSize int,
当前页
IN pageIndex int,
排序,多个排序以,分隔
IN sortName varchar(500),
where条件,多个条件以,分隔
IN strWhere varchar(500)
**************调用方法**************
call PageDiv('t_user','vcName,vcPassword',3,1,'','')
*/
DECLARE filedlist varchar(500);/*字段列表*/
DECLARE counts int default 0;/*总记录数*/
DECLARE intPages int default 0;/*总页数*/
DECLARE strPages varchar(20);/*总页数*/
/*获取总记录数*/
if strWhere=''||strWhere=null then
set @sqlStr=concat("select count(*) into @counts from ",tableName);
else
set @sqlStr=concat("select count(*) into @counts from ",tableName," where ",strWhere);
end if;
PREPARE STMT FROM @sqlStr;
EXECUTE STMT;
/*获取总页数*/
if strWhere=''||strWhere=null then
set @sqlStr=concat("select count(*)/",pageSize," into @intPages from ",tableName);
else
set @sqlStr=concat("select count(*)/",pageSize," into @intPages from ",tableName," where ",strWhere);
end if;
PREPARE STMT FROM @sqlStr;
EXECUTE STMT;
/*总页数返回值小于1的,均按1取值;大于1但有小数位的,则通过截取字符串的方式加1*/
if(@intPages<1) then
set @intPages=1;
elseif(@intPages>1) then
set @strPages=cast(@intPages as char);
if(INSTR(@strPages,".")>=0) then
set @strPages=LEFT(@strPages,INSTR(@strPages,".")-1);
set @intPages=cast(@strPages as char)+1;
end if;
end if;
/*分页操作*/
if filedsNames=''||filedsNames=null THEN
set filedlist='*';
else
set filedlist=filedsNames;
end if;
if strWhere=''||strWhere=null then
if sortName=''||sortName=null then
set @strSQL=concat('SELECT ',filedlist,',',@counts,' as totalRecord ,',@intPages,' as totalPage FROM
',tableName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
else
set @strSQL=concat('SELECT ',filedlist,',',@counts,' as totalRecord ,',@intPages,' as totalPage FROM
',tableName,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
end if;
else
if sortName=''||sortName=null then
set @strSQL=concat('SELECT ',filedlist,',',@counts,' as totalRecord ,',@intPages,' as totalPage FROM
',tableName,' WHERE ',strWhere,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
else
set @strSQL=concat('SELECT ',filedlist,',',@counts,' as totalRecord ,',@intPages,' as totalPage FROM
',tableName,' WHERE ',strWhere,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
end if;
end if;
PREPARE stmt_strSQL FROM @strSQL;
EXECUTE stmt_strSQL;
DEALLOCATE PREPARE stmt_strSQL;
END
- mysql 分页存储过程
- MySQL 分页存储过程
- MySql分页存储过程
- MySql分页存储过程
- MySql分页存储过程
- MySql分页存储过程
- MySql分页存储过程
- MySql分页存储过程
- Mysql分页存储过程
- Mysql分页存储过程
- Mysql分页过程
- Mysql分页存储过程
- MySql 分页存储过程
- mysql 分页存储过程
- MySQL分页存储过程
- MySql 分页存储过程
- mysql分页存储过程
- MySql 通用分页存储过程
- 关于main函数的参数argc,argv
- 可变长的实参列表,实参对象arguments
- Jquery中的控制DOM对象
- HDU 1198 并查集
- 第七周-项目4-复数模板类
- Mysql分页过程
- [IOS 图像处理]--相机的各种处理效果DLCImagePickerController
- [gallery] 显示sdcard上的图片,及它的缩略图
- reverse integer
- 词法分析——Flex介绍(包括如何编译和运行)
- PCI 9054
- MATLAB旋转体的绘制
- mysql学习笔记
- 问题五十八:贺老师教1班和2班两个班的C++,1班同学的学号为1-41,2班同学的学号为42-84,现在每个班要抽签确定5名同学去参加学校组织的教学效果评价,请完成这个“抽签“。