mysql存储过程传数组

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE process_array(IN input_str VARCHAR(255))
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE value VARCHAR(255);
    WHILE (i < LENGTH(input_str) - LENGTH(REPLACE(input_str, ',', '')) + 1) DO
        SET i = i + 1;
        SET value = SUBSTRING_INDEX(SUBSTRING_INDEX(input_str, ',', i), ',', -1);
        -- 处理每个值
        SELECT * FROM your_table WHERE column = value;
    END WHILE;
END //
DELIMITER ;

-- 调用存储过程
CALL process_array('value1,value2,value3');

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE process_array(IN input_str VARCHAR(255))
BEGIN
    CREATE TEMPORARY TABLE temp_table (value VARCHAR(255));
    SET @sql = CONCAT('INSERT INTO temp_table (value) VALUES (', REPLACE(input_str, ',', '),('), ')');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 处理临时表中的数据
    SELECT * FROM your_table JOIN temp_table ON your_table.column = temp_table.value;

    DROP TEMPORARY TABLE temp_table;
END //
DELIMITER ;

-- 调用存储过程
CALL process_array('value1,value2,value3');

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE process_json_array(IN input_json JSON)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE value VARCHAR(255);
    DECLARE json_length INT DEFAULT JSON_LENGTH(input_json);

    WHILE (i < json_length) DO
        SET i = i + 1;
        SET value = JSON_UNQUOTE(JSON_EXTRACT(input_json, CONCAT('$[', i, ']')));
        -- 处理每个值
        SELECT * FROM your_table WHERE column = value;
    END WHILE;
END //
DELIMITER ;

-- 调用存储过程
CALL process_json_array('["value1", "value2", "value3"]');