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

回答1:

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.

回答2:

If you're looking to get the last x number of rows, the easiest thing to do is SORT DESC and LIMIT to the first x rows. Granted, the SORT will slow your query down. But if you're opposed to setting an arbitrarily large number as the second LIMIT arg, then that's the way to do it.

回答3:

The only solution I am aware of currently is to do as you say and give a ridiculously high number as the second argument to LIMIT. I do not believe there is any difference in performance to specifying a low number or a high number, mysql will simply stop returning rows at the end of the result set, or when it hits your limit.

回答4:

I think you don't need to enter max value for select all by LIMIT. It is enough to find count of table and then use it as max LIMIT.

回答5:

The next query should work too, and is in my opinion more effective...

SELECT * FROM mytbl WHERE id != 1 ORDER BY id asc

By ordering the query will find the id imediately and skip this one, so the next rows he won't check anymore whether the id = 1.