如何打开数据库的分页（四类数据库分页实现方案总结之PG分页实现）
如何打开数据库的分页（四类数据库分页实现方案总结之PG分页实现）select * from table where 自增列 not in (select 自增列 from table ORDER BY 自增列 asc limit 页码 * 显示数) ORDER BY 自增列 asc limit 显示数示例：select * from read_sight_order_log where id in ( select id from read_sight_order_log ORDER BY id desc limit (select count(*) - (0*10) from read_sight_order_log)) ORDER BY id asc limit 10B就是查询的起点位置。基于上面语法介绍PG的5种分页实现方案，如下：select * from table where 自增列 in (select 自增列
概述前面介绍了Oracle、mysql、sqlserver的分页实现方案了，所以今天主要介绍一下关于PG的分页实现。传统的精准分页(能获取到总页数并且任意跳转页码)在需要符合条件的记录总数目 不过pg在这个地方是一个大坑 pg的count查询性能不行(目测不如mysql sqlserver mongodb)。
pg官网wiki给了个count estimate(https://wiki.postgresql.org/wiki/Count_estimate)的解决方案 有兴趣的朋友也可以后面了解一下，下面先看一下PG的limit实现。
PG limit 语法
select * from table limit A offset B;
参数：
A就是需要多少行；
B就是查询的起点位置。
基于上面语法介绍PG的5种分页实现方案，如下：
1、方案1
select *
from table
where 自增列 in (select 自增列
from 表
ORDER BY 自增列 desc limit (select count(*) - (页码 * 显示数)
from table))
ORDER BY 自增列 asc limit 显示数;
示例：
select * from read_sight_order_log where id in ( select id from read_sight_order_log ORDER BY id desc limit (select count(*) - (0*10) from read_sight_order_log)) ORDER BY id asc limit 10
2、方案2
select *
from table
where 自增列 not in
(select 自增列 from table ORDER BY 自增列 asc limit 页码 * 显示数)
ORDER BY 自增列 asc limit 显示数
示例：
select * from read_sight_order_log where id not in (select id from read_sight_order_log ORDER BY id asc limit 0 * 10) ORDER BY id asc limit 10
3、方案3
select * from table order by 排序列 asc LIMIT 显示数 offset (页码-1) * 显示数
示例：
select * from read_sight_order_log order by id asc LIMIT 10 offset 0*10
4、方案4
select *
from table
where 自增列 BETWEEN 页码 * 显示数 and (1 页码) * 显示数
示例：
select * from read_sight_order_log where id BETWEEN 0 * 10 and (1 0 )*10
5、方案5
select *
from (select * row_number() over(order by 列 asc) as rownumber from table) t
where t.rownumber > 页码*显示数
and t.rownumber <= (1 页码)*显示数
示例：
select * from (select * row_number() over(order by id asc) as idx from read_sight_order_log)t where t.idx>0 * 10 and t.idx<= (1 0)*10
6、实例演示
6.1、环境准备
$ psql -h 172.26.151.107 -p 5432 FSL-VIS postgres
\dn --list schemas
set search_path to 'fslvis_schema';
\dt --list tables
CREATE TABLE t(
EMPNO INT PRIMARY KEY NOT NULL
ENAME CHAR(10) NOT NULL
JOB CHAR(10) NOT NULL
MGR INT
HIREDATE date
SAL INT
COMM INT
DEPTNO INT
);
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');
6.2、limit分页实现
select * from t
where empno in (select empno from t ORDER BY empno desc limit (select count(*) - (1 * 5) from t))
ORDER BY empno desc limit 5;
--或者以下
select * from t order by empno desc LIMIT 5 offset 0 * 5;
觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~
