一文看懂MYSQL分頁實現(xiàn)方案，值得收藏

述

昨天介紹了Oracle分頁實現(xiàn)方案，那么，mysql又是如何實現(xiàn)分頁呢？

參考官網：https://dev.mysql.com/doc/refman/5.7/en/select.html

mysql分頁實現(xiàn)

MySQL中實現(xiàn)分頁查詢：在數據量較小的情況下可使用limit查詢來實現(xiàn)分頁查詢，在數據量大的情況下使用建立主鍵或唯一索引來實現(xiàn)，另外可通過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:

Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.
Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.

1、limit分頁實現(xiàn)

先看一下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、傳統(tǒng)實現(xiàn)方式

一般情況下，客戶端通過傳遞 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;

2、查詢顯示行號（實現(xiàn)類似Oracle數據庫的ROWNUM()）

Oracle中有專門的rownum()顯示行號的函數，而MySQL沒有專門的顯示行號函數，但可以通過用@rownum自定義變量顯示行號。

一般實現(xiàn)過程如下：

SELECT 
  (@rownum :=@rownum + 1) AS rownum,
  t.* 
FROM
  table t,
  (SELECT @rownum :=0) AS rn

3、實例演示

3.1、環(huán)境準備

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;

覺得有用的朋友多幫忙轉發(fā)哦！后面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注下~

頁是每個網站并不可少的，但是處理往往非常麻煩，雖然有很多的插件可以使用，但復雜度依然不減，今天我們來手敲一個最簡單的實現(xiàn)方式，看夠之后分頁再也不是難題，而且以后用插件也懂原理了。手動擒來！

文章概略：

分頁的原理
分層實現(xiàn)
附贈Git開源代碼

我們先來說下原理吧，分頁就是數據庫的數據太多了，一次性查看不完，分批次查看。一般分頁分為從數據庫處理分頁和在服務器端處理分頁兩大類。

在數據庫分頁就是用多少數據jdbc去多少數據，比如取100條數據，則用sql語句查詢出100條語句就可以了。在服務器分頁就是把數據庫所有數據都取到服務器，然后客戶要多少數據返回多少數據。一般這種方式io要求很高，而且浪費資源。不建議使用，目前開發(fā)都是使用第一種方式，編譯好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頁（最后一頁）如下：
實現(xiàn)代碼如下：

<script>
/*
分頁：接收參數：總頁數，當前頁數
默認一個頁面顯示10個頁數
可能的情況：
總頁數小于10頁
全部顯示
總頁數大于10頁
當前頁小于10頁，顯示前10頁
當前頁大于10頁，顯示左右各5頁
當前頁數+5頁大于總頁數，顯示到最后一頁
否則顯示左右各5頁
*/
var path="${ pageContext.request.contextPath }";//獲得當前應用的地址現(xiàn)在是 /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>

最好我們最好加個“跳轉到第幾頁的功能”，如圖實現(xiàn)代碼：

到<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>
這個這么簡單，就不解釋了吧。

最后，我們說一下后臺實現(xiàn)，我用的是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

在線咨詢

上一篇：編程技巧：Jquery實時驗證，指定長度的「正整數」
下一篇：Java秒殺系統(tǒng)實戰(zhàn)系列-開發(fā)通用的發(fā)送郵件服務

您的項目需求

*請認真填寫需求信息，我們會在24小時內與您取得聯(lián)系。

整合營銷服務商

一文看懂MYSQL分頁實現(xiàn)方案，值得收藏

述