将 LIMIT 作为参数传递给 MySQL sproc
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2875238/
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
passing LIMIT as parameters to MySQL sproc
提问by Kyle
I'm creating a paging class and need to pass in two parameters to my MySQL stored procedure for the LIMIT clause.
我正在创建一个分页类，需要将两个参数传递给我的 LIMIT 子句的 MySQL 存储过程。
I'm passing them in as INTs and trying something like this
我将它们作为 INT 传递并尝试这样的事情
SELECT *
FROM
`MyTable`
LIMIT
MyFirstParamInt, MySecondParamInt
it gives me an error when I try and save the sproc though. Is there a way to do this that I'm just missing? Or am I going to have to EVAL the whole query and EXECUTE it?
但是，当我尝试保存 sproc 时，它给了我一个错误。有没有办法做到这一点，我只是想念？或者我将不得不评估整个查询并执行它？
回答by Quassnoi
Prior to 5.5.6, LIMITcould not be parameterized in MySQL stored procedures. You'd need to build the query dynamically and execute it.
在 5.5.6 之前，LIMIT无法在 MySQL 存储过程中参数化。您需要动态构建查询并执行它。
In 5.5.6 and above, you can just pass the stored procs parameters as arguments to LIMITand OFFSETas long as they are INTEGER.
在 5.5.6 及更高版本中，您可以将存储的 procs 参数作为参数传递给LIMIT并且OFFSET只要它们是INTEGER。
回答by Eric_M
I just found a solution which may be helpful. Use declared variables in your stored procedure and set them to your parameters
我刚刚找到了一个可能有用的解决方案。在您的存储过程中使用声明的变量并将它们设置为您的参数
eg.
例如。
CREATE PROCEDURE MyProcedure(
IN paramFrom INT,
IN paramTo INT
)
BEGIN
DECLARE valFrom INT;
DECLARE valTo INT;
SET valFrom = paramFrom;
SET valTo = paramTo;
SELECT * FROM myTable LIMIT valFrom, valTo;
END
回答by k.m
From http://dev.mysql.com/doc/refman/5.1/en/select.html:
从http://dev.mysql.com/doc/refman/5.1/en/select.html：
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants(except when using prepared statements).
LIMIT 子句可用于限制 SELECT 语句返回的行数。LIMIT 接受一个或两个数字参数，它们都必须是非负整数常量（使用准备好的语句时除外）。
Here's prepared statement example which might help you:
这是准备好的语句示例，它可能对您有所帮助：
SET @skip=1;
SET @rows=5;
PREPARE STMT FROM 'SELECT * FROM table LIMIT ?, ?';
EXECUTE STMT USING @skip, @rows;
回答by WebLearner
The following worked just fine in MySQL 5.5.35. It also worked in another procedure where the same SELECTwas used within a DECLARE . . . CURSORstatement.
以下在 MySQL 5.5.35 中工作得很好。它也在另一个过程中起作用，其中SELECT在DECLARE . . . CURSOR语句中使用了相同的过程。
CREATE PROCEDURE `test`(
IN `lim_val` INT,
IN `lim_offset` INT
)
BEGIN
SELECT array_ident_id
FROM ArrayIdents
ORDER BY array_ident_id
LIMIT lim_val OFFSET lim_offset;
END;
回答by mukesh mali
Best pagination example may help you
最佳分页示例可能对您有所帮助
call user_list(v_private_key,v_user_id,v_pageIndex,v_limit,v_image_path,
@o_rec_count,
@o_error_code,
@o_error_message)
调用 user_list（v_private_key、v_user_id、v_pageIndex、v_limit、v_image_path、@o_rec_count、@o_error_code、@o_error_message）
DECLARE v_QueryLimit TEXT DEFAULT "";
DECLARE v_Select TEXT DEFAULT "";
DECLARE v_where TEXT DEFAULT '';
DECLARE v_From TEXT DEFAULT "";
DECLARE v_group_by TEXT DEFAULT " ";
DECLARE v_having TEXT DEFAULT "";
DECLARE v_OrderBy TEXT DEFAULT "";
SET o_error_code = '200';
SET v_Select = CONCAT(" SELECT
AES_DECRYPT(email,'",v_private_key,"') AS email,
AES_DECRYPT(first_name,'",v_private_key,"') AS first_name,
AES_DECRYPT(last_name,'",v_private_key,"') AS last_name,
AES_DECRYPT(mobile_no,'",v_private_key,"') AS mobile_no,
CONCAT(AES_DECRYPT(first_name,'",v_private_key,"'),' ', AES_DECRYPT(last_name,'",v_private_key,"')) as full_name,
CONCAT('",v_image_path,"','profile/',IFNULL(thumb,'user_thumb.png')) AS thumb,
CONCAT('",v_image_path,"','profile/small/',IFNULL(thumb,'user_thumb.png')) AS thumb_small,
IFNULL(country_code,'+91') as country_code,
IFNULL(unique_code,'') as user_code
");
SET v_From = CONCAT(" FROM userinfo WHERE role_group = 2 AND id != ",v_user_id," ");
IF (v_PageIndex) > 0 THEN
SET v_QueryLimit = CONCAT(" LIMIT ", v_limit, "," , v_pageIndex);
END IF;
-- set v_group_by = concat(' GROUP BY ut.user_card_id, ');
SET @rec_Query= CONCAT(v_Select
,v_From
,v_Where
,v_group_by
,v_having
,v_OrderBy);
/**************** Get Record Count **************/
SET @cnt_Query = CONCAT("Select Count(*) INTO @o_rec_count FROM (",@rec_Query,") AS tmp");
PREPARE c2 FROM @cnt_Query;
EXECUTE c2;
SET o_rec_count=@o_rec_count;
/**************** Calculate Limit **************/
IF (v_limit != "" && v_pageIndex != "") AND @o_rec_count>0 THEN
CALL Calculate_Paging_Index(@o_rec_count ,v_limit,v_pageIndex,@new_start_limit);
SET v_QueryLimit = CONCAT(" LIMIT ",@new_start_limit, ",",v_limit);
END IF;
SET @vv2_Query= CONCAT(v_Select
,v_From
,v_Where
,v_group_by
,v_having
,v_OrderBy
,v_QueryLimit);
PREPARE s2 FROM @vv2_Query;
EXECUTE s2;
SET o_error_message = "success";
calculate page index SP
计算页面索引SP
CREATE PROCEDURE calculate_paging_index(in_count,in_limit,in_page,@out_start_limit)enter code here`
CREATE PROCEDURE calculate_paging_index 在(in_count,in_limit,in_page,@out_start_limit)此处输入代码`
DECLARE count1 INT;
DECLARE total_pages INT;
SET count1 = in_count;
IF( count1 > 0 ) THEN
SET total_pages = CEIL(count1/in_limit);
ELSE
SET total_pages = 0;
END IF;
IF (in_page > total_pages) THEN
SET in_page=total_pages;
END IF;
SET out_start_limit = in_limit * in_page - in_limit;
回答by Axe
pagination without statements:
没有语句的分页：
create PROCEDURE test(
IN first_rec integer,
IN rec_count integer
)
BEGIN
-- return --
SET @rownum=0;
SELECT * FROM (
SELECT
user.*, @rownum:=@rownum+1 AS rn FROM user
) t WHERE rn>=first_rec and rn<first_rec+rec_count;
END;;
回答by Hitesh Modha
Simple solution
简单的解决方案
CREATE PROCEDURE `some_proc` (
IN _START INTEGER,
IN _LIMIT INTEGER
)
BEGIN
PREPARE STMT FROM
" SELECT * FROM products LIMIT ?,? ";
SET @START = _START;
SET @LIMIT = _LIMIT;
EXECUTE STMT USING @START, @LIMIT;
DEALLOCATE PREPARE STMT;
END $$
Try prepare statement in stored procedure.
在存储过程中尝试准备语句。
