1. 什么是分页查询
在实际应用中,我们常常需要对大量的数据进行查询和展示,但是一次查询返回的所有数据可能会导致页面加载缓慢,因此需要将数据分页显示。分页查询就是从大量数据中提取固定数量的数据,按照页面的需求返回给用户所需的数据。分页查询的优点是可以节省网络带宽和页面加载时间。
分页查询的常见应用场景:
电商网站对商品列表的分页展示
新闻网站对新闻列表的分页展示
后台管理系统对用户列表的分页展示
2. 分页查询的常见写法
2.1. LIMIT 分页
SELECT * FROM table LIMIT offset, limit;
LIMIT
关键字用于限制查询结果返回的行数。它可以接受两个参数,第一个参数是偏移量(即从哪一行开始返回),第二个参数是返回行数。
示例:
SELECT * FROM users LIMIT 0, 10; --返回 users 表中的前10条记录
上述语句会从users
表的第一行(offset=0
)开始,返回10行记录。
2.2. OFFSET 分页
SELECT * FROM table OFFSET offset ROWS FETCH NEXT limit ROWS ONLY;
OFFSET
关键字用于跳过查询结果集中的第n行。基于OFFSET分页的查询需要为每次的查询指定具体的起始位置和查询的行数,因此比较适合于下拉式分页的实现方式。
示例:
SELECT * FROM users ORDER BY age OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; --返回 users 表中的第11至20条记录
上述语句会按照age
字段升序排序,从users
表的第11行开始(offset=10
),返回10行记录。
2.3. ROW_NUMBER() 分页
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY column DESC) as rowNumber FROM table
) tmp WHERE rowNumber > offset AND rowNumber <= offset + limit;
ROW_NUMBER()
是一种窗口函数,它可以给查询结果集中的每一行增加一个行号。ROW_NUMBER() 的语法如下:
ROW_NUMBER() OVER ([PARTITION BY column1, column2,...] ORDER BY column) AS RowNumber
使用ROW_NUMBER()函数进行分页时,首先需要为查询语句增加一个ROW_NUMBER()列,然后再以ROW_NUMBER()列作为过滤条件,筛选出所需的行数。
示例:
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY age DESC) as rowNumber FROM users
) tmp WHERE rowNumber > 10 AND rowNumber <= 20; --返回 users 表中的第11至20条记录
上述语句中,ROW_NUMBER()函数按照age
字段降序排列,第一条记录的rowNumber为1,以此类推。然后从临时表中选择rowNumber在11和20之间的行,这样就可以返回第11至20条记录。
2.4. 总结
以上三种方法是SQL中常用的分页查询方法,它们各有优缺点,具体使用时需要根据实际情况选择。
LIMIT 分页简单直观,但是在查询大量数据时性能较差。
OFFSET 分页性能较好,但是需要指定具体的起始位置和查询行数。
ROW_NUMBER() 分页性能较好,但是需要使用窗口函数和子查询,语法较为复杂。
参考资料: