MySQL将同一表中不同列的多值字符串拆分到新表中

Question

我想将一个表中的多值字符串拆分为一个由主键和拆分字符串结果组成的新表。

示例字符串:

table1.field1 (primary key) = 100 , table1.field2 = 'abc,def,ghi'

在新表(table2)中，结果应该是这样的:

         **column1** **column2**
**row1**     100        'abc' 
**row2**     100        'def' 
**row3**     100        'ghi'
**row4**     etc         etc

我知道如何拆分table1.field2，但是由于数据太大，我需要将结果自动插入到table2中。如果我手动做的话，会花很长时间。有人可以帮助我吗？

Answer 1

这是使用准备好的语句的解决方案:

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)

如果您有任何疑问，请告诉我。

关于这个问题，我如何解释源表中的行数意味着准备好的语句超过最大连接长度的情况，请参阅以下示例。因为这使用 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;

问候，

詹姆斯