MySQL存储过程接受带有多个参数的字符串 [英] MySQL stored procedure that accepts string with multiple parameters

问题描述

我想创建一个存储过程，该存储过程接受IN参数中的所有值作为单个字符串.

I want to create a stored procedure which accepts all the values in the IN parameter as a single string.

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 ;

我收到以下错误:

#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

运行此查询时:

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

推荐答案

您需要将VARCHAR大小更改为最大值(或较低的有效值).

You need to change the VARCHAR size to it's maximum value (or a lower significant value).

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

此处所示.

这篇关于MySQL存储过程接受带有多个参数的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！