MySQL存储过程从列表中插入多行 [英] MySQL stored procedure insert multiple rows from list
问题描述
如何编写MySQL存储过程以从可变大小的列表中插入值?更具体地说,我需要将数据插入到一个父表中,从插入中获取ID,然后以一对多的关系将可变数量的子记录以及新ID插入到另一个表中.我的架构如下所示:
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 ;
我的想法是通过逗号分隔的字符串传递要插入表B的项目列表.值是字符串.我不确定在LIST INSERT部分中要做什么.我需要某种循环吗?到目前为止,我是否有此存储过程正确的方法?我不想进行批量处理,因为列表中可能有数百甚至数千个项目.有更好的解决方案吗?我正在使用直接JDBC.
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.
推荐答案
是的,您需要一个循环,在其中可以使用substring_index()获取列表中的值.该解决方案基于此SO主题:
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 ;
这篇关于MySQL存储过程从列表中插入多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!