SQL分页写法

Oracle分页SQL

方式一：ROWNUM

SELECT * FROM (
	SELECT TEMP.*, ROWNUM ROW_NUM FROM (
		SELECT * FROM ${TABLE_NAME} WHERE <...> ORDER BY <COL>
	) TEMP WHERE ROWNUM <= <END>
) WHERE ROW_NUM > <START>;

注意：ROWNUM是在数据被选取后分配的，不能在WHERE子句中直接使用ROWNUM进行条件过滤，否则可能导致不符合预期的结果。如：

-- 这里的查询结果会是空,SELECT * FROM ${TABLE_NAME} WHERE ROWNUM > ?也会为空,但是小于某个值是支持的。
-- 需要用一个中间变量将其带出来才能使用,如:rownum row_num,将变量换名称
SELECT * FROM (
	SELECT * FROM ${TABLE_NAME} WHERE ROWNUM <= <END>
) WHERE ROWNUM > <START>;

方式二：OFFSET

Oracle12c及以上支持

-- ${START} 起始下标(1开始), ${PAGE_SIZE} 起始下标往后的行数
SELECT *
FROM ${TABLE_NAME}
ORDER BY ID
OFFSET ${
  START} ROWS FETCH NEXT ${PAGE_SIZE} ROWS ONLY;

大分页查询

-- 降序查询耗时: 4314ms
SELECT * FROM (
	SELECT temp.*,ROWNUM ROW_NUM FROM (SELECT ${TABLE_NAME}.* FROM ${TABLE_NAME} ORDER BY id desc) temp WHERE ROWNUM <= 1000010
) WHERE ROW_NUM > 1000000;

-- 降序查询耗时: 895ms,对应的结果不一定一样,因为ID不一定是连续增长的
SELECT * FROM (
	SELECT temp.*,ROWNUM ROW_NUM FROM (SELECT ${TABLE_NAME}.* FROM ${TABLE_NAME} WHERE ID < (SELECT MAX(ID) FROM ${TABLE_NAME} ) - 1000000 ORDER BY id desc) temp
) WHERE ROW_NUM <= 10;


-- 升序查询耗时: 3529ms
SELECT * FROM (
	SELECT temp.*,ROWNUM ROW_NUM FROM (SELECT ${TABLE_NAME}.* FROM ${TABLE_NAME} ORDER BY id) temp WHERE ROWNUM <= 1000010
) WHERE ROW_NUM > 1000000;

-- 升序查询耗时: 488ms,对应的结果不一定一样,因为ID不一定是连续增长的
SELECT * FROM (
	SELECT temp.*,ROWNUM ROW_NUM FROM (SELECT ${TABLE_NAME}.* FROM ${TABLE_NAME} WHERE ID > 1000000 ORDER BY id) temp
) WHERE ROW_NUM <= 10;

MySQL分页SQL

SELECT * FROM ${TABLE_NAME}
ORDER BY ID
LIMIT ${
  START}, ${PAGE_SIZE};