MySQL offset infinite rows

Question

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 LIMIT requires a limit as well as an offset. Is there any way to do this?

Solution 1

From the MySQL Manual on LIMIT:

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:

SELECT * FROM tbl LIMIT 95, 18446744073709551615;

Solution 2

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

SELECT * FROM somewhere LIMIT 18446744073709551610 OFFSET 5

Solution 3

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?

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.

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.

Solution 4

Another approach would be to select an autoimcremented column and then filter it using 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.

Solution 5

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.

  LIMIT 95, ~0

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

View more solutions

Comments

[Petruza]

Awful! I came here hoping that MySQL made the Limit clause optional, as it is, but also with an offset provided... but no! I've seen this 18446744073709551615 scatter all over the code and I was blaming lazy programmers, but it's a design feature!

[Antony]

Wow, is this the official solution from MySQL team?

[AlicanC]

18446744073709551615 is 2^64-1 for those who were wondering. You may want to watch out because you won't be able to store this value in an 32 bit integer. You have to make sure you store this as a string to ensure compatibility.

[vulcan raven]

Terrible! they need to get more elegant than that... Limit -1 or Limit Null looks pretty reasonable! or atleast Limit should accept a subquery like select * from table limit (select count(*) from table)

[Karl Adler]

use php 'PHP_INT_MAX' to avoid overflow effects.

[octern]

This will give incorrect results if you've ever deleted a record. This method is especially dangerous, because it works most of the time, and fails silently when it doesn't.

[octern]

Bad idea. It will give the incorrect offset if you've ever deleted a row.

[cesoid]

I think this falls under the category of TODINAWYW, which I just made up, and it stands for The Official Documentation is Not Always What You Want. It explains how you would get all records if you had a table that was maxed out at (about) 18 quintillion rows, and also suggests using "some large number" as an alternative, but I would suggest that there is a meaningful, non-infinite, and non-18-quintillion number of records you are willing to get back, and you should use that. (I included this in my own answer.)

[amd]

You are right, but keeping this decision to the developer is not a good choice

[amd]

select count(*) on a table with 7M records takes around 17s

[cesoid]

@amd Could you explain that a little more? I don't know what you're trying to say.

[stannius]

There seems to be some special handling in MySQL for 18446744073709551615. It's not accepted as a LIMIT for a DELETE clause with safe mode on, for one example. However 18446744073709551614 is accepted.

[Autumn Leonard]

@cesoid I think he's saying that devs shouldn't be the ones to arbitrarily choose the business logic, which I agree with, but only to a point. Let's say you're returning a list of orders to a customer. It's perfectly reasonable to never return more than, say, a million at a time, but limiting to 100 might cause confusion.

[Reham Fahmy]

thank you, and i wonder how can i put such query in PHP statement! i mean like that way $sql = 'SET @a :=0 SELECT .....';

[cesoid]

@amd I'm not saying that the developer should change the behavior of the app in order to avoid using 18446744073709551615. I'm saying that they should consider whether using that number makes sense as part of the implementation of whatever the client or interface designer has requested, and that it is very unlikely to be the right implementation for anything. The decision to use MySQL was probably already made by the developer without asking whether there would be more than 18 quintillion of something.

[jurchiks]

Doesn't work in MariaDB 10.3 :( I tried both LIMIT 5, ~0 and LIMIT ~0 OFFSET 5. Is this a MySQL 8.0 feature?

[Jonny Nott]

This is not a thing in MySQL 5.7 - invalid syntax.

[Rodrirokr]

as @amd stated: "select count(*) on a table with 7M records takes around 17s"

[Rodrigo]

I wonder why people still use MySQL when there's PostgreSQL around...

[Ray Perea]

My 2 cents on this.... You can already easily get all records without having to use that magic 18 quintillion number... Consider this query - select * from table. So that begs the question, what would you do if you got 18 quintillion records for that query? Are we to modify all our basic queries with a limit like this select * from table limit 0, 1000000 to avoid getting too many records? I just think that it's bad design on MySQL's part, especially when other database systems like PostgreSQL can have queries like this select * from myTable offset 10 which I use all the time

[cesoid]

@RayPerea I agree that MySQL should be designed to have an offset without an upper limit, and most of the time you probably don't need to limit your queries in that way. I just think that the best option for working around that shortcoming is conveniently pretty good practice anyway, which is to at least briefly consider some upper limit, even if your only safe guess is to make it really high, like 1,000,000,000,000. It's annoying, but the other options seem worse.

[Ray Perea]

@cesoid Well said

[Sámal Rasmussen]

~0 is not an improvement of readability - it is an obfuscation of what the value really is.