Calculating LIMIT Values from Expressions
Problem
You want to use
expressions to specify the arguments for LIMIT
.
Solution
Sadly, you cannot. You can use only literal integers—unless you issue the query from within a program, in which case you can evaluate the expressions yourself and stick the resulting values into the query string.
Discussion
Arguments to LIMIT
must be literal integers, not
expressions. Statements such as the following are illegal:
SELECT * FROM profile LIMIT 5+5; SELECT * FROM profile LIMIT @skip_count, @show_count;
The same “no expressions allowed”
principle applies if you’re using an expression to
calculate a LIMIT
value in a program that
constructs a query string. You must evaluate the expression first,
then place the resulting value in the query. For example, if you
produce a query string in Perl (or PHP) as follows, an error will
result when you attempt to execute the query:
$str = "SELECT * FROM profile LIMIT $x + $y";
To avoid the problem, evaluate the expression first:
$z = $x + $y; $str = "SELECT * FROM profile LIMIT $z";
Or do this (but don’t omit the parentheses or the expression won’t evaluate properly):
$str = "SELECT * FROM profile LIMIT " . ($x + $y);
If you’re constructing a two-argument
LIMIT
clause, evaluate both expressions before
placing them into the query string.
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.