corsiKa corsiKa - 1 month ago 7
MySQL Question

Use LIMIT to paginate results in MySQL query

I want to fetch my results a 'page' at a time; I want the page number to be a parameter (in a JDBC prepared statement). Consider the following snippet

SELECT * FROM thread t ORDER BY t.id LIMIT ((? - 1) * 20), 20


So ideally, this would result, for page 1, to
LIMIT 0, 20
.

When I test

SELECT * FROM thread t ORDER BY t.id LIMIT ((1 - 1) * 20), 20


I am told I have a syntax error. I don't see what it could be, though - it's just some simple math. All it tells me is


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '((1 -
1) * 20), 20' at line 1


What am I doing wrong with my
LIMIT
clause, and how can I fix it?

Answer

MySQL requires numeric constants for that LIMIT syntax.

From http://dev.mysql.com/doc/refman/5.7/en/select.html:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

  • Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

  • Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.

Compute the constant on the Java side.