PostgreSQL数据分页

      继Golang学习系列第五天： Golang和PostgreSQL开发 RESTful API，有一个接口是查询文章，不过由于文章会有很多，所以需要分页显示，类似于下面的效果

我使用的是PostgreSQL数据库，就以它为例展示如何完成后台数据分页，还好是数据库本身带有的特性。

数据库文章表里原始数据：

csdn=# select * from articles order by  updated_at desc;
 id |          created_at           |          updated_at           |          deleted_at           |                           title         
                  |                                               content                                                |         category   
       |             tag              |                    author                    
----+-------------------------------+-------------------------------+-------------------------------+-----------------------------------------
------------------+------------------------------------------------------------------------------------------------------+--------------------
-------+------------------------------+----------------------------------------------
  6 | 2020-07-16 13:42:20.291723+08 | 2020-07-16 13:42:20.291723+08 | 2020-07-16 13:46:40.626837+08 | 测试删除                                
                  | 测试删除                                                                                             | ['golang','postgrxy
esql'] | ['golang','go']              | ['dongguangming', 'dgm']
  5 | 2020-07-16 13:09:47.795349+08 | 2020-07-16 13:09:47.795349+08 |                               | 我在江宁                                
                  | 江宁地广，路很宽                                                                                     | ['南京','江宁']    
       | ['南京','百家湖','购物']     | ['dmg']
  4 | 2020-07-16 13:08:35.71687+08  | 2020-07-16 13:08:35.71687+08  |                               | 新街口                                  
                  | 新街口商场很多，东西也贵                                                                             | ['南京','金陵']    
       | ['南京','金陵','购物']       | ['dmg','董广明']
  3 | 2020-07-16 13:07:18.856783+08 | 2020-07-16 13:07:18.856783+08 |                               | 南京总统府咋样                          
                  | 南京总统府很好玩                                                                                     | ['南京','金陵']    
       | ['南京','金陵','旅游']       | ['董广明']
  2 | 2020-07-16 13:05:56.92499+08  | 2020-07-16 13:05:56.92499+08  |                               | Java开发RESTful API                     
                  | java开发接口开发过程                                                                                 | ['java','postgresql
']     | ['java','code','postgresql'] | ['dongguangming', '董广明']
  1 | 2020-07-16 12:57:17.621445+08 | 2020-07-16 12:57:17.621445+08 |                               | Golang学习系列第五天： Golang和PostgreSQ
L开发 RESTful API | 记录接口开发过程，出了问题，直接看博客，不，需要再查其他网文，节省时间干其他事，时间就是毛爷爷！！！ | ['golang','postgrxy
esql'] | ['golang','go','postgresql'] | ['dongguangming', 'dgm','董广明','知名学者']
(6 rows)

 

通过limit和offset控制返回的数据行

csdn=# select * from articles order by  updated_at desc limit 2;
 id |          created_at           |          updated_at           |          deleted_at           |  title   |     content      |         ca
tegory          |           tag            |          author          
----+-------------------------------+-------------------------------+-------------------------------+----------+------------------+-----------
----------------+--------------------------+--------------------------
  6 | 2020-07-16 13:42:20.291723+08 | 2020-07-16 13:42:20.291723+08 | 2020-07-16 13:46:40.626837+08 | 测试删除 | 测试删除         | ['golang',
'postgrxyesql'] | ['golang','go']          | ['dongguangming', 'dgm']
  5 | 2020-07-16 13:09:47.795349+08 | 2020-07-16 13:09:47.795349+08 |                               | 我在江宁 | 江宁地广，路很宽 | ['南京','
宁']           | ['南京','百家湖','购物'] | ['dmg']
(2 rows)

csdn=# select * from articles order by  updated_at desc limit 2 offset 0;
 id |          created_at           |          updated_at           |          deleted_at           |  title   |     content      |         ca
tegory          |           tag            |          author          
----+-------------------------------+-------------------------------+-------------------------------+----------+------------------+-----------
----------------+--------------------------+--------------------------
  6 | 2020-07-16 13:42:20.291723+08 | 2020-07-16 13:42:20.291723+08 | 2020-07-16 13:46:40.626837+08 | 测试删除 | 测试删除         | ['golang',
'postgrxyesql'] | ['golang','go']          | ['dongguangming', 'dgm']
  5 | 2020-07-16 13:09:47.795349+08 | 2020-07-16 13:09:47.795349+08 |                               | 我在江宁 | 江宁地广，路很宽 | ['南京','
宁']           | ['南京','百家湖','购物'] | ['dmg']
(2 rows)

csdn=# select * from articles order by  updated_at desc limit 2 offset 2;
 id |          created_at           |          updated_at           | deleted_at |     title      |         content          |    category    
 |          tag           |      author      
----+-------------------------------+-------------------------------+------------+----------------+--------------------------+----------------
-+------------------------+------------------
  4 | 2020-07-16 13:08:35.71687+08  | 2020-07-16 13:08:35.71687+08  |            | 新街口         | 新街口商场很多，东西也贵 | ['南京','金陵']
 | ['南京','金陵','购物'] | ['dmg','董广明']
  3 | 2020-07-16 13:07:18.856783+08 | 2020-07-16 13:07:18.856783+08 |            | 南京总统府咋样 | 南京总统府很好玩         | ['南京','金陵']
 | ['南京','金陵','旅游'] | ['董广明']
(2 rows)

csdn=# select * from articles order by  updated_at desc limit 2 offset 4;
 id |          created_at           |          updated_at           | deleted_at |                           title                           |
                                               content                                                |         category          |           
  tag              |                    author                    
----+-------------------------------+-------------------------------+------------+-----------------------------------------------------------+
------------------------------------------------------------------------------------------------------+---------------------------+-----------
-------------------+----------------------------------------------
  2 | 2020-07-16 13:05:56.92499+08  | 2020-07-16 13:05:56.92499+08  |            | Java开发RESTful API                                       |
 java开发接口开发过程                                                                                 | ['java','postgresql']     | ['java','c
ode','postgresql'] | ['dongguangming', '董广明']
  1 | 2020-07-16 12:57:17.621445+08 | 2020-07-16 12:57:17.621445+08 |            | Golang学习系列第五天： Golang和PostgreSQL开发 RESTful API |
 记录接口开发过程，出了问题，直接看博客，不，需要再查其他网文，节省时间干其他事，时间就是毛爷爷！！！ | ['golang','postgrxyesql'] | ['golang',
'go','postgresql'] | ['dongguangming', 'dgm','董广明','知名学者']
(2 rows)

csdn=# select * from articles order by  updated_at desc limit 2 offset 6;
 id | created_at | updated_at | deleted_at | title | content | category | tag | author 
----+------------+------------+------------+-------+---------+----------+-----+--------
(0 rows)

csdn=# 

 注：limit可以理解为一页显示多少条数据，而offset表示偏移量，表示从哪行开始计数

 

参考：

1. PostgreSQL LIMIT and OFFSET https://www.postgresql.org/docs/current/queries-limit.html

2. Five ways to paginate in Postgres, from the basic to the exotic https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/

3. Pagination Done the PostgreSQL Way https://leopard.in.ua/2014/10/11/postgresql-paginattion#.XxnYv-_CwXd

4. How to Paginate in SQL - PostgreSQL https://rabbitoncode.com/big-data/2019/10/10/pagination-postgres/

5. LIMIT,  OFFSET, ORDER BY and Pagination in PostgreSQL https://technobytz.com/limit-offset-order-by-and-pagination-in-postgresql.html

6. SQL Pagination with LIMIT and OFFSET https://www.petefreitag.com/item/451.cfm

7. How To Do Pagination in Postgres with Golang in 4 Common Ways  https://medium.com/easyread/how-to-do-pagination-in-postgres-with-golang-in-4-common-ways-12365b9fb528

8. Faster SQL Pagination with jOOQ Using the Seek Method https://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-using-the-seek-method/

9. REST API Design: Filtering, Sorting, and Pagination https://www.moesif.com/blog/technical/api-design/REST-API-Design-Filtering-Sorting-and-Pagination/

10. Best practices for REST API design https://stackoverflow.blog/2020/03/02/best-practices-for-rest-api-design/