Déplacer une colonne varchar contenant le format CSV vers une autre table

Question

La base de données que j'ai créée il y a 15 ans était mal conçue. J'ai une colonne varchar contenant des chaînes séparées par des virgules : '5,8,13'. La chaîne ne contient pas d'espaces ni de virgules finales, mais le nombre de valeurs est variable. Ce que je veux faire, c'est créer une nouvelle table et déplacer ces valeurs associées à l'ID de la ligne dans la nouvelle table. En supposant que la valeur ci-dessus provient de la ligne portant l'ID 7, le résultat sera de 3 lignes :

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

En regardant l'ensemble de données existant, la colonne a des valeurs allant de 1 à 6. J'ai trouvé un fil de discussion qui montre comment utiliser la fonction de fractionnement de chaîne : Diviser la valeur d'un champ à deux

Mais franchement, je ne sais pas comment traduire cela en une seule opération d'insertion. Si ce n'est pas une tâche simple, j'écrirai simplement un script PHP, même s'il comportera de nombreuses instructions d'insertion.

Pour expliquer davantage, voici deux tableaux :

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 correspond aux données existantes, où les données dans la colonne des plates-formes sont les nombres « 3,45 » ou « 56,4 » séparés par des virgules. Ces nombres pointent vers un index dans une autre table. Oui, je sais que c'est une mauvaise conception, heureusement, j'ai appris une meilleure méthode depuis. Je souhaite analyser les nombres de la chaîne de plate-forme de Table_A et les insérer dans Table_B avec l'ID de la ligne Table_A.

Answer 1

Voici une petite requête pour réaliser cette fonctionnalité. Il ne sépare que 6 valeurs du CSV. S'il y a plus de valeurs d'affilée, vous devez changer UNION ALL

Les résultats sont stockés dans une table unique.

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