更新时间:2022-02-25 22:15:26
是的,您需要一个循环,在其中可以使用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 ;