一、Oracle 的分页
Oracle 的表,除了建表时设计的各个字段,其实还有两个字段(此处只介绍 2 个),分别是 ROWID(行标示符)和 ROWNUM(行号)。即使使用 DESCRIBE命令查看表的结构,也无法看到这两个列的描述,因为它们只在数据库内部使用,所以也通常称它们为伪列(pseudo column)。建一个只有两个字段(id,col)的表。使用 describe 命令查看表结构,可以看到确实只有建表时的两个字段。但查询的时候,可以查找到伪列的值。
select rowid,rownum,id,col from table;
这个 rowid 一般用不到,Oracle 数据库内部使用它来存储行的物理位置,是一个 18 位的数字,采用 base-64 编码。而这个 rownum,正是用来进行分页查询的,它的值,就是表示的该行的行号。
对于分页查询,只要想办法查询从某一起始行到终止行就可以的,分页的逻辑可以放到程序里面。于是,理所当然会想到如下语句查询第 2 页的数据(每页 2 条数据,页码从 1 开始,所以起始行的行号为(页码-1)*每页长度+1=3
,终止行的行号为页码*每页长度=4
:
select * from table where rownum>=3 rownum <= 4;
出人意料,没有任何结果。原因很简单,Oracle 机制就是这样的:因为第一条数据行号为 1,不符合 >=3 的条件,所以第一行被去掉,之前的第二行变为新的第一行(即该行号不是写死的,可以理解为是动态的),如此下去,一直到最后一行,条件始终没法满足,所以就一条数据也查不出来。
对症下药,要想解决这个问题,只要将行号查询出来生成一个结果集,然后再从这个结果集中,选择行号大于设定的那个值就可以了,上面的分页查找正确的写法应该是这样:
select id,col
from
(select rownum rn,u.* from table u) ua
where
ua.rn between 3 and 4;
上面的语句还可以优化:虽然不能用“>=”但“<=”却可以用。为提高查询效率,可以使用终止行筛选子查询的结果,SQL 如下:
select id,col
from
(select rownum rn,u.* from table u where rownum<=4) ua
where
ua.rn >= 3;
很多时候,并不是盲目分页查找,而是按某一个或多个字段的升序或降序分页,即包含 order by 语句的分页查询,先看一下 order by 的查询结果中 rownum 是怎样的:
select rownum,id,col from table order by col;
结果,此时的行号并不是经过 order by 后结果的增序行号。
但有了上面的嵌套查询的经验,这里也可以好好应用一下,怎么做呢:先查找出排序好的结果集,然后应用上面的方法得到最终结果,sql 如下:
SELECT
id,col
FROM
( SELECT rownum rn, uo.* FROM ( SELECT * FROM TABLE ORDER BY col ) uo WHERE rownum <= 4 ) ua
WHERE
ua.rn >= 3;
二、分页效果的实现,思路有三种
-
纯 JS 实现分页。一次性查询记录并加载到 html 的 table 中。然后通过选择性地显示某些行来达到分页显示的目的。这是一种伪分页,障眼法而已。只能用于数据少的情况下。一旦数据多了,十几万条数据加载到 html 中会变得很慢。而且不实时,一次加载完后数据就写死在页面了,若数据库中有变化,浏览器端显示的仍是上次加载过来的数据。
-
一次查询,分批显示。
就是说,可以执行一个数据库查询操作,得到结果集 rs。然后,通过指针的移动来显示当前页面的记录。这样,就可以以 rs.absolute(当前页面号*每页记录数)定位到当前页的第一条记录,然后通过 while 循环显示 n 条记录(n 为每页显示记录数)。在跳页时,只需修改 currentPage,即可在重定位到下一页时把当前页面号改掉,重新定位记录指针,通过 while 遍历显示 n 条记录。与 JS 选择性显示不同,这里是选择性遍历。与 JS 分页不同的是,这里分页每次跳页修改的是遍历的指针,每次跳页都要进行一次全面查询。同样地,不适合大数据量查询。这里比 JS 分页优化的地方在于——实时性。每次跳页都会查询一次数据库,保证数据的实时性。 -
在服务端分页。跳到第 n 页才查询、显示第 n 页内容。要点就是根据客户端表格的“页面”计算出数据库要查询的当前页面的第一条记录的位置。优点:实时性:跳页才查询。数据量小:只加载当前页的记录进行显示。