MYSQL : Undeclared variable

Question

我有两个存储过程，第一个参数带有静态，并且运行良好但是当我使用动态参数时，它给了我一个错误未声明的变量:limitvar
1/程序

`DELIMITER $$

DROP PROCEDURE IF EXISTS `gestprospects`.`recClientsFiltre` $$
CREATE  PROCEDURE `recClientsFiltre`(IN params text, in limitvar int , in offsetvar int )

BEGIN
SET @query := concat(concat("SELECT ... WHERE CONCAT(clients.id,'-') in (",params),") limit 10,10");
  PREPARE stmt FROM @query ;
  EXECUTE stmt  ;
  DEALLOCATE PREPARE stmt;

END  $$

DELIMITER ;`

2/程序

 DELIMITER $$

DROP PROCEDURE IF EXISTS `gestprospects`.`recClientsFiltre` $$
CREATE  PROCEDURE `recClientsFiltre`(IN params text, in limitvar int , in offsetvar int )

BEGIN
SET @query := concat(concat("SELECT ... WHERE CONCAT(clients.id,'-') in (",params),") limit limitvar,offsetvar");
  PREPARE stmt FROM @query ;
  EXECUTE stmt  ;
  DEALLOCATE PREPARE stmt;

END  $$

DELIMITER ;

谢谢您的回复

Answer 1

您在动态查询中包含了文字字符串 limitvar 和 offsetvar，而您希望包含它们的值。

@query := CONCAT(
    "SELECT ... WHERE CONCAT(clients.id,'-') IN (", params,")",
    " LIMIT ", limitvar, ", ", offsetvar);

注意如何 concatenate several strings在一次调用 CONCAT() 中；