MySQL偏移无限行

Question

我想构建一个查询，将所有结果显示在表中，但从表的开头偏移5行。据我所知，MySQL的LIMIT需要限制和偏移量。是否有任何方法可以实现此目的？

- Teegijee

---

这是一个完全合理的问题，但我想知道是否更好的做法是通过编程获取所有内容并忽略前几条记录。考虑到似乎最佳答案（限制5、18446744073709551615）的可怕情况，我更倾向于解决MySQL LIMIT的限制。 - cesoid

如果你想使用limit 5000, 18446744073709551615，那么你不需要为了让代码看起来漂亮而多获取5000行。 - elipoultorak

@user3576887 我认为你是对的，我之前考虑这个问题时假设只需要 5，而没有考虑可能需要更大数量的变化（也没有考虑解决别人的问题）。 - cesoid

我建议这是一个非常罕见的任务，解决方案的丑陋可以被接受。 - Rick James

Answer 1

根据MySQL手册中的LIMIT部分：

如果要检索从某个偏移量到结果集末尾的所有行，可以为第二个参数使用一些大数。以下语句将返回从第96行到最后一行的所有行：

SELECT * FROM tbl LIMIT 95, 18446744073709551615;

- RoBorg

---

糟糕！我来这里是希望MySQL可以让Limit子句可选，同时提供偏移量...但没有！我看到代码中到处都是18446744073709551615，一直以为是懒惰的程序员的锅，结果原来是设计特性！ - Petruza

对于那些想知道的人来说，18446744073709551615是2的64次方减1。需要注意的是，您无法将此值存储在32位整数中。您必须将其存储为字符串以确保兼容性。 - AlicanC

太糟糕了！他们需要变得更加优雅一些... Limit-1或者Limit Null看起来相当合理！或者至少，Limit应该接受一个子查询，像这样：select * from table limit (select count(*) from table)。 - vulcan raven

使用PHP中的'PHP_INT_MAX'来避免溢出效应。 - Karl Adler

我认为这属于我刚刚想出来的 TODINAWYW 类别，它代表着“官方文档并不总是你所需要的”。它解释了如果你拥有一个最大行数约为 18 万亿行的表，你将如何获得所有记录，并建议使用“一些大数字”作为替代方案，但我建议您应该使用有意义、非无限和非 18 万亿条记录的数量。 （我在我的答案中包含了这一点。） - cesoid

MySQL似乎对18446744073709551615有一些特殊处理。例如，在安全模式下，它不被接受作为DELETE子句的LIMIT。然而，18446744073709551614是可以接受的。 - stannius

Answer 2

根据您提到的需要使用LIMIT，因此您需要使用最大可能的限制，即18446744073709551615（无符号 BIGINT 的最大值）。

SELECT * FROM somewhere LIMIT 18446744073709551610 OFFSET 5

- Czimi

---

哇，这是 MySQL 团队的官方解决方案吗？ - Antony

我更喜欢MySQL而不是PostgreSQL。Google甚至更喜欢它（MariaDB），所以... - Spock

@Spock 为什么有人会更喜欢 MySQL 而不是 PostgreSQL？ - Rodrigo

@Rodrigo 我认为MySQL更适合用于网站和在线交易以及简单的结构，而PostgreSQL更适合用于大型和复杂的分析过程（对象等）。最终，这取决于个人偏好和经验。 - Spock

@Spock 最便宜的网站服务器可以很好地处理PostgreSQL，因此我认为使用它没有任何额外开销。那么，为什么要使用两种不同的“语言”，当一种就足够了呢？除非，也许你正在做嵌入式数据库，但在这种情况下，你将需要像SQLite这样的东西。 - Rodrigo

我将它与多种数据库一起使用 - 对于实时数据，我使用Firebase。但我一定会更仔细地看看PostgresSQL，谢谢。我只是太熟悉MySQL了，所以它是我的首选。 - Spock

Answer 3

正如其他答案中所指出的，MySQL建议将18446744073709551615作为限制记录数的数字，但请考虑：如果你返回了18,446,744,073,709,551,615条记录，你该怎么办？事实上，如果你返回了1,000,000,000条记录，你该怎么办？

也许你确实想要超过十亿条记录，但我的观点是有一定的数量限制，并且它肯定小于18万亿。为了稳定性、优化和可能的可用性，我建议在查询中设置一些有意义的限制。这也会减少任何从未见过这个神奇数字的人的困惑，并有额外的好处，可以至少说明您愿意一次处理多少条记录。

如果你真的必须从你的数据库中获取所有的18万亿条记录，也许你真正想要的是以每1亿条记录为增量进行抓取并循环1840亿次。

- cesoid

---

你说得没错，但是把这个决定留给开发者并不是一个好选择。 - amd

@amd 你能再解释一下吗？我不知道你想说什么。 - cesoid

@cesoid 我认为他的意思是开发人员不应该随意选择业务逻辑，我同意这一点，但只有到一定程度。比如说你要向客户返回订单列表。每次最多返回100万个是完全合理的，但限制在100个可能会引起混淆。 - Autumn Leonard

@amd 我并不是说开发者应该改变应用程序的行为，以避免使用18446744073709551615。我是说他们应该考虑是否使用这个数字作为客户或界面设计师所要求的实现的一部分是有意义的，并且它很可能不是任何东西的正确实现。使用MySQL的决定可能已经由开发者做出，而没有询问是否会有超过18万亿的某些东西。 - cesoid

