一直感觉数据库分页是一个很专业的术语,其实就是为了查看数据快一些,方便一些,而将数据库中的数据分批查询,同时也可以理解为在前端展示的时候,数据太多,无法全部展示,只能分成好几个页面展示。
其实分页查询的实现不只是以下几种,下面只是自己容易记的方法,但是针对特定的场景性能可能不是最好的,因此需要多加考虑。
一、SQL Server中的分页
在SQL Server中,可以通过主键和使用子查询完成分页查询,但是SQL Server 2005中新出的一个函数,可以更方便的进行分页查询,就是row_number()
,它可以返回一个查询结果集的序列号,从1开始,但是它只能在select后面使用,而无法在where语句后面使用,因此无法直接使用它作为查询条件,例如下面的两条语句是错误的:
- 报错“Invalid column name ‘rownum’”
SELECT ROW_NUMBER() OVER ( ORDER BY Id DESC ) AS rownum ,* FROM World WHERE rownum > 10;
- 报错“Windowed functions can only appear in the SELECT or ORDER BY clauses.”
SELECT * FROM World WHERE ROW_NUMBER() OVER ( ORDER BY Id DESC ) > 10;
但是可以通过子查询绕过这种限制,如下:
SELECT * from (select *, ROW_NUMBER() OVER ( ORDER BY Id DESC ) AS rownum) a WHERE a.rownum > 10
所以我们的分页查询如下:
DECLARE @PageSize INT
DECLARE @PageNum INT
DECLARE @Sql NVARCHAR(200)
SET @PageSize=10
SET @PageNum=2
SET @strSql='SELECT TOP '+cast(@PageSize AS NVARCHAR(10)) +'* FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY ID) AS num FROM 目标表) as t WHERE t.num>'+CAST((@PageNum-1)*@PageSize AS NVARCHAR(1000))
exec sp_executesql @strSql
二、MySQL中的分页
MySQL
中的分页相对SQL Server
来说就简单多了,因为它有现成的语句limit用来分页,它可以接收一个或者两个参数:
接收两个参数时
SELECT * FROM world.city LIMIT 5,10;
第一个参数是相对于结果集中第一行的偏移量,这个偏移量是从0开始的,也就是说第一行的偏移量是0,而不是1,第二个参数指的是返回结果的最大行数,即如果不够该数目,则返回实际的数量。
接收一个参数时
指定返回的行数,相对于第一行,比如:
SELECT * FROM world.city LIMIT 10;
效果等于:
SELECT * FROM world.city LIMIT 0,10;
在使用的过程中注意:
limit后面只能使用非负、整型的常数:也就是说后面只能是1,100等整型数字,而一些表达式也是不允许的,比如100+200等等;
在子查询中,外部的查询中使用的limit的优先级要高于子查询中limit,比如:
(SELECT * FROM world.city LIMIT 3) LIMIT 5;
最后返回5行。
由于limit后面只能使用非负、整型常数,因此无法使用变量,如下分页语句会错误:
set @pageSize = 10;
set @pageIndex = 2;
select * from city LIMIT (@pageIndex-1)*@pageSize, @pageSize;
但是limit在预编译语句中可以使用?作为占位符来传递参数,但是在使用预编译语句的时候如下的语句都是错误的:
- 传递参数变量表达式:
set @pageSize = 10;
set @pageIndex = 2;
PREPARE stmt FROM 'select * from city LIMIT ?, ?';
EXECUTE stmt USING (@pageIndex-1)*@pageSize, @pageSize;
- 在limit后面使用表达式:
set @pageSize = 10;
set @pageIndex = 2;
PREPARE stmt FROM 'select * from city LIMIT (?-1)*?, ?';
EXECUTE stmt USING @pageIndex, @pageSize, @pageSize;
正确的预编译分页方法如下:
set @pageSize = 10;
set @pageIndex = 2;
set @off = (@pageIndex-1)*@pageSize;
PREPARE stmt FROM 'select * from city LIMIT ?, ?';
EXECUTE stmt USING @off, @pageSize;
另外也可以使用存储过程来完成分页的查询,如下:
DROP PROCEDURE IF EXISTS Page_Query;
CREATE PROCEDURE Page_Query(pageIndex int, pageSize int)
BEGIN
DECLARE off int;
SET off = (pageIndex-1)*pageSize;
SELECT * FROM world.city LIMIT off, pageSize;
END
CALL Page_Query(2, 10);
但是在其中仍需要一个中间变量来存储偏移量,而如下的语句则是错的:
DROP PROCEDURE IF EXISTS Page_Query;
CREATE PROCEDURE Page_Query(pageIndex int, pageSize int)
BEGIN
-- DECLARE off int;
-- SET off = (pageIndex-1)*pageSize;
SELECT * FROM world.city LIMIT (pageIndex-1)*pageSize, pageSize;
END
在分页的过程中,如果遇到的数据量很大还会遇到性能问题,尤其是如下语句:
SELECT * FROM world.city LIMIT 100000, 20;
总共扫描了100020行,最后只返回20行,就好比去买东西,让人找找总共有多少商品,但是最后只要一件,把人累死、气死,因此如果每次我们把上次查询的偏移量记下在进行查询则会快很多:
SELECT * FROM world.city where Id > 100000 LIMIT 20;
相关文章: