如何使用WHERE IN mysql存储过程

How do I pass an array and use WHERE IN inside stored procedure?

如何传递数组并在存储过程中使用WHERE IN？

Do i need to concatenate input string or something ?

我需要连接输入字符串或什么？

Lets say

让我们说

DELIMITER $$
DROP PROCEDURE IF EXISTS `abc`.`table1`$$
CREATE PROCEDURE  `abc`.`test`
(IN somestring VARCHAR(255))
BEGIN
    SELECT * FROM abc.table1 
    WHERE flight_type IN somestring
END $$
DELIMITER ;

3 个解决方案

#1

You can use the string concatenation and the PREPARE statement to run dynamically built queries.

您可以使用字符串连接和PREPARE语句来运行动态构建的查询。

somestring must be constructed in a valid SQL format like '1','2','3'

somestring必须以有效的SQL格式构造，如'1'，'2'，'3'

DELIMITER $$
DROP PROCEDURE IF EXISTS `abc`.`table1`$$
CREATE PROCEDURE  `abc`.`test`
(IN somestring VARCHAR(255))
BEGIN
    @s=CONCAT("
    SELECT * FROM abc.table1 
    WHERE flight_type IN (",somestring,");")
    PREPARE stmt FROM @s;
    EXECUTE @s;
END $$
DELIMITER ;

#2

You can use FIND_IN_SET() if somestring is formatted a,b,c,d:

如果somestring的格式为a，b，c，d，则可以使用FIND_IN_SET（）：

SELECT *
FROM abc.table1
WHERE FIND_IN_SET(flight_type, somestring)

#3

This should work as long as somestring is of the form

只要somestring是形式的，这应该工作

"(item1, item2, item3, ... )"

if not, you could format it accordingly inside of your SP but I'm not sure what the best practice is.

如果没有，你可以在你的SP内部相应地格式化它，但我不确定最佳做法是什么。

#1