MYSQL-使用逗号分隔的字符串作为变量输入的存储过程 [英] MYSQL - Stored Procedure Utilising Comma Separated String As Variable Input

问题描述

我希望有人能够提供帮助.我创建了我的第一个存储过程(没什么花哨的)，但是我遇到了问题.

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

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

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);

所以存储的过程如下:

-- --------------------------------------------------------------------------------
-- 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:

工程

call moments.new_procedure('5045318357397')

不起作用

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')

不是这个:

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?

推荐答案

您可以使用:

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.

这篇关于MYSQL-使用逗号分隔的字符串作为变量输入的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！