如何在mysql存储过程中使用WHERE [英] how to use WHERE IN mysql stored procedure

问题描述

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

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

我需要连接输入字符串吗?

Do i need to concatenate input string or something ?

让我们说

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 ;

推荐答案

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

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

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

somestring must be constructed in a valid SQL format like '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 ;

这篇关于如何在mysql存储过程中使用WHERE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！