分頁功能是很常見的功能，特別是當資料量越來越大的時候，分頁查詢是必不可少的。實現分頁功能有很多種方式，如果使用的ORM框架是mybatis的話，有開源的分頁外掛可以使用，如：Mybatis-PageHelper。如果不使用分頁外掛，那麼就需要手動分頁了，由於不同的資料庫實現分頁的SQL語句並不一致，如Mysql使用的是limit關鍵字，而Oracle使用的是rownum，所以本文字文講解的分頁方案只適用於Mysql資料庫。
基於limit的分頁方案
首先講講分頁操作必須滿足的幾個要求：一個是有序性，一個是不重複。有序性可以看成是不重複的前提條件，因為假如資料是無序的，那麼就不能保證多個分頁之間是不重複的。因此分頁操作往往需要先對資料進行排序，然後再加上分頁條件。我們講的第一種方案是基於limit的分頁方案，也是很多分頁外掛使用的分頁方案。我們先來看看我們的測試資料。
先看一下表結構：
mysql> desc user;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | bigint(20) | NO | PRI | NULL | |
| name | char(50) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set
複製程式碼可以看到我們的user表只有2列，分別是bigint型的id和char型的name。
接下來看下錶資料：
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
| 45116 |
+----------+
1 row in set
mysql> select * from user order by id asc limit 10;
+----+--------+
| id | name |
+----+--------+
| 0 | user_0 |
| 1 | user_1 |
| 2 | user_2 |
| 3 | user_3 |
| 4 | user_4 |
| 5 | user_5 |
| 6 | user_6 |
| 7 | user_7 |
| 8 | user_8 |
| 9 | user_9 |
+----+--------+
10 rows in set
複製程式碼可以看到資料總行數大概45000條。
基於limit實現分頁是比較簡單的：
mysql> select * from user order by id asc limit 10000,10;
+-------+------------+
| id | name |
+-------+------------+
| 10000 | user_10000 |
| 10001 | user_10001 |
| 10002 | user_10002 |
| 10003 | user_10003 |
| 10004 | user_10004 |
| 10005 | user_10005 |
| 10006 | user_10006 |
| 10007 | user_10007 |
| 10008 | user_10008 |
| 10009 | user_10009 |
+-------+------------+
10 rows in set
複製程式碼其中，limit後面的第一個參數列示下標，也就是從第10000行記錄開始取，第二個參數列示總共取10行記錄。
使用limit實現分頁功能使用起來非常簡單，但是有沒有什麼問題呢？
我們先來回顧一下前面說的分頁需要滿足的2個要素：有序性和不重複。上述的語句我們已經使用了order by 進行排序，所以是可以滿足有序性的，但滿足了不重複了嗎？假設在查詢當前頁跟下一頁之間插入了一條記錄，且該資料的id小於當前頁記錄中最大的id，會怎麼樣呢？我們測試一下就知道了：
mysql> select * from user order by id asc limit 10000,10;
+-------+------------+
| id | name |
+-------+------------+
| 10000 | user_10000 |
| 10001 | user_10001 |
| 10002 | user_10002 |
| 10003 | user_10003 |
| 10004 | user_10004 |
| 10005 | user_10005 |
| 10006 | user_10006 |
| 10007 | user_10007 |
| 10008 | user_10008 |
| 10009 | user_10009 |
+-------+------------+
10 rows in set
mysql> insert into user(id,name) values(-1,'user_-1');
Query OK, 1 row affected
mysql> select * from user order by id asc limit 10010,10;
+-------+------------+
| id | name |
+-------+------------+
| 10009 | user_10009 |
| 10010 | user_10010 |
| 10011 | user_10011 |
| 10012 | user_10012 |
| 10013 | user_10013 |
| 10014 | user_10014 |
| 10015 | user_10015 |
| 10016 | user_10016 |
| 10017 | user_10017 |
| 10018 | user_10018 |
+-------+------------+
10 rows in set
複製程式碼可以看到，當我們在相鄰的2頁查詢之間插入一條記錄的時候，後面一頁跟前面一頁有記錄重複了（id為10009的記錄在相鄰2頁中都出現了）。原因在於插入一條記錄之後，分頁結構已經改變了，所以才會出現重複資料。
因此，使用limit進行分頁似乎不是很優雅啊，接下來將介紹另外一種分頁的寫法。
基於limit與比較的分頁方案
另外一種分頁的寫法可以這樣考慮，比如我們要取的是從第10000行開始的10行記錄，那麼我們可以先把大於或等於10000行的資料查出來並排序，然後再取出前10行記錄，這樣也可以完成分頁。接下來看具體的SQL語句：
mysql> select * from user where id >=10000 order by id asc limit 10;
+-------+------------+
| id | name |
+-------+------------+
| 10000 | user_10000 |
| 10001 | user_10001 |
| 10002 | user_10002 |
| 10003 | user_10003 |
| 10004 | user_10004 |
| 10005 | user_10005 |
| 10006 | user_10006 |
| 10007 | user_10007 |
| 10008 | user_10008 |
| 10009 | user_10009 |
+-------+------------+
10 rows in set
複製程式碼那麼這種寫法可以防止上面出現的問題嗎？我們做個試驗就知道了。
mysql> select * from user where id >=10000 order by id asc limit 10;
+-------+------------+
| id | name |
+-------+------------+
| 10000 | user_10000 |
| 10001 | user_10001 |
| 10002 | user_10002 |
| 10003 | user_10003 |
| 10004 | user_10004 |
| 10005 | user_10005 |
| 10006 | user_10006 |
| 10007 | user_10007 |
| 10008 | user_10008 |
| 10009 | user_10009 |
+-------+------------+
10 rows in set
mysql> insert into user(id,name) values(-4,'user_-4');
Query OK, 1 row affected
mysql> select * from user where id >=10010
order by id asc limit 10;
+-------+------------+
| id | name |
+-------+------------+
| 10010 | user_10010 |
| 10011 | user_10011 |
| 10012 | user_10012 |
| 10013 | user_10013 |
| 10014 | user_10014 |
| 10015 | user_10015 |
| 10016 | user_10016 |
| 10017 | user_10017 |
| 10018 | user_10018 |
| 10019 | user_10019 |
+-------+------------+
10 rows in set
複製程式碼可以看到，當在相鄰的兩頁查詢之間插入資料時，分頁查詢結果不會出現重複。其實也很好理解，因為雖然插入記錄後，分頁的結構變了，但是由於我們現在的分頁查詢是從固定的id開始查的，所以插入新的資料對後面的分頁結果沒有影響。
當然，這種分頁查詢也是有限制的。其只適用於用來排序的列具有唯一性的情況，在上述例子中，id列是主鍵，所以具有唯一性，故可以使用這種方式分頁。如果用來排序的列不具有唯一性，比如說是時間戳，那麼這種分頁方式也可能出現重複，大家可以想想是為什麼。
如果覺得這篇文章對你有幫助，可以掃描下方二維碼，關注我的公眾號“Java架構沉思錄”。