在MySQL中变量限制条款Variable LIMIT Clause in MySQL
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;
(原文：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:
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);
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 ;
call get_users(1, 10);
(原文：worked best for me - thanks)
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;
(原文：I like the initialization of @rownum inside the SELECT, nice!)
I know this answer has come late, but try SQL_SELECT_LIMIT.
Declare rowCount int; Set rowCount = 100; Set SQL_SELECT_LIMIT = rowCount; Select blah blah Set SQL_SELECT_LIMIT = Default;
(原文：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.
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;
This is a simple code for php:
$my_size = 100; $samplequery = "SELECT * FROM some_table LIMIT ".$my_size;
(原文：This does not answer the question. The OP is looking for a way to accomplish this with a stored procedure.)
- 发现差异在MySQL的两个表的行数Finding difference in row count of two tables in MySQL
- 如何设置连接超时根据MySQL用户登录的吗How to setup a connection timeout depending of the user login in MySQL
- MySQL计数(不同的())意想不到的结果MySQL COUNT(DISTINCT()) unexpected results
- 有可能有一个MySQL索引视图?Is it possible to have an indexed view in MySQL?
- 什么是适当的交叉表的SQL查询语法吗?What is the proper syntax for a cross-table SQL query?
- 在MySQL中变量限制条款Variable LIMIT Clause in MySQL
- 从MySQL在Java中检索记录Retrieving records from MySQL in Java
- mysql导入脚本mysql import script
- 有什么问题这个create table语句(复制)What is wrong with this create table statement [duplicate]
- 是什么数据类型使用散列密码字段长度呢?What data type to use for hashed password field and what length?
- 这是什么时间格式?(不是UNIX,UTC,没有什么)What time format is this? (not UNIX, not UTC, nothing)