Alihkan lajur varchar yang mengandungi format CSV ke jadual lain

Question

Pangkalan data yang saya buat 15 tahun lalu direka dengan buruk. Saya mempunyai lajur varchar yang mengandungi rentetan dipisahkan koma: '5,8,13'. Rentetan tidak mengandungi ruang dan koma di belakang, tetapi bilangan nilai adalah berubah-ubah. Apa yang saya mahu lakukan ialah mencipta jadual baharu dan memindahkan nilai ini berpasangan dengan ID baris ke dalam jadual baharu. Dengan mengandaikan nilai di atas datang dari baris dengan ID 7, hasilnya akan menjadi 3 baris:

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

Melihat set data sedia ada, lajur mempunyai nilai antara 1 hingga 6. Saya menjumpai urutan yang menunjukkan cara menggunakan fungsi rentetan split: Nilai pisah daripada satu medan kepada dua

Tetapi terus terang, saya tidak tahu bagaimana untuk menterjemah ini ke dalam satu operasi sisipan. Jika ini bukan tugas yang mudah, maka saya hanya akan menulis skrip PHP, walaupun ia akan mempunyai banyak penyata sisipan.

Untuk menerangkan lebih lanjut, berikut adalah dua jadual:

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 ialah data sedia ada, dengan data dalam lajur platform ialah nombor "3,45" atau "56,4" yang dipisahkan dengan koma. Nombor ini menunjukkan indeks dalam jadual lain. Ya, saya tahu ia adalah reka bentuk yang buruk, nasib baik saya telah belajar cara yang lebih baik sejak itu. Saya ingin menghuraikan nombor daripada rentetan platform Table_A dan memasukkannya ke dalam Table_B bersama ID baris Table_A.

Answer 1

Berikut ialah pertanyaan kecil untuk mencapai fungsi ini. Ia hanya membahagikan 6 nilai daripada CSV. Jika terdapat lebih banyak nilai berturut-turut anda perlu ubah UNION ALL

Hasil disimpan dalam jadual tunggal.

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]>