MySQL存储过程接受带有多个参数的字符串

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

3 个解决方案

#1

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.

如此处所见。

#2

(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个字符)

#3

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

#1