按照分页状态划分,mysql5支持动态分页和静态分页。
按照分页对象划分,mysql5支持sql分页和存储过程分页。
mysql5分页的基本原理是依靠limit字句来对查询分页。
mysql5的limit语句不支持表达式,只支持确定的整数值或者预定义参数。这是一个大陷阱!也是mysql最为变态的一个特征了,让我迷茫了很长时间才知道。不过db2、oracle等数据库分页参数都支持表达式。也是本文中的一个焦点问题。
mysql5的语句的基本格式是在select语句最后添加一个返回记录的限制数,有两个参数(取值范围均大于等于0),分别用来限制返回记录的起起始位置和返回记录的数量,。但是不包含起始位置的记录,例如,select * from tab limit 1,3; 则指挥显示第2、3、4三条记录,第1条记录不会被返回,如果要返回则应该从0开始。
mysql5的分
下面从分页对象的角度研究mysql5的分页实现,分为sql分页和存储过程分页。其中sql分页分为两种类型,一种是静态的,一种是动态的。而存储过程下只讨论动态的,静态的没有意义。
环境:
windows xp professional 简体中文版
mysql-5.0.45-win32
开发工具:
sql manager 2007 for mysql
sqlyog enterprise 6.05
测试脚本:
一个表,参看[url]http://img1.51cto.com/attachment/200708/62575_1188540717.txt[/url],打开后,里面有db2、mysql两种数据库脚本,执行mysql的。
一、sql分页
1、静态分页
select xh,mldm,mlmc from dm_hy_cy limit 2,3;
执行结果:
+----+------+------------------------------+
| xh | mldm | mlmc |
+----+------+------------------------------+
| 3 | c | 制造业 |
| 4 | d | 电力、燃气及水的生产和供应业 |
| 5 | e | 建筑业 |
+----+------+------------------------------+
截图如下:
2、动态分页
prepare stmt1 from 'select xh,mldm,mlmc from dm_hy_cy limit ?,?';
set @start=2;
set @size=3;
execute stmt1 using @start,@size;
说明:
预制语句的sql语法基于三个sql语句:
prepare stmt_name from preparable_stmt;
stmt_name 是预处理语句的标识,是一个将要执行的sql语句。里面如果有参数,则用“?”替换,“?”在这里也叫占位符。类似jdbc预处理sql语句。
execute stmt_name [using @var_name [, @var_name] ...];
执行一个预处理语句stmt_nameusing表示使用了动态变量(变量名前有“@”标识符),@var_name [, @var_name] ... 是参数列表,按顺序赋值给预处理sql中的(占位)参数。
{deallocate | drop} prepare stmt_name;
删除或者分配存储单元给预处理语句。
说明:在mysql5中,可以使用动态变量,动态变量的类型是不确定的,可以多次赋不同类型的值,动态变量的类型取决于其值的具体类型。动态变量定义的定义和使用就像随地大小便一样,有需要就当即解决。定义的方式也很简单:set @var_name=...,在定义的时候就给定了值。
执行结果和1一样,截图如下:
二、存储过程分页
-- 设定语句定界符为 $$
delimiter $$
-- 预防性删除存储过程
drop procedure if exists testdb.sp_pagination$$
-- 声明创建存储过程
create procedure sp_pagination(in in_start integer(11), in in_size integer(11))
-- ------------------------------------------
-- 说明:mysql5存储过程分页
-- 语言:mysql
-- 作者:熔 岩
-- 日期:2007-9-1
-- ------------------------------------------
begin
-- 定义两个动态变量,接收存储过程中的分页参数
set @start=in_start;
set @size=in_size;
-- 定义预处理sql查询语句
prepare stmt from 'select mldm,mlmc from dm_hy_cy limit ?,?';
-- 执行预处理语句,并用动态变量替换参数占位符
execute stmt using @start,@size;
end
-- 存储过程声明结束
$$
-- 设定语句定界符为 “;”,以保证后来的以分号结尾的sql正常执行
delimiter ;
存储过程部署截图:
存储过程定义好了后编译以下。
然后用命令执行:
call sp_pagination(2,3);
返回结果如下:
反面教材:如果不使用预处理sql,不管你怎么搞,这个动态根据传递参数分页都是无法实现的(至少目前我这个环境下如此,如果你有兴趣可以挑战一下看看)。
下面我就给出我刚开始用db2的经验实现mysql5存储过程分页的例子,结果当然是失败了。现在给出来,大家看看:
delimiter $$
drop procedure if exists testdb.sp_pagination$$
create procedure sp_pagination(in in_start integer(11), in in_size integer(11))
-- ------------------------------------------
-- 说明:反面教材,切勿模仿!mysql5存储过程分页
-- 语言:mysql
-- 作者:熔 岩
-- 日期:2007-9-1
-- ------------------------------------------
begin
-- 直接利用调用参数在limit子句中用
select mldm,mlmc from dm_hy_cy limit in_start,in_size;
end
$$
delimiter ;
总结:mysql5存储过程分页目前我所知道就这几种,也许mysql会在将来新版本中支持limit的表达式参数,但眼前的是要解决问题。希望这篇文章能对您mysql过程分页提供一种解决方案,节省摸索研究的时间。