数据库分页查询
基于MySql的LIMIT和Oracle的ROWNUM,可以直接限制返回区间(以MySql为例,注意使用Oracle的ROWNUM时要应用子查询):
第一种:limit。这是最简单的,也是最容易想到的一种方法。
SELECT * FROM table WHERE 查询条件 ORDER BY 排序条件 LIMIT ((页码-1)*页大小),页大小;
这种方法的优点是:优点是:写法简单。缺点是:当页码和页大小过大时,性能明显下降。适用于数据量不大的情况。
基于LIMIT(MySql)、ROWNUM(Oracle)和TOP(SqlServer),他们可以限制返回的行数,因此可以得到以下两套通用的方法(以SqlServer为例):
第二种:not in。
SELECT TOP 页大小 * FROM table WHERE 主键 NOT IN ( SELECT TOP (页码-1)*页大小 主键 FROM table WHERE 查询条件 ORDER BY 排序条件 ) ORDER BY 排序条件
优点:通用性强。
缺点:当数据量较大时向后翻页,NOT IN中的数据过大会影响性能。
适用:数据量不大。
第三种:max。
SELECT TOP 页大小 * FROM table WHERE 查询条件 AND id > ( SELECT ISNULL(MAX(id),0) FROM ( SELECT TOP ((页码-1)*页大小) id FROM table WHERE 查询条件 ORDER BY id ) AS tempTable ) ORDER BY id
优点:速度快,特别是当id为主键时。
缺点:适用面窄,要求排序条件单一且可比较。
适用:简单排序(特殊情况也可尝试转换成类似可比较值处理)。
基于SqlServer和Oracle的ROW_NUMBER(),可以得到返回数据的行号,基于此在限制返回区间得到如下方法(以SqlServer为例):
第四种:row_number()
SELECT TOP 页大小 * FROM ( SELECT TOP (页码*页大小) ROW_NUMBER() OVER (ORDER BY 排序条件) AS RowNum, * FROM table WHERE 查询条件 ) AS tempTable WHERE RowNum BETWEEN (页码-1)*页大小+1 AND 页码*页大小 ORDER BY RowNum
优点:在数据量较大时相比NOT IN有优势。
缺点:小数据量时不如NOT IN。
适用:大部分分页查询需求。