mysql - MySQL 5.6.1 中 LIMIT 子句中的变量不起作用

Question

根据这个previously asked question据我了解，我可以在 mysql 版本 5.5.6+ 中运行这样的查询:

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

但是当我像这样运行时，出现错误。

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

这是我检查mysql版本

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

Answer 1

documentation很清楚。在存储程序中，limit可以将变量/参数作为参数:

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.

您的代码不是存储程序。因此，您需要使用常量。