mysql5分页查询深入探讨

发布时间:2019-07-27编辑:脚本学堂
本文介绍了mysql分页查询的相关原理与实现方法,mysql5分页的相关内容,有需要的朋友参考下。
本节内容:
mysql/fenye/ target=_blank class=infotextkey>mysql分页解析

按照分页状态划分,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过程分页提供一种解决方案,节省摸索研究的时间。