How to use User-Defined Variables in the limit sta

Question

I want a paging query in common format, not hard code, by this:

set @pageSize = 10; set @pageIndex = 2;

select * from city LIMIT (@pageIndex-1)*@pageSize, @pageSize;

but the mysql told me that:

[Err] 1064 - 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 '(@pageIndex-1)*@pageSize, @pageSize' at line 1

what's the problem about this?

Answer 1

You cannot use the user defined variables with LIMIT.

MySQL Doc says "User variables may be used in most contexts where expressions are permitted. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE N LINES clause of a LOAD DATA statement"

But you can achieve similar functionality using 'user defined function' or 'stored procedure' or 'plain SQL by calculating @rowid and using it in the where clause'.