接受带有多个参数的字符串的 MySQL 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13960760/
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
MySQL stored procedure that accepts string with multiple parameters
提问by Colin747
I want to create a stored procedure which accepts all the values in the IN parameter as a single string.
我想创建一个存储过程，它接受 IN 参数中的所有值作为单个字符串。
DELETE FROM object
WHERE Type NOT IN
('ListGrid',
'TextField',
'SpinBox',
'MenuButton',
'ListGrid',
'RadioButton',
'DropDown',
'PopUp',
'Element',
'Checkbox',
'TreeDropDown',
'TblColumn',
'Button',
'Link',
'Filter',
'TblRow',
'GridRow',
'Popup')
This is an example of one I've tried but it does not work.
这是我尝试过的一个例子，但它不起作用。
DELIMITER //
CREATE PROCEDURE deleteObjectTypes(IN p_type VARCHAR(255))
BEGIN
SET @query = CONCAT ('DELETE FROM object WHERE Type NOT IN (',p_type,')');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
I get the following error:
我收到以下错误：
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''List)' at line 1
When running this query:
运行此查询时：
CALL deleteObjectTypes("'ListGrid1','TextField1','SpinBox1','MenuButton1','ListGrid2','TextField2','SpinBox2','MenuButton2','ListGrid3','TextField3','SpinBox3','MenuButton3','ListGrid4','TextField4','SpinBox4','MenuButton4','ListGrid5','TextField5','SpinBox5','MenuButton5','ListGrid6','TextField6','SpinBox6','MenuButton6'")
回答by Francis P
You need to change the VARCHAR size to it's maximum value (or a lower significant value).
您需要将 VARCHAR 大小更改为其最大值（或较低的有效值）。
DELIMITER //
CREATE PROCEDURE deleteObjectTypes(IN p_type VARCHAR(65535))
BEGIN
SET @query = CONCAT ('DELETE FROM object WHERE Type NOT IN (',p_type,')');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
However, note that the limit is lower if you use a multi-byte character set:
但是，请注意，如果使用多字节字符集，限制会更低：
VARCHAR(21844) CHARACTER SET utf8
As seen here.
如这里所见。
回答by Mateusz
(sorry i cannot add comments too low reputation) Your procedure looks ok, maybe the problem is somewhere else? note that we have defined as a varchar 255 characters and the example you provided more than this number (291 characters)
（对不起，我不能添加评论太低的声誉）您的程序看起来不错，也许问题出在其他地方？请注意，我们已将 varchar 定义为 255 个字符，并且您提供的示例超过此数字（291 个字符）
回答by Sir Rufo
You should give this a try (shortened example):
你应该试试这个（缩短的例子）：
DELETE
FROM
object
WHERE
NOT FIND_IN_SET( Type, 'ListGrid,TextField,SpinBox,MenuButton,ListGrid' );
and with stored procedure
和存储过程
DELIMITER //
CREATE PROCEDURE deleteObjectTypes(IN p_type VARCHAR(255))
BEGIN
DELETE
FROM
object
WHERE
NOT FIND_IN_SET( Type, p_type );
END //
DELIMITER ;
CALL deleteObjectTypes( 'ListGrid1,TextField1,SpinBox1,MenuButton1,ListGrid2,TextField2,SpinBox2,MenuButton2,ListGrid3,TextField3,SpinBox3,MenuButton3,ListGrid4,TextField4,SpinBox4,MenuButton4,ListGrid5,TextField5,SpinBox5,MenuButton5,ListGrid6,TextField6,SpinBox6,MenuButton6' );
