在MySQL中变量限制条款Variable LIMIT Clause in MySQL

- 此内容更新于:2014-12-30
主题:

我写一个存储过程,我有一个输入参数称为my_size int,我希望能够用在一个select语句的限制条款。显然这是不支持,是否有办法解决吗?

原文:

I am writing a stored procedure where I have an input parameter called my_size that is an int, I want to be able to use in a limit clause in a select statement. Apparently this is not supported, is there a way to work around this?

# I want something like:
SELECT * FROM some_table LIMIT my_size;

# Instead of hardcoding a permanent limit:
SELECT * FROM some_table LIMIT 100;
Hammerite的回复:如果你有控制你使用哪个版本的MySQL,看起来这是固定在v5.5.6开始。bugs.mysql.com/bug.php?id=11918

(原文:If you have control over which version of MySQL you use, it looks like this is fixed starting in v5.5.6. bugs.mysql.com/bug.php?id=11918)

user3011839的回复:stackoverflow.com/a/4315661发现另一个简单的解决方案

(原文:found another simple solution stackoverflow.com/a/4315661)

解决方案:
原文:

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.

解决方案:
存储过程 使用 调用get_user(10);
原文:

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);
uval的回复:工作最适合我,谢谢

(原文:worked best for me - thanks)

解决方案:
对于那些不能使用MySQL,5.5.6 +和不想写一个存储过程,还有另一个变体。我们可以添加where子句的子选择ROWNUM。
原文:

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.

SET @limit = 10;
SELECT * FROM (
  SELECT instances.*, 
         @rownum := @rownum + 1 AS rank
    FROM instances, 
         (SELECT @rownum := 0) r
) d WHERE rank < @limit;
andig的回复:我喜欢@rownum在选择的初始化,不错!

(原文:I like the initialization of @rownum inside the SELECT, nice!)

解决方案:
我知道这个答案来得太晚,但SQL_SELECT_LIMIT尝试。 例子:
原文:

I know this answer has come late, but try SQL_SELECT_LIMIT.

Example:

Declare rowCount int;
Set rowCount = 100;
Set SQL_SELECT_LIMIT = rowCount;
Select blah blah
Set SQL_SELECT_LIMIT = Default;
Gruber的回复:不幸的是# 39;t似乎支持一个论点起始位置,2和5一样,从位置2行5。但是,除非你需要可能奏效。

(原文:Unfortunately doesn't seem to support an argument for starting position, like 5,2 for taking 2 rows from position 5. But unless you need that it may do the trick.)

解决方案:
这个功能已经添加到MySQL 5.5.6。 看看这个链接。 我已经升级到MySQL 5.5只是为了这个特性,伟大的工作。 5.5也有很多性能升级的地方,我完全推荐它。
原文:

This feature has been added to MySQL 5.5.6. Check this link out.

I've upgraded to MySQL 5.5 just for this feature and works great. 5.5 also has a lot of performance upgrades in place and I totally recommend it.

解决方案:
另一种方式,一样的写“Pradeep桑佳亚”,但使用CONCAT:
原文:

Another way, the same as wrote "Pradeep Sanjaya", but using 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;
解决方案:
这是一个简单的php代码:
原文:

This is a simple code for php:

$my_size = 100;
$samplequery = "SELECT * FROM some_table LIMIT ".$my_size;
Aaron J Spetner的回复:这并没有回答这个问题。OP是寻找一种方法来完成这一个存储过程。

(原文:This does not answer the question. The OP is looking for a way to accomplish this with a stored procedure.)