Variable in LIMIT Clause in MySQL 5.6.1 not working

Question

Per this previously asked question it's my understanding that I could run a query like this in mysql version 5.5.6+:

set @limit_start=10;
set @limit_offset=10;
select
    ID,
    AGE, 
    GENDER
from
    PLAYER
limit
    @limit_start, @limit_offset;

But when I run it like so, I get an error.

mysql> set @limit_start=10;
Query OK, 0 rows affected (0.00 sec)

mysql> set @limit_offset=10;
Query OK, 0 rows affected (0.00 sec)

mysql> select
    ->     ID
    -> from
    ->     PLAYER_GAME_RESULT
    -> limit
    ->     @limit_start, @limit_offset;
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 '@limit_start, @limit_offset' at line 6

This is me checking the mysql version

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.21    |
+-----------+

score 1 · Accepted Answer · answered Jun 02 '16 at 21:37

The documentation is pretty clear. In a stored program, then limit can take a variable/parameter as an argument:

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.

Your code is not a stored program. Hence, you need to use constants.

Variable in LIMIT Clause in MySQL 5.6.1 not working

1 Answers1