我的看法是...你已经可以轻松地获取所有记录，而不必使用那个神奇的18万亿数字...考虑一下这个查询- select * from table。那么问题来了，如果你为该查询获得了18万亿条记录，你会怎么做呢？难道我们要修改所有基本查询，像这样加上一个限制 select * from table limit 0, 1000000，以避免获取太多的记录吗？我只是认为这是MySQL设计上的缺陷，特别是其他数据库系统如PostgreSQL可以有像这样的查询 select * from myTable offset 10，我经常使用。 - Ray Perea

@RayPerea 我同意MySQL应该设计成具有没有上限的偏移量，而且大多数情况下，您可能不需要以那种方式限制查询。我只是认为，解决这个缺点的最佳选择是相当好的实践，即使您唯一的安全猜测是将其设置得非常高，例如1,000,000,000,000，也至少要考虑一些上限。这很烦人，但其他选项似乎更糟。 - cesoid

Answer 4

另一种方法是选择一个自增列，然后使用HAVING进行筛选。

SET @a := 0; 
select @a:=@a + 1 AS counter, table.* FROM table 
HAVING counter > 4

但我可能会坚持采用高限制的方法。

- jishi

---

谢谢，我想知道如何在PHP语句中放置这样的查询！我的意思是像这样 $sql = 'SET @a :=0 SELECT .....'; - Reham Fahmy

Answer 5

正如其他人提到的，可以从MySQL手册中了解到。为了实现这个目标，你可以使用一个无符号大整数的最大值，即这个可怕的数字（18446744073709551615）。但是要使它变得更加简洁，你可以使用波浪线“~”按位运算符。

  LIMIT 95, ~0

它作为位非运算符。"~0"的结果是18446744073709551615。

- Bruno.S

---

在MariaDB 10.3中不起作用 :( 我尝试了LIMIT 5, ~0和LIMIT ~0 OFFSET 5两种方式。这是MySQL 8.0的一个特性吗？ - jurchiks

MySQL 5.7 中不存在此功能——语法无效。 - Jonny Nott

“~0” 不是可读性的提高，而是对值真正含义的混淆。 - Sámal Rasmussen

Answer 6

你可以使用带有 LIMIT 的 MySQL 语句：

START TRANSACTION;
SET @my_offset = 5;
SET @rows = (SELECT COUNT(*) FROM my_table);
PREPARE statement FROM 'SELECT * FROM my_table LIMIT ? OFFSET ?';
EXECUTE statement USING @rows, @my_offset;
COMMIT;

在MySQL 5.5.44中进行了测试。因此，我们可以避免插入数字18446744073709551615。

注意：事务确保变量@rows与执行语句时考虑的表一致。

- sissi_luaty

---

正如 @amd 所述：“在一个拥有 700 万条记录的表上选择 count(*) 大约需要 17 秒”。 - Rodrigo García

Answer 7

在练习LC#1321时，我遇到了一个非常相似的问题，即我必须选择所有日期，但前6个日期被跳过。

我在MySQL中使用ROW_NUMBER()窗口函数和子查询来实现这一点。例如，以下查询返回跳过前五行的所有结果：

SELECT
    fieldname1,
    fieldname2
FROM(
    SELECT
        *,
        ROW_NUMBER() OVER() row_num
    FROM
        mytable
) tmp
WHERE
    row_num > 5;

你可能需要在子查询中添加一些逻辑，特别是在 OVER() 中以适应你的需求。此外，根据你的实际偏移逻辑，可以使用 RANK()/DENSE_RANK() 窗口函数代替 ROW_NUMBER()。

参考资料：

MySQL 8.0 参考手册 - ROW_NUMBER()

- fishstick

Answer 8

今天我正在阅读有关从mysql表中获取大量数据（超过一百万行）的最佳方法。一种方法是使用LIMIT x，y，其中x是偏移量，y是您想要返回的最后一行。然而，正如我发现的那样，这不是最有效的方法。如果您有一个自增列，您可以轻松地使用带有WHERE子句的SELECT语句来指定您想要开始的记录。

例如， SELECT * FROM table_name WHERE id > x; 似乎当您使用LIMIT时，mysql会获取所有结果，然后仅显示符合偏移量的记录：性能并不是最好的。

来源：对此问题的回答MySQL论坛。请注意，该问题已经存在6年了。

- fed

---

如果您曾经删除过记录，使用此方法会产生不正确的结果。该方法特别危险，因为大多数情况下它可行，但当它失败时会默默地失败。 - octern

Answer 9

我知道这个问题有点老了，但是我没有看到类似的回答，所以这是我会使用的解决方案。

首先，我会在表上执行一个计数查询，以查看有多少条记录存在。这个查询很快，通常执行时间可以忽略不计。例如：

SELECT COUNT(*) FROM table_name;

接下来，我将使用从计数中获取的结果作为我的限制来构建查询（因为这是表可能返回的最大行数）。就像这样：

SELECT * FROM table_name LIMIT count_result OFFSET desired_offset;

或者可能是这样的：

SELECT * FROM table_name LIMIT desired_offset, count_result;

当然，如果需要的话，您可以从count_result中减去desired_offset以获得实际的、准确的值作为限制条件。如果我能够确定一个合适的限制条件，那么传递“18446744073709551610”这个值就没有意义了。

- Baron Von Sparklefarts

---

在一个有700万条记录的表上执行select count(*)语句大约需要17秒。 - amd

Answer 10

WHERE .... AND id > <YOUROFFSET>

id 可以是任何自增或唯一数字列...

- user3131125

---

不好的想法。如果你曾经删除过一行，它会给出不正确的偏移量。 - octern