如何将变量传递给IN子句？

Lets say I have a SP that has a SELECT statements as follows,

假设我有一个具有SELECT语句的SP，如下所示，

SELECT product_id, product_price FROM product 
WHERE product_type IN ('AA','BB','CC');

But data goes to that IN clause 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?

但它没有那样工作。知道怎么做吗？

4 个解决方案

#1

Pass parameter value like this - 'AA,BB,CC'. Then, it is enough to use FIND_IN_SET function -

像这样传递参数值 - 'AA，BB，CC'。然后，使用FIND_IN_SET函数就足够了 -

SELECT product_id, product_price
FROM product
WHERE FIND_IN_SET(product_type, param);

#2

create a user-defined function that will convert the comma separated value into table, and by join this two can get the desired result.

创建一个用户定义的函数，将逗号分隔的值转换为表，并通过join这两个可以获得所需的结果。

for more

更多

#3

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

#4

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