数据库分页的技巧和坑：SQL Server&MySQL

一直感觉数据库分页是一个很专业的术语，其实就是为了查看数据快一些，方便一些，而将数据库中的数据分批查询，同时也可以理解为在前端展示的时候，数据太多，无法全部展示，只能分成好几个页面展示。

其实分页查询的实现不只是以下几种，下面只是自己容易记的方法，但是针对特定的场景性能可能不是最好的，因此需要多加考虑。

一、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;

---

相关文章：

• 分页查询的那些坑和各种技巧