MYSQL5 分页查询深入研究

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:
 
按照分页状态划分,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://lavasoft.blog.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 ;
 
不过这个反面教材在DB2下是完全可行的。请参看“ DB2存储过程分页测试”一文
 
 
总结:MySQL5存储过程分页目前我所知道就这几种,也许MySQL会在将来新版本中支持LIMIT的表达式参数,但眼前的是要解决问题。希望这篇文章能对您MySQL过程分页提供一种解决方案,节省摸索研究的时间。也希望各位博友广开言路,提出更多的见解。
 
 本文转自 leizhimin 51CTO博客,原文链接:http://blog.51cto.com/lavasoft/40626,如需转载请自行联系原作者
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2天前
|
SQL 关系型数据库 MySQL
【MySQL】基本查询(表的增删改查)-- 详解(上)
【MySQL】基本查询(表的增删改查)-- 详解(上)
|
2天前
|
关系型数据库 MySQL
【MySQL】复合查询(重点)-- 详解
【MySQL】复合查询(重点)-- 详解
|
8天前
|
SQL 关系型数据库 MySQL
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
|
1天前
|
关系型数据库 MySQL
实现MySQL分页查询的三种方式~
实现MySQL分页查询的三种方式~
|
2天前
|
SQL 关系型数据库 MySQL
简简单单 My SQL 学习笔记(3)——连接和嵌套查询
简简单单 My SQL 学习笔记(3)——连接和嵌套查询
|
2天前
|
SQL 关系型数据库 MySQL
简简单单 My SQL 学习笔记(2)——分组和简单数据的查询
简简单单 My SQL 学习笔记(2)——分组和简单数据的查询
|
2天前
|
关系型数据库 MySQL 数据库管理
【MySQL进阶之路 | 基础篇】子查询之二(不相关子查询与相关子查询)
【MySQL进阶之路 | 基础篇】子查询之二(不相关子查询与相关子查询)
|
2天前
|
SQL Oracle 关系型数据库
【MySQL】基本查询(表的增删改查)-- 详解(下)
【MySQL】基本查询(表的增删改查)-- 详解(下)
|
6天前
|
关系型数据库 MySQL Linux
查询服务器中有没有mysql - 蓝易云
同样,如果MySQL已经安装,这个命令将会显示MySQL的版本信息。如果没有安装,系统将会显示一个错误消息。
57 0
|
6天前
|
SQL 关系型数据库 MySQL
查询mysql版本sql - 蓝易云
执行这个命令后,MySQL将返回当前正在运行的版本信息。
42 0

推荐镜像

更多