Mysql Offset Endless Strings

Question

I would like to build a query that displays all the results in a table, but shifts 5 from the beginning of the table. As far as I can tell, MySQL LIMIT requires a constraint as well as an offset. Is there any way to do this?

Answer 1

From MySQL Guide to LIMIT :

To get all rows from a specific offset to the end of the set result, you can use some amount for the second parameter. This statement extracts all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95, 18446744073709551615; 

Answer 2

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

 SELECT * FROM somewhere LIMIT 18446744073709551610 OFFSET 5 

Answer 3

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

You may need more than one billion records, but I want to say that there is a certain limit to the number you want, and it is less than 18 quintillion. For the sake of stability, optimization and, possibly, usability, I would suggest introducing some significant restrictions on the request. It will also reduce confusion for those who have never seen this magic number, and has the added benefit of reporting at least the number of records that you are ready to process right away.

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

Answer 4

Another approach would be to select a column with auto-flickers and then filter it using HAVING.

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

But I probably follow a high limit approach.

Answer 5

Only today I read about the best way to get huge amounts of data (over a million rows) from a mysql table. One way, as suggested, is to use LIMIT x,y , where x is the offset and y is the last line you want to return. However, as I found out, this is not the most effective way to do this. If you have an auto-increment column, you can easily use the SELECT with the WHERE from which you want to start recording.

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

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

Source: answer this question MySQL Forums . Just bear in mind that the question is about 6 years old.

Answer 6

You can use the MySQL statement with LIMIT:

 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 entering the number 18446744073709551615.

Note: the transaction ensures that the @rows variable is consistent with the table considered during the execution of the instruction.

Answer 7

I know this is old, but I have not seen a similar answer, so this is a solution that I would use.

First, I would execute a count query on a table to find out how many records exist. This request is fast, and usually the execution time is short. Something like:

 SELECT COUNT(*) FROM table_name; 

Then I would build my query using the result that I got from count, as my limit (since this is the maximum number of rows that can be returned in the table). Something like:

 SELECT * FROM table_name LIMIT count_result OFFSET desired_offset; 

Or maybe something like:

 SELECT * FROM table_name LIMIT desired_offset, count_result; 

Of course, if necessary, you can subtract the desired execution from count_result to get the actual exact value for the feed as the limit. Passing the value "18446744073709551610" simply does not make sense if I can actually determine the appropriate limit for the provision.

Answer 8

Using this other question and combining this trick with Greg's answer, you can use ~ 0 instead of the maximum unsigned bigint value:

 SELECT * FROM tbl LIMIT ~0 OFFSET 95; 

or

 SELECT * FROM tbl LIMIT 95, ~0; 

Answer 9

 WHERE .... AND id > <YOUROFFSET> 

id can be any auto-incrementing or unique numeric column you have ...