概述
前面介绍了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 postgresdn --list schemasset search_path to 'fslvis_schema'; dt --list tablesCREATE 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方面的内容,感兴趣的朋友可以关注下~