MySQL中SQL分页查询的几种实现方法及优缺点

【SQL】SQL分页查询总结

开发过程中经常遇到分页的需求，今天在此总结一下吧。

简单说来方法有两种，一种在源上控制，一种在端上控制。源上控制把分页逻辑放在SQL层；端上控制一次性获取所有数据，把分页逻辑放在UI上（如GridView）。显然，端上控制开发难度低，适于小规模数据，但数据量增大时性能和IO消耗无法接受；源上控制在性能和开发难度上较为平衡，适应大多数业务场景；除此之外，还可以根据客观情况（性能要求，源与端的资源占用等）在源和端之间加一层，应用特殊算法和技术进行处理。以下主要讨论源上，即SQL上的分页。

分页的问题其实就是在满足条件的一堆有序数据中截取当前所需要展示的那部分。实际上各种数据库都考虑到分页问题而内置了一些策略，比如MySql的LIMIT，Oracle的ROWNUM和ROW_NUMBER()，SqlServer的TOP和ROW_NUMBER()，基于此我们可以得到一系列分页的方法。

1、基于MySql的LIMIT和Oracle的ROWNUM，可以直接限制返回区间（以MySql为例，注意使用Oracle的ROWNUM时要应用子查询）：

方法一、直接限制返回区间

SELECT * FROM table WHERE 查询条件 ORDER BY 排序条件 LIMIT ((页码-1)*页大小),页大小;

2、基于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 排序条件

方法三、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

3、基于SqlServer和Oracle的ROW_NUMBER()，可以得到返回数据的行号，基于此在限制返回区间得到如下方法（以SqlServer为例）：

方法四、ROW_NUMBER()

SELECT TOP 页大小 * FROM ( SELECT TOP (页码*页大小) ROW_NUMBER() OVER (ORDER BY 排序条件) AS RowNum, * FROM table WHERE 查询条件) AS tempTableWHERE RowNum BETWEEN (页码-1)*页大小+1 AND 页码*页大小ORDER BY RowNum

更多相关文章

随机推荐