將一個包含CSV格式的varchar列移到另一個表中

Question

我創建的資料庫15年前設計得不好。我有一個varchar列，其中包含逗號分隔的字串：'5,8,13'。字串中不包含空格和尾隨逗號，但值的數量是可變的。我想做的是建立一個新表，並將這些值與該行的ID配對移至新表中。假設上述值來自ID為7的行，結果將是3行：

[
   {7, 5}
   {7, 8}
   {7, 13}
]

查看現有資料集，該列的值從1到6個不等。我找到了一個線程，展示瞭如何使用拆分字串函數：Split value from one field to two

但坦白說，我不知道如何將其轉化為單一插入操作。如果這不是一個簡單的任務，那麼我將只寫一個PHP腳本，即使它會有很多插入語句。

進一步解釋，這裡有兩個表：

create table Table_A(
    id int auto_increment primary key,
    platforms varchar(255)
)

create table Table_B(
    id int auto_increment primary key,
    platform int not null
)

Table_A是現有數據，其中platforms列中的數據是以逗號分隔的數字"3,45,"或"56,4"。這些數字指向另一個表中的索引。是的，我知道這是一個糟糕的設計，幸運的是，我從那時起已經學到了更好的方法。我想從Table_A的platform字串中解析數字，並將它們與Table_A行的ID一起插入到Table_B。

Answer 1

這裡有一個小查詢來實現這個功能。它只從CSV中分割6個值。 如果一行中有更多的值，您必須更改UNION ALL

#結果儲存在singletable中。

MariaDB [bernd]> DESCRIBE singletable;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| rno       | int(11)          | YES  |     | NULL    |                |
| singleval | int(11)          | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [bernd]> SELECT * FROM singletable;
Empty set (0.00 sec)

MariaDB [bernd]> 
MariaDB [bernd]> SELECT * FROM csvtable;
+----+----------+
| id | csvvals  |
+----+----------+
|  1 | 1,3,5    |
|  2 | 2,4      |
|  3 | 6        |
|  4 | 8,9      |
|  5 | 22,21,20 |
+----+----------+
5 rows in set (0.00 sec)

MariaDB [bernd]> 
MariaDB [bernd]> 
MariaDB [bernd]> INSERT INTO singletable (rno,singleval)
    -> SELECT id as rno , SUBSTRING_INDEX( SUBSTRING_INDEX(csvvals, ',', no) ,',',-1) singleval
    -> FROM csvtable
    -> CROSS JOIN (SELECT 1 as no UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) as n
    -> WHERE no <= LENGTH(csvvals) - LENGTH(REPLACE(csvvals,',','')) +1
    -> ORDER BY id,no;
Query OK, 11 rows affected (0.01 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [bernd]> SELECT * FROM singletable;
+----+------+-----------+
| id | rno  | singleval |
+----+------+-----------+
|  1 |    1 |         1 |
|  2 |    1 |         3 |
|  3 |    1 |         5 |
|  4 |    2 |         2 |
|  5 |    2 |         4 |
|  6 |    3 |         6 |
|  7 |    4 |         8 |
|  8 |    4 |         9 |
|  9 |    5 |        22 |
| 10 |    5 |        21 |
| 11 |    5 |        20 |
+----+------+-----------+
11 rows in set (0.02 sec)

MariaDB [bernd]>