mysql存储过程怎么传字符串参数_如何在MySQL存储过程中传递字符串列表？

I'm trying to pass an array as a String in MySQL Stored Procedure but it doesn't work fine.

Here's my SQL Codes:

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?

解决方案

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

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)