在存储过程中使用WHERE IN（MySQL）

The goal

Passing multiple values through a single VARCHAR() parameter.

通过单个VARCHAR()参数传递多个值。

The scenario

On my application there is a procedure that get products from some table. What I need is simple: take three (or whatever) results by id if I pass it. For example: CALL getProducts('1, 2, 3');.

在我的应用程序中,有一个程序从某些表中获取产品。我需要的是简单的:如果我通过它,则通过id取三个(或其他)结果。例如:CALL getProducts('1,2,3');.

To do this, I'm working with IN clause. Take a look at my query:

为此,我正在使用IN子句。看看我的查询:

BEGIN
    Select product.Name
    From app_products As product
    Where product.Id In (1, 2, 3)
END

How can I pass those 1, 2, 3 by parameter?

如何通过参数传递1,2,3?

What I've already tried

Take a look in my query with its parameter:

用我的参数查看我的查询:

CREATE PROCEDURE `getProductsPrices`(IN `ids` VARCHAR(255))
BEGIN
    Select product.Name
    From app_products As product
    Where product.Id In (ids)
END

Now, when I call it:

现在,当我打电话给它时:

CALL `getProductsPrices`('1, 2, 3')

The return is:

回报是:

Warning: Truncated incorrect DOUBLE '1, 2, 3'

警告:截断错误DOUBLE'1,2,3'

Someone has any suggestion for me?

有人对我有任何建议吗?

Duplicated?

I think that this topic isn't duplicated because I already searched by the same subject but with no success.

我认为这个主题没有重复,因为我已经搜索了相同的主题,但没有成功。

2 个解决方案

#1

You need to set up dynamic SQL

您需要设置动态SQL

DELIMITER $$
DROP PROCEDURE `getProductsPrices` $$
CREATE PROCEDURE `getProductsPrices`(IN `ids` VARCHAR(255))
BEGIN
    SET @sql = 'Select product.Name From app_products';
    SET @sql = CONCAT(@sql,' As product Where product.Id In (',`ids`,')');
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;
END $$
DELIMITER ;

Give it a Try !!!

试一试 !!!

#2

Probably not really index friendly, but you may be able to use the find_in_set function...

可能不是真正的索引友好,但你可以使用find_in_set函数...

where find_in_set(product.id, ids)

Otherwise you need to parse the delimitted string into a temp table and join with the table.

否则,您需要将分隔的字符串解析为临时表并与表连接。

#1