Mysql 拆分行到 INSERT

Question

我有一张 table 1:

id - values
12 - 124,145,135
16 - 254,33,11,456,78

...

使用 SQL，我如何拆分要插入到另一个表中的值以获得:

INSERT INTO table2 (id,cat) VALUES 12, 124;
INSERT INTO table2 (id,cat) VALUES 12, 145;
INSERT INTO table2 (id,cat) VALUES 12, 135;
INSERT INTO table2 (id,cat) VALUES 16, 254;
...

谢谢!

Answer 1

这是在 MySQL 中的实现方式:

INSERT INTO table2
SELECT id,
       REPLACE(substring(substring_index(vals, ',', i),
               length(substring_index(vals, ',', i - 1)) + 1), ',', '')
FROM (
    SELECT a.*, @i := if(@id = a.id, @i + 1, 1) i, @id := a.id
    FROM (
        SELECT *
        FROM table1 a
        INNER JOIN information_schema.global_status b ON 1 = 1
        ORDER BY a.id
    ) a
    INNER JOIN (SELECT @i := 0, @id := NULL) x
) a
WHERE i <= LENGTH(vals) - LENGTH(REPLACE(vals, ',', '')) + 1

显然，主要问题是将第二列转换为行。

这是它的工作原理(您应该从内部查询开始阅读它):

INSERT INTO table2
SELECT id,
       /* Get i-th CSV value from vals (where i ranges between 1
          and number of generated rows) */
       REPLACE(substring(substring_index(vals, ',', i),
               length(substring_index(vals, ',', i - 1)) + 1), ',', '')
FROM (
    SELECT a.*,

           /* Generate an index for each row. The index values will
              go from 1 to n (the number of generated rows) for each row
              in table1 */
           @i := if(@id = a.id, @i + 1, 1) i,

           /* We keep a reference to the previous table1.id, so that 
              we can reset the counter when we move to the next row
              in table1 */
           @id := a.id
    FROM (
        /* Generate sufficient rows
           You should have at least: Count(table1.*) x MAX_CSV_VALUES_PER_ROW rows */
        SELECT *
        FROM table1 a
        /* I used information_schema.global_status, but you may use any other table
           or even create your own temporary table:
           SELECT 1 UNION ALL SELECT 2 ... UNION ALL SELECT n */
        INNER JOIN information_schema.global_status b ON 1 = 1
        ORDER BY a.id
    ) a
    INNER JOIN (SELECT @i := 0, @id := NULL) x
) a
WHERE
    /* Keep only the relevant values; the other values are duplicates.
       We're going to keep only those values with indices that are less
       or equal to the number of CSV values in vals */
    i <= LENGTH(vals) - LENGTH(REPLACE(vals, ',', '')) + 1;