MySQL Cookbook, 2nd Edition

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 statement from within a program, in which case you can evaluate the expressions yourself and insert the resulting values into the statement 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 statement string. You must evaluate the expression first, and then place the resulting value in the statement. For example, if you produce a statement string in Perl or PHP as follows, an error will result when you attempt to execute the statement:

$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 statement string.