SQL Limit & Offset

1. 定義

慕課解釋： Limit用于對查詢結果集數量進行限制；Offset 則常與 Limit 搭配，意為偏移，表示跳過一定數量的記錄。

2. 前言

本小節，我們將一起學習 SQL 中的Limit和 Offset。

在實際的開發中，我們可能常常會遇到以下場景：

1. 獲得分數的前 5 名。
2. 分頁獲取數據。

這些場景需要細粒度地控制數據庫數據，因此 SQL 引入了 Limit 和 Offset 來幫助開發者。

本小節測試數據如下，請先在數據庫中執行：

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 一個指令就能實現 Limit + Offset 的全部功能，但一些數據庫為了保持彼此的兼容性，仍然提供了Offset。

3. Limit

Limit 后面可接受一個參數或者兩個參數；當接受一個參數count時，表示返回的結果集最多有 count 條。

語法如下：

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

其中col表示字段名，table_name表示數據表名稱，count代表限制數量。

3.1 例1 Limit 單參數使用

請書寫 SQL 語句，返回imooc_user表中年齡最大的3位用戶。

分析：

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

語句：

整理可得語句如下：

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

結果如下：

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

3.2 例2 Limit 雙參數使用

Limit 后面使用雙參數時，第一個參數表示偏移量，即 offset，表示從幾個記錄后開始返回，第二個參數表示最大數量，即 count，表示最多返回幾個記錄。我們合起來可以理解為，跳過一定數量（offset）的記錄，返回最多 count 個記錄。

語法如下：

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

其中col表示字段名，table_name表示數據表名稱，offset是偏移量，count是最大數量。

請書寫SQL語句，返回imooc_user表中年齡由大到小的第 2 位到第 4 位用戶。

分析：

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

語句：

整理可得語句如下：

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

結果如下：

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

第 2 位到第 4 位共 3 位用戶，因此 LIMIT 的第一個參數為 1，跳過第 1 個用戶，第二個參數為 3。

4. Offset

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

語法如下：

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

其中col表示字段名，table_name表示數據表名稱，offset是偏移量，count是最大數量。

4.1 例3 Limit 搭配 Offset 使用

請書寫 SQL 語句，返回imooc_user表中年齡由大到小的第 2 位到第 4 位用戶。

分析：

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

語句：

整理可得語句如下：

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

結果如下：

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

第 2 位到第 4 位共 3 位用戶，因此 offset 應是 1，跳過第 1 位用戶，count 應是 3。

5. 小結

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