将 LIMIT 作为参数传递给 MySQL sproc

Question

我正在创建一个分页类，需要将两个参数传递给我的 MySQL 存储过程作为 LIMIT 子句。

我将它们作为 INT 传递并尝试类似的操作，

SELECT *
FROM
`MyTable`
LIMIT
MyFirstParamInt, MySecondParamInt

但当我尝试保存存储过程时，它会给我一个错误。有没有办法做到这一点，我只是想念？或者我是否必须评估整个查询并执行它？

Answer 1

在 5.5.6 之前，LIMIT 无法在 MySQL 存储过程中进行参数化。您需要动态构建查询并执行它。

在 5.5.6 及更高版本中，您可以将存储过程参数作为参数传递给 LIMIT 和 OFFSET，只要它们是 INTEGER 即可。

Answer 2

我刚刚找到了一个可能有帮助的解决方案。
在存储过程中使用声明的变量并将它们设置为您的参数，

例如。

 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

Answer 3

来自 http://dev.mysql.com/doc/refman/5.1/ en/select.html:

LIMIT 子句可用于限制 SELECT 语句返回的行数。 LIMIT 接受一个或两个数字参数，它们必须都是非负整数常量（使用准备好的语句时除外）。

以下是准备好的语句示例，可能会对您有所帮助：

SET @skip=1;
SET @rows=5;

PREPARE STMT FROM 'SELECT * FROM table LIMIT ?, ?';
EXECUTE STMT USING @skip, @rows;

Answer 4

以下内容在 MySQL 5.5.35 中运行良好。它也适用于另一个过程，其中在 DECLARE 中使用相同的 SELECT 。 。 。 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;

Answer 5

不带语句的分页：

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;;

Answer 6

最好的分页示例可以帮助您

调用 user_list(v_private_key,v_user_id,v_pageIndex,v_limit,v_image_path,
@o_rec_count，
@o_错误代码，
@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";

计算页面索引 SP

CREATE PROCEDUREcalculate_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; 

Answer 7

DELIMITER &&  
CREATE PROCEDURE SP_ALL( pageno int, pagesize int)    
BEGIN    
declare pg int;
declare ps int;
set pg = ((pageno - 1) * PageSize);
set ps = (pageno * pagesize);
    SELECT * FROM Users where IsActive = true limit ps offset pg;     
END &&  
DELIMITER ; 

Answer 8

简单的解决方案

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 $ 

尝试在存储过程中准备语句。