MySQL 偏移无限行

MySQL offset infinite rows

提问by stillinbeta

I would like to construct a query that displays all the results in a table, but is offset by 5 from the start of the table. As far as I can tell, MySQL's LIMITrequires a limit as well as an offset. Is there any way to do this?

我想构建一个查询，显示表中的所有结果，但从表的开头偏移 5。据我所知，MySQLLIMIT需要限制和偏移量。有没有办法做到这一点？

回答by Greg

From the MySQL Manual on LIMIT:

来自LIMIT 上的MySQL 手册：

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

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

SELECT * FROM tbl LIMIT 95, 18446744073709551615;

回答by Czimi

As you mentioned it LIMIT is required, so you need to use the biggest limit possible, which is 18446744073709551615 (maximum of unsigned BIGINT)

正如您提到的 LIMIT 是必需的，因此您需要使用可能的最大限制，即 18446744073709551615（无符号 BIGINT 的最大值）

SELECT * FROM somewhere LIMIT 18446744073709551610 OFFSET 5

回答by cesoid

As noted in other answers, MySQL suggests using 18446744073709551615 as the number of records in the limit, but consider this: What would you do if you got 18,446,744,073,709,551,615 records back? In fact, what would you do if you got 1,000,000,000 records?

正如其他答案中所述，MySQL 建议使用 18446744073709551615 作为限制中的记录数，但请考虑这一点：如果您恢复了 18,446,744,073,709,551,615 条记录，您会怎么做？事实上，如果你有 1,000,000,000 条记录，你会怎么做？

Maybe you do want more than one billion records, but my point is that there is some limit on the number you want, and it is less than 18 quintillion. For the sake of stability, optimization, and possibly usability, I would suggest putting some meaningful limit on the query. This would also reduce confusion for anyone who has never seen that magical looking number, and have the added benefit of communicating at least how many records you are willing to handle at once.

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

If you really must get all 18 quintillion records from your database, maybe what you really want is to grab them in increments of 100 million and loop 184 billion times.

如果您真的必须从数据库中获取所有 18 个 quintillion 记录，也许您真正想要的是以 1 亿为增量抓取它们并循环 1840 亿次。

回答by jishi

Another approach would be to select an autoimcremented column and then filter it using HAVING.

另一种方法是选择一个自动增加的列，然后使用 HAVING 对其进行过滤。

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

But I would probably stick with the high limit approach.

但我可能会坚持使用上限方法。

回答by Bruno.S

As others mentioned, from the MySQL manual. In order to achieve that, you can use the maximum value of an unsigned big int, that is this awful number (18446744073709551615). But to make it a little bit less messy you can the tilde "~" bitwise operator.

正如其他人提到的，来自 MySQL 手册。为了实现这一点，您可以使用 unsigned big int 的最大值，即这个可怕的数字 (18446744073709551615)。但是为了让它不那么混乱，您可以使用波浪号“~”按位运算符。

  LIMIT 95, ~0

it works as a bitwise negation. The result of "~0" is 18446744073709551615.

它用作按位否定。“~0”的结果是 18446744073709551615。

回答by fed

Just today I was reading about the best way to get huge amounts of data (more than a million rows) from a mysql table. One way is, as suggested, using LIMIT x,ywhere xis the offset and ythe last row you want returned. However, as I found out, it isn't the most efficient way to do so. If you have an autoincrement column, you can as easily use a SELECTstatement with a WHEREclause saying from which record you'd like to start.

就在今天，我正在阅读有关从 mysql 表获取大量数据（超过一百万行）的最佳方法。一种方法是，如建议的那样，使用LIMIT x,ywherex是偏移量和y您想要返回的最后一行。但是，正如我发现的那样，这并不是最有效的方法。如果你有一个自动增量列，你可以很容易地使用一个SELECT带有WHERE子句的语句，说明你想从哪个记录开始。

For example, SELECT * FROM table_name WHERE id > x;

例如， SELECT * FROM table_name WHERE id > x;

It seems that mysql gets all results when you use LIMITand then only shows you the records that fit in the offset: not the best for performance.

似乎mysql在您使用时获得所有结果LIMIT，然后只向您显示适合偏移量的记录：不是最好的性能。

Source: Answer to this question MySQL Forums. Just take note, the question is about 6 years old.

来源：回答这个问题MySQL 论坛。请注意，这个问题大约有 6 年历史了。

回答by sissi_luaty

You can use a MySQL statement with LIMIT:

您可以使用带有 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;

Tested in MySQL 5.5.44. Thus, we can avoid the insertion of the number 18446744073709551615.

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

note: the transaction makes sure that the variable @rows is in agreement to the table considered in the execution of statement.

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

回答by Baron Von Sparklefarts

I know that this is old but I didnt see a similar response so this is the solution I would use.

我知道这是旧的，但我没有看到类似的回应，所以这是我将使用的解决方案。

First, I would execute a count query on the table to see how many records exist. This query is fast and normally the execution time is negligible. Something like:

首先，我将对表执行计数查询以查看存在多少条记录。此查询速度很快，通常执行时间可以忽略不计。就像是：

SELECT COUNT(*) FROM table_name;

Then I would build my query using the result I got from count as my limit (since that is the maximum number of rows the table could possibly return). Something like:

然后我将使用从 count 中得到的结果作为我的限制来构建我的查询（因为这是表可能返回的最大行数）。就像是：

SELECT * FROM table_name LIMIT count_result OFFSET desired_offset;

Or possibly something like:

或者可能是这样的：

SELECT * FROM table_name LIMIT desired_offset, count_result;

Of course, if necessary, you could subtract desired_offset from count_result to get an actual, accurate value to supply as the limit. Passing the "18446744073709551610" value just doesnt make sense if I can actually determine an appropriate limit to provide.

当然，如有必要，您可以从 count_result 中减去 desired_offset 以获得实际、准确的值作为限​​制提供。如果我实际上可以确定要提供的适当限制，则传递“18446744073709551610”值是没有意义的。

回答by user3131125

WHERE .... AND id > <YOUROFFSET>

id can be any autoincremented or unique numerical column you have...

id 可以是您拥有的任何自动递增或唯一的数字列...