Search code examples
mysqllimitoffset

How to get ALL rows starting from row x in MySQL

In MySQL, how can I retrieve ALL rows in a table, starting from row X? For example, starting from row 6:

LIMIT 5,0

This returns nothing, so I tried this:

LIMIT 5,ALL

Still no results (sql error).

I'm not looking for pagination functionality, just retrieving all rows starting from a particular row. LIMIT 5,2000 seems like overkill to me. Somehow Google doesn't seem to get me some answers. Hope you can help.

Thanks

Solution

  • According to the documentation:

    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;

    This is the maximum rows a MyISAM table can hold, 2^64-1.

    There is a limit of 2^32 (~4.295E+09) rows in a MyISAM table. If you build MySQL with the --with-big-tables option, the row limitation is increased to (2^32)^2 (1.844E+19) rows. See Section 2.16.2, “Typical configure Options”. Binary distributions for Unix and Linux are built with this option.