7

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?

corsiKa
  • 81,495
  • 25
  • 153
  • 204

3 Answers3

18

This cannot be done.

See solution here: MySQL Math and COUNT(*) in LIMIT

I would recommend using javascript or something to handle the first parameter (i.e. offset) such as: limit 0,20 on first page and limit 21,20 on second...

For example if your first page has a get variable in the url www.example.com?page=1

offset = (page - 1)*20 ;
row_count = 20;
select * from table limit (offset, row_count);
Community
  • 1
  • 1
Shawn
  • 3,583
  • 8
  • 46
  • 63
  • optimally you would use something like this. The code is much faster than two queries where you would have to do a count on the table and then use that count result as your new parameters because of the potential for n amount of rows in your table... The larger the table the less efficient queries will be. – Shawn Jul 30 '12 at 20:11
12

Define Offset for the query using the following syntax 

SELECT column FROM table 
LIMIT {someLimit} OFFSET {someOffset};

For example, to get page #1 (records 1-10), set the offset to 0 and the limit to 10;

SELECT column FROM table 
LIMIT 10 OFFSET 0;

To get page #2 (records 11-20), set the offset to 10 where the limit to 10

SELECT column FROM table 
LIMIT 10 OFFSET 10;
Junior
  • 11,602
  • 27
  • 106
  • 212
Prabodh Hend
  • 1,321
  • 1
  • 12
  • 15
10

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.

Pang
  • 9,564
  • 146
  • 81
  • 122
Matt
  • 43,482
  • 6
  • 101
  • 102
  • 1
    so it can't evaluate `((1-1)*20)` as a constant, even though it consists of nothing but constants? That seems like a bizarre limitation. I figured it was considered a constant, since it contained no variables... but at least I know what to do now! Thanks. – corsiKa Jul 30 '12 at 20:20
  • Yeah, mathematically speaking, you're representing a "constant" ((1-1)*20 is always the same).. but from a parser perspective, MySQL isn't in a mathematical-expression-parsing mood :) – Matt Jul 30 '12 at 20:22
  • 2
    I just replaced the offending code with `limit ?, 20` and using `pageStart = (page - 1) * 20;` and it worked like a charm. Now on to the next bug :) – corsiKa Jul 30 '12 at 20:28