mysql - 如何在limit语句中使用用户定义变量？

Question

我想要通用格式的分页查询，而不是硬编码，方法如下:

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

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

但是 mysql 告诉我:

[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

这有什么问题吗？

Answer 1

您不能将用户定义的变量与 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"

但是您可以使用“用户定义函数”或“stored procedure”来实现类似的功能。 ' 或“通过计算 @rowid 并在 where 子句中使用它的纯 SQL”。