MySQL 如何将变量传递给 IN 子句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13116042/
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
How to pass a variable to a IN clause?
提问by Thanu
Lets say I have a SP that has a SELECTstatements as follows,
假设我有一个 SP，其SELECT声明如下，
SELECT product_id, product_price FROM product
WHERE product_type IN ('AA','BB','CC');
But data goes to that INclause must be through a single variable that contains the string of values. Something link below
但是进入该IN子句的数据必须通过包含值字符串的单个变量。下面的一些链接
SELECT product_id, product_price FROM product
WHERE product_type IN (input_variables);
But its not working that way. Any idea how to do this?
但它不是那样工作的。知道如何做到这一点吗？
回答by Devart
Pass parameter value like this - 'AA,BB,CC'. Then, it is enough to use FIND_IN_SETfunction -
像这样传递参数值 - 'AA,BB,CC'。那么，使用FIND_IN_SET函数就足够了——
SELECT product_id, product_price
FROM product
WHERE FIND_IN_SET(product_type, param);
回答by solaimuruganv
回答by Daniel Adenew
passing a string using a variable was a problem assume this solution
使用变量传递字符串是一个问题假设这个解决方案
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `spTestListValues`(_list varchar(200))
BEGIN
SET @LIST=_list; -- assume this a paramter from the stored procedure
SELECT NULL AS Id, '' AS Description --insert null value to be used for list box population
UNION
(SELECT id, Description
FROM test_table
WHERE FIND_IN_SET(id,@LIST) ORDER BY Description ASC) ;
END
Calling the procedure from other query window
从其他查询窗口调用过程
call `spTestListValues`('4,5,3'); --no paramter currently for test
output
输出
ID Description
NUll
1 TEST1
4 TEST2
5 TEST3
回答by ErikHH
Try using a prepared statement: http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.html
尝试使用准备好的语句：http: //dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.html
