SQL Limit & Offset

1. 定義

慕課解釋?zhuān)?Limit用于對(duì)查詢結(jié)果集數(shù)量進(jìn)行限制；Offset 則常與 Limit 搭配，意為偏移，表示跳過(guò)一定數(shù)量的記錄。

2. 前言

本小節(jié)，我們將一起學(xué)習(xí) SQL 中的Limit和 Offset。

在實(shí)際的開(kāi)發(fā)中，我們可能常常會(huì)遇到以下場(chǎng)景：

1. 獲得分?jǐn)?shù)的前 5 名。
2. 分頁(yè)獲取數(shù)據(jù)。

這些場(chǎng)景需要細(xì)粒度地控制數(shù)據(jù)庫(kù)數(shù)據(jù)，因此 SQL 引入了 Limit 和 Offset 來(lái)幫助開(kāi)發(fā)者。

本小節(jié)測(cè)試數(shù)據(jù)如下，請(qǐng)先在數(shù)據(jù)庫(kù)中執(zhí)行：

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int,
  score int
);
INSERT INTO imooc_user(id,username,age,score)
VALUES (1,'peter', 18, 100),(2,'pedro', 24, 500),
(3,'jerry', 24, 500),(4,'mike', 18, 100),(5,'tom', 20, 1000);

提示： 理論上 Limit 一個(gè)指令就能實(shí)現(xiàn) Limit + Offset 的全部功能，但一些數(shù)據(jù)庫(kù)為了保持彼此的兼容性，仍然提供了Offset。

3. Limit

Limit 后面可接受一個(gè)參數(shù)或者兩個(gè)參數(shù)；當(dāng)接受一個(gè)參數(shù)count時(shí)，表示返回的結(jié)果集最多有 count 條。

語(yǔ)法如下：

SELECT [col] FROM [table_name] LIMIT [count];

其中col表示字段名，table_name表示數(shù)據(jù)表名稱(chēng)，count代表限制數(shù)量。

3.1 例1 Limit 單參數(shù)使用

請(qǐng)書(shū)寫(xiě) SQL 語(yǔ)句，返回imooc_user表中年齡最大的3位用戶。

分析：

題干中指出只需返回 3 位用戶，因此需用到 Limit 限制數(shù)量；且年齡應(yīng)該為最大的 3 位，因此結(jié)果集應(yīng)按照年齡降序排序，即 ORDER BY age DESC。

語(yǔ)句：

整理可得語(yǔ)句如下：

SELECT username,age FROM imooc_user ORDER BY age DESC LIMIT 3;

結(jié)果如下：

+----------+-----+
| username | age |
+----------+-----+
| pedro    | 24  |
| jerry    | 24  |
| tom      | 20  |
+----------+-----+

3.2 例2 Limit 雙參數(shù)使用

Limit 后面使用雙參數(shù)時(shí)，第一個(gè)參數(shù)表示偏移量，即 offset，表示從幾個(gè)記錄后開(kāi)始返回，第二個(gè)參數(shù)表示最大數(shù)量，即 count，表示最多返回幾個(gè)記錄。我們合起來(lái)可以理解為，跳過(guò)一定數(shù)量（offset）的記錄，返回最多 count 個(gè)記錄。

語(yǔ)法如下：

SELECT [col] FROM [table_name] LIMIT [offset],[count];

其中col表示字段名，table_name表示數(shù)據(jù)表名稱(chēng)，offset是偏移量，count是最大數(shù)量。

請(qǐng)書(shū)寫(xiě)SQL語(yǔ)句，返回imooc_user表中年齡由大到小的第 2 位到第 4 位用戶。

分析：

題干中指出只需返回年齡的第 2 位到第 4 位用戶，因此需用到 Limit 限制數(shù)量和偏移；且年齡由大到小，因此結(jié)果集應(yīng)按照年齡降序排序，即 ORDER BY age DESC。

語(yǔ)句：

整理可得語(yǔ)句如下：

SELECT username,age FROM imooc_user ORDER BY age DESC LIMIT 1,3;

結(jié)果如下：

+----------+-----+
| username | age |
+----------+-----+
| jerry    | 24  |
| tom      | 20  |
| peter    | 18  |
+----------+-----+

第 2 位到第 4 位共 3 位用戶，因此 LIMIT 的第一個(gè)參數(shù)為 1，跳過(guò)第 1 個(gè)用戶，第二個(gè)參數(shù)為 3。

4. Offset

Offset 必須與 Limit 搭配使用，且二者都必須是單參數(shù)形式。Limit 和 Offset 搭配的作用等同于 Limit 使用雙參數(shù)。

語(yǔ)法如下：

SELECT [col] FROM [table_name] LIMIT [count] OFFSET [offset];

其中col表示字段名，table_name表示數(shù)據(jù)表名稱(chēng)，offset是偏移量，count是最大數(shù)量。

4.1 例3 Limit 搭配 Offset 使用

請(qǐng)書(shū)寫(xiě) SQL 語(yǔ)句，返回imooc_user表中年齡由大到小的第 2 位到第 4 位用戶。

分析：

題干中指出只需返回年齡的第 2 位到第 4 位用戶，因此需用到 Limit 限制數(shù)量，Offset 來(lái)表示偏移；且年齡由大到小，因此結(jié)果集應(yīng)按照年齡降序排序，即 ORDER BY age DESC。

語(yǔ)句：

整理可得語(yǔ)句如下：

SELECT username,age FROM imooc_user ORDER BY age DESC LIMIT 3 OFFSET 1;

結(jié)果如下：

+----------+-----+
| username | age |
+----------+-----+
| jerry    | 24  |
| tom      | 20  |
| peter    | 18  |
+----------+-----+

第 2 位到第 4 位共 3 位用戶，因此 offset 應(yīng)是 1，跳過(guò)第 1 位用戶，count 應(yīng)是 3。

5. 小結(jié)

• Limit 和 Offset 常搭配用于數(shù)據(jù)分頁(yè)，是很多分頁(yè)工具的核心操作。
• Limit 其實(shí)完全可以替代 Limit + Offset，但考慮到SQL語(yǔ)句的可讀性，Limit + Offset的寫(xiě)法更好。