mysql - 在 MySQL 存储例程中创建动态 WHERE 子句

Question

对这个问题的任何帮助都会很好，因为它让我发疯。我的 MYSQL 数据库中的一个表包含货币硬币的详细信息尽管那里似乎有很多动态的 WHERE 子句，我搜索了几十个似乎没有一个变量是失踪。

TABLE coin (
  coinID smallint(6) NOT NULL AUTO_INCREMENT,
  coinCountry smallint(6) NOT NULL,
  coinDenom smallint(6) NOT NULL,
  coinMinted smallint(6) NOT NULL,
  coinImageLocation varchar(128) NOT NULL,
  coinPgeTitle varchar(45) NOT NULL,
  PRIMARY KEY (coinID)
) 

我想要一个接受三个参数的存储例程中的动态“WHERE”子句其中一个参数是强制性的，并且总是有一个值，另外两个是任意的并且要么有值，要么没有值

我已经写了这个存储例程，但其中某处潜伏着问题

DELIMITER $$


CREATE DEFINER=`root`@`localhost` PROCEDURE `procFilterCoins`
(

/*******************************************************

Proc     :  Filter the coins returned by adjusting the WHERE Clause.

IN   :  iCountry (The CountryID) a mandatory value

IN   :  iDenom (The DenominationID) a discretionary value

IN   :  iMinted (The year minted) a discretionary value

OUT      :  filtered coins recordset

********************************************************/

IN iCountry int(11), 

IN iDenom int(11), 

IN iMinted int(11)
)

BEGIN

/* somewhere to hold our where clause*/

DECLARE strWhere varchar(100);


set strWhere = '((coinCountry = ';

IF iDenom > 0 and iDenom IS NOT NULL THEN

    set strWhere = concat(strWhere, iCountry, ") AND (coinDenom = ", iDenom, ')');

ELSE

    set strWhere = concat(strWhere, iCountry, ')');

END IF;


IF iMinted > 0 and iMinted IS NOT NULL THEN

    set strWhere = concat(strWhere, " AND (coinMinted = ", iMinted, '))');

ELSE

    set strWhere = concat(strWhere, ')');
END IF;


/*Extract the records via prepared view*/
SELECT * FROM vAllcoins WHERE + strwhere;


END

我使用MySQL Workbench 5.2.46.CE测试了以下数据

调用 procfiltercoins(18,null,1948);产生... WHERE ((coinCountry = 18) AND (coinMinted = 1948));没有记录返回

调用 procfiltercoins(103,4,1948,@out);产生...

WHERE ((coinCountry = 103) AND (coinDenom = 4) AND (coinMinted = 1948));

没有记录返回

调用 procfiltercoins(18,2,null,@out);产生...

WHERE ((coinCountry = 18) AND (coinDenom = 2));

没有记录返回

我已经将所有这些复制到 Access 数据库中，没有任何问题

Answer 1

尝试使用 prepared statement :

SET @query = CONCAT("SELECT * FROM vAllcoins WHERE ",strwhere);

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

不确定要对查询结果做什么。也许把它放在一个 View 中？

SET @query = CONCAT("CREATE VIEW myView as SELECT * FROM vAllcoins WHERE ",strwhere);

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

祝你好运!