如何仅使用(My)SQL使限制偏移量动态 [英] How to make limit offset dynamic using only (My)SQL
问题描述
此代码无效
select pagenr into @offset from pages where id = 3;
select * from table1 limit @offset*10, 10;
我需要使用哪种SQLcode才能使这种代码正常工作
仅使用 SQL!
What SQLcode do I need to use in order to get this kind of code to work
using only SQL!
请注意
SET SQL_SELECT_LIMIT = @count
不起作用,因为我主要关注偏移量,而不是限制本身.
doesn't work because I'm mainly concerned with the offset, not the limit as such.
推荐答案
根据MySQL 5.5规范:
From the MySQL 5.5 specification:
LIMIT
子句可用于 限制返回的行数 通过SELECT
语句.LIMIT
需要 一两个数字参数,其中 必须都为非负整数 常量,但以下情况除外:
The
LIMIT
clause can be used to constrain the number of rows returned by theSELECT
statement.LIMIT
takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:
- 在准备好的语句中,可以使用
?
指定LIMIT
参数 占位符标记. - 在存储的程序中,可以使用以下命令指定
LIMIT
参数 自MySQL 5.5.6起为整数值的例程参数或局部变量.
- 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 as of MySQL 5.5.6.
因此,在存储过程中,将可以进行以下操作:
So, inside a stored procedure, the following would work:
DECLARE offset bigint
SELECT pagenr * 10 INTO offset FROM pages where id = 3;
SELECT * FROM table1 LIMIT offset, 10;
否则,您将需要预先计算值并通过查询将其传递.您应该已经知道页面的大小和页码,所以这并不困难.
Otherwise, you'll need to precompute the value and pass it in via the query. You should already know the page size and page number, so this shouldn't be difficult.
这篇关于如何仅使用(My)SQL使限制偏移量动态的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!