多值插入工程在MySQL程序? [英] multiple value insert works in mysql procedures?

问题描述

多值插入示例-可以手动运行，但不能在mySQL存储过程中使用.

Multivalue insert example - it works manually but NOT in mySQL stored procedure.

INSERT INTO input_data1(mobile) VALUES (9619825525),(9619825255),(9324198256),(9013000002),(9999999450),(9999999876) ;

我在下面的proc中的"str"字附近遇到语法错误，有人可以让我知道如何在过程中实现此多值INSERT工作吗?

i am getting syntax error near "str" word in below proc, Can any one let me know how to implement this multi value INSERT work in procedure?

DELIMITER |
DROP PROCEDURE IF EXISTS mobile_series1;
CREATE PROCEDURE mobile_series1(IN str text)  
    LANGUAGE SQL     READS SQL DATA  
    BEGIN    
         DROP TABLE IF EXISTS input_data1 ;  
         CREATE TEMPORARY TABLE input_data1 (mobile varchar(1000)) engine=memory;
         INSERT INTO input_data1(mobile) VALUES str;

    END |
DELIMITER ;

在此先感谢.

推荐答案

我没有MySQL服务器，因此可能存在语法错误和+1错误(例如，可能没有捕获列表中的最后一个，可能无法进行更新)第一项，等等，可以通过在代码中添加+1来解决问题)，但您基本上想将INSERT语句替换为此.

I don't have a MySQL server so there's probably syntax errors and +1 errors (i.e. may not be capturing the last on the list, may not progress past the first item etc, problems fixed by putting a +1 in the code), but you basically want to replace your INSERT statement with something this.

DECLARE INT _CURSOR 0;
DECLARE INT _TOKENLENGTH 0;
DECLARE VARCHAR _TOKEN NULL;

SELECT LOCATE(str, ",", _CURSOR) - _CURSOR INTO _TOKENLENGTH;

LOOP

    IF _TOKENLENGTH <= 0 THEN
        SELECT RIGHT(str, _CURSOR) INTO _TOKEN;
        INSERT INTO input_data1(mobile) VALUE _TOKEN;
        LEAVE;
    END IF;

    SELECT SUBSTRING(str, _CURSOR, _TOKENLENGTH) INTO _TOKEN;

    INSERT INTO input_data1(mobile) VALUE _TOKEN;

    SELECT _CURSOR + _TOKENLENGTH + 1 INTO _CURSOR;

    SELECT LOCATE(str, ",", _CURSOR + 1) - _CURSOR INTO _TOKENLENGTH;

END LOOP;

您的函数调用将类似于

EXEC mobile_series1('9619825525,9619825255,9324198256')

这篇关于多值插入工程在MySQL程序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！