在 MySQL 的 LIMIT 子句中使用变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/245180/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using variable in a LIMIT clause in MySQL
提问by MPX
I am writing a stored procedure where I have an input parameter called my_sizethat is an INTEGER. I want to be able to use it in a LIMITclause in a SELECTstatement. Apparently this is not supported, is there a way to work around this?
我正在编写一个存储过程，其中有一个名为my_size的输入参数，它是一个INTEGER。我希望能够LIMIT在SELECT语句的子句中使用它。显然这不受支持，有没有办法解决这个问题？
# I want something like:
SELECT * FROM some_table LIMIT my_size;
# Instead of hardcoding a permanent limit:
SELECT * FROM some_table LIMIT 100;
采纳答案by TheSoftwareJedi
A search turned up this article. I've pasted the relevant text below.
搜索出现了这篇文章。我已经粘贴了下面的相关文字。
Here's a forum post showing an example of prepared statements letting you assign a variable value to the limit clause:
http://forums.mysql.com/read.php?98,126379,133966#msg-133966
However, I think this bug should get some attention because I can't imagine that prepared statements within a procedure will allow for any procedure-compile-time optimizations. I have a feeling that prepared statements are compiled and executed at the runtime of the procedure, which probaby has a negative impact on efficiency. If the limit clause could accept normal procedure variables (say, a procedure argument), then the database could still perform compile-time optimizations on the rest of the query, within the procedure. This would likely yield faster execution of the procedure. I'm no expert though.
这是一个论坛帖子，显示了一个准备好的语句示例，让您可以为 limit 子句分配一个变量值：
http://forums.mysql.com/read.php?98,126379,133966#msg-133966
但是，我认为这个错误应该引起一些注意，因为我无法想象过程中的准备好的语句将允许任何过程编译时优化。我有一种感觉，准备好的语句是在程序运行时编译和执行的，这可能会对效率产生负面影响。如果 limit 子句可以接受正常的过程变量（比如过程参数），那么数据库仍然可以在过程中对查询的其余部分执行编译时优化。这可能会更快地执行该过程。虽然我不是专家。
回答by ENargit
For those, who cannot use MySQL 5.5.6+ and don't want to write a stored procedure, there is another variant. We can add where clause on a subselect with ROWNUM.
对于那些不能使用 MySQL 5.5.6+ 并且不想编写存储过程的人，还有另一种变体。我们可以使用 ROWNUM 在子选择上添加 where 子句。
SET @limit = 10;
SELECT * FROM (
SELECT instances.*,
@rownum := @rownum + 1 AS rank
FROM instances,
(SELECT @rownum := 0) r
) d WHERE rank < @limit;
回答by Pradeep Sanjaya
STORED PROCEDURE
存储程序
DELIMITER $
create PROCEDURE get_users(page_from INT, page_size INT)
begin
SET @_page_from = page_from;
SET @_page_size = page_size;
PREPARE stmt FROM "select u.user_id, u.firstname, u.lastname from users u limit ?, ?;";
EXECUTE stmt USING @_page_from, @_page_size;
DEALLOCATE PREPARE stmt;
end$
DELIMITER ;
USAGE
用法
call get_users(1, 10);
回答by user888112
I know this answer has come late, but try SQL_SELECT_LIMIT.
我知道这个答案来晚了，但试试 SQL_SELECT_LIMIT。
Example:
例子：
Declare rowCount int;
Set rowCount = 100;
Set SQL_SELECT_LIMIT = rowCount;
Select blah blah
Set SQL_SELECT_LIMIT = Default;
回答by Jiho Kang
回答by Алексей Пузенко
Another way, the same as wrote "Pradeep Sanjaya", but using CONCAT:
另一种方式，与写“Pradeep Sanjaya”相同，但使用 CONCAT：
CREATE PROCEDURE `some_func`(startIndex INT, countNum INT)
READS SQL DATA
COMMENT 'example'
BEGIN
SET @asd = CONCAT('SELECT `id` FROM `table` LIMIT ',startIndex,',',countNum);
PREPARE zxc FROM @asd;
EXECUTE zxc;
END;
回答by rekaszeru
As of MySQL version 5.5.6, you can specify LIMITand OFFSETwith variables / parameters.
从 MySQL 5.5.6 版开始，您可以指定LIMIT和OFFSET使用变量/参数。
For reference, see the 5.5 Manual, the 5.6 Manualand @Quassnoi's answer
回答by juan_carlos_yl
I've faced the same problem using MySql 5.0 and wrote a procedure with the help of @ENargit's answer:
我在使用 MySql 5.0 时遇到了同样的问题，并在@ENargit 的回答的帮助下编写了一个程序：
CREATE PROCEDURE SOME_PROCEDURE_NAME(IN _length INT, IN _start INT)
BEGIN
SET _start = (SELECT COALESCE(_start, 0));
SET _length = (SELECT COALESCE(_length, 999999)); -- USING ~0 GIVES OUT OF RANGE ERROR
SET @row_num_personalized_variable = 0;
SELECT
*,
@row_num_personalized_variable AS records_total
FROM(
SELECT
*,
(@row_num_personalized_variable := @row_num_personalized_variable + 1) AS row_num
FROM some_table
) tb
WHERE row_num > _start AND row_num <= (_start + _length);
END;
Also included the total rows obtained by the query with records_total.
还包括通过records_total 查询获得的总行数。
