MySQL 限制和偏移示例

LIMIT 关键字是什么？ MySQL?

此 极限 关键字限制查询结果中返回的行数。它可以与 SELECT、UPDATE 和 DELETE 语句一起使用，因此它既限制了查询读取的行数，也限制了写入操作影响的行数。

LIMIT 关键字的语法如下。

SELECT {fieldname(s) | *} FROM tableName(s) [WHERE condition] LIMIT N;

点击这里

• “SELECT {字段名称| *} FROM 表名称” 是 选择语句 包含我们希望在查询中返回的字段。
• “[WHERE 条件]” 是可选的，但如果提供，则会指定对结果集的筛选条件。 WHERE 子句 在 LIMIT 之前应用，因此过滤先发生，上限应用于剩余部分。
• “限制 N” 是关键词，而且 N N 是从 0 开始的任意数字。将限制值设为 0 将不返回任何记录。将限制值设为 5 将返回五条记录。如果表中的记录数少于 N，则返回所有记录，且不会引发错误。

语法虽然简短，但在举例之前，有必要说明其存在的原因。

为什么要使用 LIMIT 关键字？

假设我们正在开发ping 该应用程序运行在 myflixdb 之上。系统设计人员要求我们将每页显示的记录数限制为 20 条，以解决加载速度慢的问题。我们该如何实现一个满足此要求的系统？

LIMIT 关键字正是为了处理这种情况而设计的。它不会将所有成员行都拉取到应用程序中并丢弃大部分记录，而是每页返回 20 条记录，由数据库完成后续处理。这样做有三个好处。

• 更快的响应： 从磁盘读取的数据量减少，通过网络传输的数据量也减少。
• 降低内存占用： 该应用程序只显示一页行，而不是整个表格。
• Safer写道： 更新限制或 删除 这条语句限制了错误可能影响的行数。

MySQL LIMIT 查询示例

以下示例针对 myflixdb 数据库的 members 表运行。第一个示例返回两行数据，除此之外没有其他结果。

SELECT * FROM members LIMIT 2;

如上结果所示，只返回了两个成员。

从数据库中获取十 (10) 名成员的列表

假设我们想要获取 Myflix 数据库中前 10 位注册会员的列表。下面的脚本会请求这些会员。

SELECT * FROM members LIMIT 10;

执行脚本后，结果如下所示。

由于 LIMIT 子句中的 N 大于表中的记录数，因此只返回了 9 个成员。明确请求 9 行数据也会产生相同的结果集。

SELECT * FROM members LIMIT 9;

💡提示： LIMIT 函数会从服务器生成的任何顺序中选择行。添加一个 ORDER BY 每当需要确定行的顺序时，就必须使用这个条款，否则“前 10 名成员”就不能保证两次都是相同的 9 个人。

限制行数是该功能的第一部分。选择窗口起始位置是第二部分。

在 LIMIT 查询中使用 OFFSET 值

此 OFFSET 该值通常与 LIMIT 关键字一起使用。它指定服务器从哪一行开始检索数据，因此会跳过该行之前的行。

假设我们想要从表格中间开始选取有限数量的成员。下面的脚本从第二行开始，并将结果限制为两条记录。

SELECT * FROM `members` LIMIT 1, 2;

执行它 MySQL 工作台 与 myflixdb 数据库进行比对，得到以下结果。

请注意这里 偏移 = 1因此，第 2 行是返回的第一行，并且 限制 = 2因此，只返回 2 条记录。

在双参数形式中，偏移量写在前面，行数写在后面，很容易不小心弄反。 MySQL 它还接受一种明确的形式，消除了歧义，因此在新代码中应该优先选择这种形式。

SELECT * FROM `members` LIMIT 2 OFFSET 1;

两条语句都返回相同的两行数据。理解了偏移量之后，所有列表页面所依赖的分页模式就自然而然地显现出来了。

如何使用 LIMIT 和 OFFSET 对查询结果进行分页

分页将大型结果集分割成多个编号页面，而 LIMIT 和 OFFSET 参数正是实现分页的机制。每次分页请求都由两个值驱动：页面大小（即每页显示的记录数）和用户请求的页码。

偏移量由它们通过一个公式计算得出。

-- OFFSET = page_size * (page_number - 1)
SELECT membership_number, full_names
FROM members
ORDER BY membership_number ASC
LIMIT 20 OFFSET 0;   -- page 1

第 2 页保持相同的限制，并将偏移量向前移动一个页面大小。

SELECT membership_number, full_names
FROM members
ORDER BY membership_number ASC
LIMIT 20 OFFSET 20;  -- page 2

三个规则保证分页列表的正确性和快速性。

1. 始终排序： 不使用 ORDER BY 的分页查询可以将同一条记录显示在两个不同的页面上，并完全隐藏另一条记录，因为服务器可以在两次调用之间自由更改行的顺序。
2. 按唯一列排序： 如果排序列中出现并列行，则这些并列行的顺序将无法确定。按主键排序或将其添加为决胜因素可以解决此问题。
3. 观看深度页面： 偏移 100000 力 MySQL 读取十万行数据并将其丢弃，然后再返回接下来的二十行。响应时间随页码增加而增加。

对于非常深的页码，键集分页完全避免了偏移量。查询不再计算要跳过的行数，而是记住上一页的最后一个键，并请求其之后的行。

SELECT membership_number, full_names
FROM members
WHERE membership_number > 20      -- last id from the previous page
ORDER BY membership_number ASC
LIMIT 20;

这种形式的索引在任何深度都能保持快速，因为索引会直接跳转到起始键，而不是遍历它前面的行。缺点是页面必须按顺序遍历，所以需要进行跳转操作。ping 现在无法直接跳转到第 500 页。

限制 MySQL 对阵 TOP 和 FETCH FIRST

LIMIT 并不是每个 SQL 方言都包含的功能，这一点在查询需要在数据库引擎之间传递时非常重要。 MySQL, PostgreSQL和 SQLite 共享 LIMIT 关键字。SQL Server 使用 TOP，并且 Oracle 使用标准的 FETCH FIRST 子句。下表对这三种子句进行了比较。

每种情况下行为都相同：限制行数，并且可以选择先跳过若干行。只有拼写有所不同。因此，需要在多个引擎上运行的查询应该将限制行数的子句单独放在代码库中，而不是分散在整个代码库中。

常见问题