mysql存储过程行对象,MySQL存储过程从列表中插入多行

How can I write a MySQL stored procedure to insert values from a variable sized list? More specifically I need to insert data into one parent table, get the ID from the insert, and then insert a variable number of child records along with the new ID into another table in a one-to-many relationship. My schema looks something like this:

TableA:

table_a_id -- Auto Increment

counter

some_data...

TableB:

table_b_id -- Auto Increment

table_a_id -- Foreign Key Constraint

some_data_from_list...

My stored procedure so far looks like this:

DELIMITER ;;

CREATE PROCEDURE insert_group_alert(

IN _some_data_a VARCHAR(255),

IN _data_list_b TEXT,

)

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

ROLLBACK;

END;

START TRANSACTION;

INSERT INTO TableA (

some_data,

counter

)

VALUES (

_some_data_a,

1

)

ON DUPLICATE KEY UPDATE

counter = counter + 1;

SELECT last_insert_id()

INTO @newId;

LIST INSERT ???:

INSERT INTO TableB (

table_a_id, some_data

) VALUES (

@newId,

list_item,

);

END LIST INSERT ???

COMMIT;

END ;;

DELIMITER ;

My thought was to pass in a list of items to insert into table B via a comma delimited string. The values are strings. I am not sure what to do in the LIST INSERT section. Do I need a loop of some sort? Is this stored procedure I have so far the correct way to do this? I don't want to do a batch as I could potentially have hundreds or even thousands of items in the list. Is there a better solution? I am using straight JDBC.

解决方案

Yes, you need a loop, in which you can use substring_index() to get the values within the list. The solution is based on the answers from this SO topic:

DELIMITER ;;

CREATE PROCEDURE insert_group_alert(

IN _some_data_a VARCHAR(255),

IN _data_list_b TEXT,

)

BEGIN

DECLARE strLen INT DEFAULT 0;

DECLARE SubStrLen INT DEFAULT 0;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

ROLLBACK;

END;

START TRANSACTION;

INSERT INTO TableA (

some_data,

counter

)

VALUES (

_some_data_a,

1

) -- you do not really need this, since you do not provide an id

ON DUPLICATE KEY UPDATE

counter = counter + 1;

SELECT last_insert_id()

INTO @newId;

do_this:

LOOP

SET strLen = CHAR_LENGTH(_data_list_b);

INSERT INTO TableB (table_a_id, some_data) VALUES(@newId,SUBSTRING_INDEX(_data_list_b, ',', 1));

SET SubStrLen = CHAR_LENGTH(SUBSTRING_INDEX(_data_list_b, ',', 1))+2;

SET _data_list_b = MID(_data_list_b, SubStrLen, strLen); --cut the 1st list item out

IF _data_list_b = '' THEN

LEAVE do_this;

END IF;

END LOOP do_this;

COMMIT;

END ;;

DELIMITER ;