如何在limit语句中使用用户定义的变量? [英] How to use User-Defined Variables in the limit statement?

问题描述

我想要这样的通用格式而不是硬代码的分页查询:

I want a paging query in common format, not hard code, by this:

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

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

但是mysql告诉我:

but the mysql told me that:

[Err] 1064-您的SQL语法有错误；查看手册 对应于您的MySQL服务器版本以获取正确的语法 在第1行的'(@ pageIndex-1)* @ pageSize，@pageSize'附近使用

[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

这是什么问题?

推荐答案

您不能将用户定义的变量与LIMIT一起使用.

You cannot use the user defined variables with LIMIT.

MySQL Doc说在大多数情况下，用户变量可能会用到 允许使用表达式.当前不包括上下文 明确需要文字值(例如在LIMIT子句中) SELECT语句或LOAD DATA的IGNORE N LINES子句 声明"

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"

但是您可以使用'用户定义函数'或'

But you can achieve similar functionality using 'user defined function' or 'stored procedure' or 'plain SQL by calculating @rowid and using it in the where clause'.

这篇关于如何在limit语句中使用用户定义的变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！