mysql 添加程序,多值插入工程在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) ;

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 ;

Thanks in Advance.

解决方案

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;

Your function call would then be something like

EXEC mobile_series1('9619825525,9619825255,9324198256')