Use LIMIT to paginate results in MySQL query

user_330057 0
Viewed 58653

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?

limit pagination mysql
3 Answers

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;
user_51858040
Related