MYSQL - 使用逗号分隔字符串作为变量输入的存储过程

Im hoping someone will be able to help. I have created my first stored procdure (nothing fancy) however im running into an issue.

我希望有人能够提供帮助。我已经创建了我的第一个存储过程(没有任何幻想)但是我遇到了一个问题。

I want to give it a string input such as 1,2,3,4,5 then it does a simple SELECT * FROM [TABLE] WHERE EAN IN (VAR);

我想给它一个字符串输入,如1,2,3,4,5然后它做一个简单的SELECT * FROM [TABLE] WHERE EAN IN(VAR);

So the stored proc looks like this:

所以存储过程看起来像这样:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE `moments`.`new_procedure`(IN var1 VARCHAR(255))
BEGIN

SELECT * FROM moments.PRODUCT WHERE EAN IN (var1);

END

Im am trying to execute it like such:

我正试图像这样执行它:

Works

call moments.new_procedure('5045318357397')

Does Not Work

不起作用

call moments.new_procedure('5045318357397,5045318357427');

This executes but doesnt not bring back any results. Is it classing the second statement as a string so its doing this:

这会执行但不会带回任何结果。它是否将第二个语句分类为字符串,所以它这样做:

select * from moments.PRODUCT WHERE EAN IN ('5045318357397,5045318357427')

and not this:

而不是这个:

select * from moments.PRODUCT WHERE EAN IN ('5045318357397','5045318357427')

How do i have to format the input in the execute query to get it to take a comma separated string as an input?

如何在执行查询中格式化输入以使其以逗号分隔的字符串作为输入?

2 个解决方案

#1

You could use:

你可以使用:

SELECT * FROM moments.PRODUCT 
WHERE FIND_IN_SET(EAN, var1)

This should work assuming it is actually comma delimited. Any other delimiting will not work in this case.

假设它实际上以逗号分隔,这应该工作。在这种情况下,任何其他分隔都不起作用。

#2

Assuming the string you passed is validated somehow and doesn't contain malicious sql, you can use prepared statements :

假设您传递的字符串以某种方式验证并且不包含恶意sql,您可以使用预准备语句:

PREPARE stmt1 FROM CONCAT('select * from moments.PRODUCT WHERE EAN IN (',var1,')');
EXECUTE stmt1;

#1