Вот решение с использованием подготовленного оператора:
DROP TABLE IF EXISTS concatenatedVals;
CREATE TABLE concatenatedVals(`key` INTEGER UNSIGNED, concatenatedValue CHAR(255));
DROP TABLE IF EXISTS splitVals;
CREATE TABLE splitVals(`key` INTEGER UNSIGNED, splitValue CHAR(255));
INSERT INTO concatenatedVals VALUES (100, 'abc,def,ghi'), (200, 'jkl,mno,pqr');
SELECT * FROM concatenatedVals;
SET @VKey := '';
SET @VExec := (SELECT CONCAT('INSERT INTO splitVals VALUES', TRIM(TRAILING ',' FROM GROUP_CONCAT(CONCAT('(', @VKey:= `key`, ', \'', REPLACE(concatenatedValue, ',', CONCAT('\'), (', @VKey, ', \'')), '\'),') SEPARATOR '')), ';') FROM concatenatedVals);
PREPARE stmt FROM @VExec;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT * FROM splitVals;
Выходы:
SELECT * FROM splitVals;
+------+------------+
| key | splitValue |
+------+------------+
| 100 | abc |
| 100 | def |
| 100 | ghi |
| 200 | jkl |
| 200 | mno |
| 200 | pqr |
+------+------------+
6 rows in set (0.00 sec)
Дайте знать, если у вас появятся вопросы.
Что касается вопроса, как я могу учитывать сценарии, в которых количество строк в моей исходной таблице означает, что подготовленный оператор превышает длину max-concat, см. следующий пример. Поскольку здесь используется цикл WHILE
, он должен находиться внутри хранимой процедуры. Это можно адаптировать, чтобы разрешить имена таблиц и имена столбцов в качестве аргументов, используя дополнительные подготовленные операторы CONCAT
AND для динамического создания и выполнения команд. Однако сейчас измените имена таблиц и столбцов из моего примера, чтобы они соответствовали вашим данным, и все должно работать нормально.
DROP TABLE IF EXISTS concatenatedVals;
CREATE TABLE concatenatedVals(`key` INTEGER UNSIGNED, concatenatedValue CHAR(255));
DROP TABLE IF EXISTS splitVals;
CREATE TABLE splitVals(`key` INTEGER UNSIGNED, splitValue CHAR(255));
INSERT INTO concatenatedVals VALUES (100, 'abc,def,ghi'), (200, 'jkl,mno,pqr'),(300, 'abc,def,ghi'), (400, 'jkl,mno,pqr'),(500, 'abc,def,ghi'), (600, 'jkl,mno,pqr'),(700, 'abc,def,ghi'), (800, 'jkl,mno,pqr'),(900, 'abc,def,ghi'), (1000, 'jkl,mno,pqr');
SELECT * FROM concatenatedVals;
DELIMITER $
DROP PROCEDURE IF EXISTS loopStringSplit$
CREATE PROCEDURE loopStringSplit()
BEGIN
DECLARE VKeyMaxLength, VConcatValMaxLength, VFixedCommandLength, VVariableCommandLength, VSelectLimit, VRowsToProcess, VRowsProcessed INT;
SET VFixedCommandLength = CHAR_LENGTH(CONCAT('INSERT INTO splitVals VALUES;'));
SET VKeyMaxLength = (SELECT MAX(CHAR_LENGTH(`key`)) FROM concatenatedVals);
SET VConcatValMaxLength = (SELECT MAX(CHAR_LENGTH(concatenatedValue)) FROM concatenatedVals);
SET VVariableCommandLength = CHAR_LENGTH('(,\'\')');
SET VSelectLimit = FLOOR((@@group_concat_max_len - VFixedCommandLength) / (VKeyMaxLength + VConcatValMaxLength + VVariableCommandLength));
SET VRowsToProcess := (SELECT COUNT(*) FROM concatenatedVals);
SET VRowsProcessed = 0;
SELECT VRowsProcessed, VRowsToProcess, VSelectLimit;
WHILE VRowsProcessed < VRowsToProcess DO
SET @VKey := '';
SET @VExec := (SELECT CONCAT('INSERT INTO splitVals VALUES', TRIM(TRAILING ',' FROM GROUP_CONCAT(CONCAT('(', @VKey:= `key`, ', \'', REPLACE(concatenatedValue, ',', CONCAT('\'), (', @VKey, ', \'')), '\'),') SEPARATOR '')), ';') FROM (SELECT * FROM concatenatedVals LIMIT VRowsProcessed, VSelectLimit) A);
SELECT @VExec;
PREPARE stmt FROM @VExec;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET VRowsProcessed = VRowsProcessed + VSelectLimit;
SELECT CONCAT('Processed rows: ', VRowsProcessed);
END WHILE;
END$
DELIMITER ;
CALL loopStringSplit();
SELECT * FROM splitVals;
С уважением,
Джеймс
person
James Scott
schedule
11.08.2017