Verschieben Sie eine Varchar-Spalte mit CSV-Format in eine andere Tabelle

Question

Die Datenbank, die ich vor 15 Jahren erstellt habe, war schlecht gestaltet. Ich habe eine Varchar-Spalte mit durch Kommas getrennten Zeichenfolgen: „5,8,13“. Die Zeichenfolge enthält keine Leerzeichen und nachgestellten Kommas, die Anzahl der Werte ist jedoch variabel. Ich möchte eine neue Tabelle erstellen und diese Werte gepaart mit der Zeilen-ID in die neue Tabelle verschieben. Unter der Annahme, dass der obige Wert aus der Zeile mit der ID 7 stammt, sind das Ergebnis 3 Zeilen:

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

Wenn man sich den vorhandenen Datensatz ansieht, weist die Spalte Werte im Bereich von 1 bis 6 auf. Ich habe einen Thread gefunden, der zeigt, wie man die Funktion zum Teilen von Zeichenfolgen verwendet: Wert von einem Feld in zwei aufteilen

Aber ehrlich gesagt weiß ich nicht, wie ich das in einen einzelnen Einfügevorgang übersetzen soll. Wenn dies keine einfache Aufgabe ist, schreibe ich einfach ein PHP-Skript, auch wenn es viele Einfügeanweisungen enthält.

Zur weiteren Erläuterung finden Sie hier zwei Tabellen:

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
)

Tabelle_A sind die vorhandenen Daten, wobei die Daten in der Plattformspalte die durch Kommas getrennten Zahlen „3,45“ oder „56,4“ sind. Diese Zahlen verweisen auf einen Index in einer anderen Tabelle. Ja, ich weiß, dass es ein schlechtes Design ist, zum Glück habe ich seitdem einen besseren Weg gelernt. Ich möchte die Zahlen aus der Plattformzeichenfolge von Table_A analysieren und sie zusammen mit der ID der Zeile von Table_A in Table_B einfügen.

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