java 如何在 MySQL 存储过程中传递字符串列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16103058/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Pass a List of String in MySQL Stored Procedure?
提问by AsirC
I'm trying to pass an array as a String in MySQL Stored Procedure but it doesn't work fine.
我试图在 MySQL 存储过程中将数组作为字符串传递，但它不能正常工作。
Here's my SQL Codes:
这是我的 SQL 代码：
CREATE DEFINER=`root`@`localhost` PROCEDURE `search_equipment`(IN equip VARCHAR(100), IN category VARCHAR(255))
BEGIN
SELECT *
FROM Equipment
WHERE e_description
LIKE CONCAT("%",equip,"%")
AND e_type IN (category)
END
And here's how i call the procedure:
这是我如何调用该程序：
String type = "'I.T. Equipment','Office Supply'";
CALL search_equipment('some equipment', type);
Any ideas?
有任何想法吗？
回答by Simon at My School Portal
Your friend here is FIND_IN_SET I expect. I first came across that method in this question : also covered in this question MYSQL - Stored Procedure Utilising Comma Separated String As Variable Input
我希望你的朋友是 FIND_IN_SET。我第一次在这个问题中遇到了那个方法：也包含在这个问题MYSQL - Stored Procedure Utilizing Comma Independent String As Variable Input
MySQL documention for FIND_IN_SET is here http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set
FIND_IN_SET 的 MySQL 文档在这里http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set
So your procedure will become
所以你的程序将变成
CREATE DEFINER=`root`@`localhost`
PROCEDURE `search_equipment`(
IN equip VARCHAR(100),
IN category VARCHAR(255)
)
BEGIN
SELECT *
FROM Equipment
WHERE e_description LIKE CONCAT("%",equip,"%")
AND FIND_IN_SET(e_type,category)
END
This relies on the category string being a comma-delimited list, and so your calling code becomes
这依赖于作为逗号分隔列表的类别字符串，因此您的调用代码变为
String type = "I.T. Equipment,Office Supply";
CALL search_equipment('some equipment', type);
(p.s. fixed a typo, in your arguments you had typed categoy)
（ps 修正了一个错字，在你的论点中你输入了类别）
回答by drunken_monkey
you have to create a dynamic statment:
你必须创建一个动态语句：
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `search_equipment`(IN equip VARCHAR(100), IN category VARCHAR(255))
BEGIN
SET @s =
CONCAT('SELECT *
FROM Equipment
WHERE e_description
LIKE \'%',equip,'%\'
AND e_type IN (',category,')');
PREPARE stmt from @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt3;
END$$
回答by Raj
This helps for me to do IN condition Hope this will help you..
这有助于我做 IN 条件希望这会帮助你..
CREATE PROCEDURE `test`(IN Array_String VARCHAR(100))
BEGIN
SELECT * FROM Table_Name
WHERE FIND_IN_SET(field_name_to_search, Array_String);
END//;
Calling:
调用：
call test('3,2,1');
