昨天介绍了Oracle分页实现方案,那么,mysql又是如何实现分页呢?
参考官网:https://dev.mysql.com/doc/refman/5.7/en/select.html
MySQL中实现分页查询:在数据量较小的情况下可使用limit查询来实现分页查询,在数据量大的情况下使用建立主键或唯一索引来实现,另外可通过order by对其排序。
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:
先看一下limit语法
SELECT * FROM TABLE
[ORDER BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
LIMIT子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT接受一个或两个数字参数。参数必须是一个整数常量。
如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)。
1.1、传统实现方式
一般情况下,客户端通过传递 pageNo(页码)、pageSize(每页条数)两个参数去分页查询数据库中的数据,在数据量较小(元组百/千级)时使用 MySQL自带的 limit 来解决这个问题
--pageNo:页码
--pagesize:每页显示的条数
select * from table limit (pageNo-1)*pageSize,pageSize;
1.2、建立主键或者唯一索引(高效)
在数据量较小的时候简单的使用 limit 进行数据分页在性能上面不会有明显的缓慢,但是数据量达到了 万级到百万级 sql语句的性能将会影响数据的返回。这时需要利用主键或者唯一索引进行数据分页;
--pageNo:页码
--pagesize:每页显示的条数
--假设主键或者唯一索引为 t_id
select * from table where t_id > (pageNo-1)*pageSize limit pageSize;
1.3、基于数据再排序
当需要返回的信息为顺序或者倒序时,对上面的语句基于数据再排序。order by ASC/DESC 顺序或倒序 默认为顺序
select * from table where t_id > (pageNo-1)*pageSize order by t_id limit pageSize;
Oracle中有专门的rownum()显示行号的函数,而MySQL没有专门的显示行号函数,但可以通过用@rownum自定义变量显示行号。
一般实现过程如下:
SELECT
(@rownum :=@rownum + 1) AS rownum,
t.*
FROM
table t,
(SELECT @rownum :=0) AS rn
3.1、环境准备
CREATE TABLE t (
EMPNO BIGINT ( 4 ) NOT NULL,
ENAME VARCHAR ( 10 ),
JOB VARCHAR ( 9 ),
MGR BIGINT ( 4 ),
HIREDATE date,
SAL BIGINT ( 10 ),
COMM BIGINT ( 10 ),
DEPTNO BIGINT ( 2 ),
PRIMARY KEY ( `EMPNO` )
) ENGINE=INNODB;
INSERT INTO t VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800', NULL, '20');
INSERT INTO t VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30');
INSERT INTO t VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30');
INSERT INTO t VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', NULL, '20');
INSERT INTO t VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30');
INSERT INTO t VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', NULL, '30');
INSERT INTO t VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', NULL, '10');
INSERT INTO t VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000', NULL, '20');
INSERT INTO t VALUES ('7839', 'KING', 'PRESIDENT', NULL, '1981-11-17', '5000', NULL, '10');
INSERT INTO t VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500', '0', '30');
INSERT INTO t VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100', NULL, '20');
INSERT INTO t VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', NULL, '30');
INSERT INTO t VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', NULL, '20');
INSERT INTO t VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300', NULL, '10');
commit;
3.2、limit分页
--查询第一页,每页显示5条数据
select * from t order by empno desc limit (1-1)*5,5;
--查询第二页,每页显示4条数据
select * from t order by empno desc limit (2-1)*4,4;
3.3、查询显示行号
--查询第二页,每页显示4条数据,并在第一列加上行号
select (@rownum :=@rownum + 1) AS rownum,t.* from t,
(SELECT @rownum :=0) AS rn
order by t.empno desc
limit 4,4;
觉得有用的朋友多帮忙转发哦!后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~
页是每个网站并不可少的,但是处理往往非常麻烦,虽然有很多的插件可以使用,但复杂度依然不减,今天我们来手敲一个最简单的实现方式,看够之后分页再也不是难题,而且以后用插件也懂原理了。手动擒来!
文章概略:
分页的原理
分层实现
附赠Git开源代码
我们先来说下原理吧,分页就是数据库的数据太多了,一次性查看不完,分批次查看。一般分页分为从数据库处理分页和在服务器端处理分页两大类。
在数据库分页就是用多少数据jdbc去多少数据,比如取100条数据,则用sql语句查询出100条语句就可以了。在服务器分页就是把数据库所有数据都取到服务器,然后客户要多少数据返回多少数据。一般这种方式io要求很高,而且浪费资源。不建议使用,目前开发都是使用第一种方式,编译好sql语句,在数据库服务器处理分页,而且数据库的服务器一般处理速度很快,利用商用。
分页无非就是服务器返回“总页数”和“当前页数”,然后客户端(一般是浏览器)对应处理。我们分层来写。
服务器可以采用java,或者php,或者asp等编写,返回“总页数”和“当前页数”。浏览器采取想应处理。我们采用javascript和html处理,逻辑判断交给js,页面修饰交给css,使用的时候你可以随时改变,方便快捷。
看下预览图(假设每个页面显示10个页数):css代码:用作美化,你可以改成你喜欢的样式
/*分页*/
/*在div paging中的a标签的属性,很简单的css*/
#paging{
width: 80%;
margin: 20px auto 40px auto;
text-align: center;
color: #666666;
font-size: 1.2em;
font-weight: 700;
}
#paging a{
color: #666666;
font-size: 1.2em;
font-weight: 700;
padding:5px 10px;
}
#paging a:hover{
background-color: #FF464E;
color: #F5F5F5;
}
html代码:
javascritp进行的逻辑判断,就是用来个document.writer,简单吧
备注:我是有的是java编写的,用了ssm框架,而且url是RESTful风格的,你可以改成普通风格的,代码注释很详细,我在这不解释了。
RESTful风格:http://localhost:8080/youxuan/index/2
普通风格:http://localhost:8080/youxuan/index?page=2
最终的结果:
总页数小于10页时(共6页),全部显示,访问第1页如下:
总页数大于10,当前页数小于10时,显示前10页,访问第2页如下:
总页数大于10,当前页数大于10时,我们让他显示左右各5页,但是考虑增加5页可能超出总页数。我们增加判断以后结果如下:
当前页+5页没超出总页数,显示当前页左右各5页,访问第10页如下:
当前页+5页超出总页数,显示到最后一页并向前显示10页,访问第20页(最后一页)如下:
实现代码如下:
<script>
/*
分页:接收参数:总页数,当前页数
默认一个页面显示10个页数
可能的情况:
总页数小于10页
全部显示
总页数大于10页
当前页小于10页,显示前10页
当前页大于10页,显示左右各5页
当前页数+5页大于总页数,显示到最后一页
否则显示左右各5页
*/
var path="${ pageContext.request.contextPath }";//获得当前应用的地址现在是 /youxuan
var url=path+"/index";//a标签的地址
//el表达式取得当前页和总页数
var nowPage=${Paging.nowPage};//当前页
var countPage=${Paging.countPage};//总页数
var count=10;//页面显示多少记录,默认每页显示10个记录
//判断“首页”是否显示
if(nowPage>1){
document.writeln(" <a href=\""+url+"/1\">首页</a>");//采用RESTful格式url
}
//总页数小于10页,显示全部页
if(countPage<=count){
for (var i=1;i<=countPage;i++) {
document.writeln(" <a href=\""+url+"/"+i+"\">"+i+"</a>");//采用RESTful格式url
}
}else{
//总页数大于10页
//当前页小于10页,显示前10页
if(nowPage<count){
for (var i=1;i<=count;i++) {
document.writeln(" <a href=\""+url+"/"+i+"\">"+i+"</a>");//采用RESTful格式url
}
//显示省略号和最后一页
document.writeln("...<a href=\""+url+"/"+countPage+"\">"+countPage+"</a>");
}else{
//当前页数大于等于10页,左右各显示5页
if (nowPage <=countPage) {
//当前页+5大于总页数,显示到最后一页,显示前10页
if (nowPage + 5 >=countPage) {
for (var i=nowPage - 10; i <=countPage; i++) {
document.writeln(" <a href=\"" + url + "/" + i + "\">" + i + "</a>");//采用RESTful格式url
}
} else {
//当前页+5小于总页数,左右各显示5页
for (var i=nowPage - 5; i <=nowPage + 5; i++) {
document.writeln(" <a href=\"" + url + "/" + i + "\">" + i + "</a>");//采用RESTful格式url
}
//显示省略号和最后一页
document.writeln("...<a href=\"" + url + "/" + countPage + "\">" + countPage + "</a>");
}
}
}
}
//判断尾页是否显示
if(nowPage<countPage){
document.writeln(" <a href=\""+url+"/"+countPage+"\">尾页</a>");
}
</script>
最好我们最好加个“跳转到第几页的功能”,如图实现代码:
到<input type="number" id="page" style="width: 80px;"/>页
<input type="button" onclick="goPage()" value="转到" style="background-color: #6c6c6c;padding: 6px; color: #F5F5F5;border: 0"/>
</div>
<script>
//跳转到第几页
function goPage() {
var page=document.getElementById("page").value;
var type=/^[1-9]+$/;
var re=new RegExp(type);
if (page.match(re)==null) {
//输入格式错误
alert("亲,页数一个正整数哦~");
}else {
//判断是否大于总页数
if(page>countPage){
alert("亲,总共只有"+countPage+"页呢~");
}else {
window.location.href=url+"/"+page;
}
}
}
</script>
这个这么简单,就不解释了吧。
最后,我们说一下后台实现,我用的是ssm框架,封装了一个分页的bean,代码如下
package com.youxuan.util;
/**
* Created by 两毛五哥哥 on 2016/8/26.
* 分页工具类
*/
public class Paging {
private int countDate;//总数据
private int countPage;//总页数
private int nowPage;//当前页数
private int pageCount;//每页显示多少数据
/**
* 构造函数
* @param countDate
* @param pageCount
* @param nowPage
*/
public Paging(int countDate,int pageCount,int nowPage){
this.countDate=countDate;
this.pageCount=pageCount;
this.nowPage=nowPage;
if(countDate%pageCount==0){
this.countPage=countDate/pageCount;
}else {
this.countPage=countDate/pageCount+1;
}
}
/**
* 构造函数,默认每页显示100条数据
* @param countDate
* @param nowPage
*/
public Paging(int countDate,int nowPage){
this(countDate,100,nowPage);
}
public int getCountDate() {
return countDate;
}
public void setCountDate(int countDate) {
this.countDate=countDate;
}
public int getCountPage() {
return countPage;
}
public void setCountPage(int countPage) {
this.countPage=countPage;
}
public int getNowPage() {
return nowPage;
}
public void setNowPage(int nowPage) {
this.nowPage=nowPage;
}
public int getPageCount() {
return pageCount;
}
public void setPageCount(int pageCount) {
this.pageCount=pageCount;
}
}
然后再servlet(controller)中返回这个封装的对象。你可以不用封装也行,我觉的这样可移植性好,下次做项目直接拿来用了。这个项目我做完会开源贡献的,喜欢的点个赞吧。
最后:git代码下载地址:https://git.oschina.net/lovepeng/share-for-you.git
@两毛五哥哥,90逗逼程序员,欢迎骚扰
绍:
这是一款来自国外的分页样式,主要以灰色为主,红色为辅。经测试,兼容所有浏览器,但 IE6 png 背景图片未处理,请自行处理。如果喜欢这款分页就拿去使用吧。
演示地址:
https://www.361zy.com/demo/520/
下载地址:
https://cloud.06dn.com/s/RWZC3
解压码:
WpUiD9SO
*请认真填写需求信息,我们会在24小时内与您取得联系。