So konvertieren Sie eine Spalte mit durch Kommas getrennten Werten in Spalten in MySQL

So konvertieren Sie eine Spalte mit durch Kommas getrennten Werten in Spalten in MySQL

Vorwort

Manchmal stößt man auf Geschäftstabellen, die nicht dem Entwurfsmuster der ersten Normalform folgen. Das heißt, mehrere Attributwerte werden in einer Spalte gespeichert. Wie in der folgenden Tabelle gezeigt

pk Wert
1 ET,AT
2 AT,BT
3 AT,DT
4 DT, CT, AT

Es gibt im Allgemeinen zwei allgemeine Anforderungen (Testdaten finden Sie am Ende des Artikels):

1. Holen Sie sich alle nicht wiederkehrenden Werte, wie

Wert
BEI
BT
CT
DT
ET

Das SQL lautet wie folgt:

Wählen Sie deutlich (Teilstring_index (Teilstring_index (a.col,',',b.help_topic_id+1),',',-1))
aus
 (select group_concat(distinct `value`) as col from `row_to_col`) als
verbinden
 mysql.help_topic als b
auf b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,',',''))+1)

2. Zeigen Sie jeden Wert und den entsprechenden Primärschlüssel an, beispielsweise

pk Wert
1 ET
1 BEI
2 BEI
2 BT
3 BEI
3 DT
4 DT
4 CT
4 BEI

Das SQL lautet wie folgt:

wähle a.pk, Teilstring_index(Teilstring_index(a.col,',',b.help_topic_id+1),',',-1)
aus
 (wählen Sie `value` als col,pk aus `row_to_col`) als
verbinden
 mysql.help_topic als b
auf b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,',',''))+1)

Umsetzungsideen:

Anforderung 1:

1. Verwenden Sie die Funktion group_concat, um die Werte der Wertespalte zu einer durch Kommas getrennten Zeichenfolge zu verketten, und verwenden Sie dann die Funktion substring_index, um die Zeichenfolge abzufangen
2. Über die Funktion substring_index müssen wir wissen, wie viele Kommas in der Zeichenfolge enthalten sind und wo sich jedes Komma befindet
3. Anzahl der Kommas = char_length(string)-char_length(replace(string,',',''))
4. Kommaposition = mysql.help_topic.id < Anzahl Kommas [+1]
5. Verwenden Sie abschließend die Funktion distinct, um Duplikate aus dem abgefangenen Einzelwert zu entfernen

Beachten:
1. Die automatisch inkrementierte ID der Tabelle mysql.help_topic beginnt bei 0, daher muss die ID beim Abfangen um 1 erhöht werden. Siehe: substring_index(a.col,',',b.help_topic_id+1)
2. Wenn das letzte Zeichen der Wertespalte kein Komma ist: Die Anzahl der Kommas + 1 soll sicherstellen, dass beim Abschneiden kein Wert nach dem letzten Komma übersehen wird, d. h.: char_length(a.col) – char_length(replace(a.col,',',''))+1;
Wenn das letzte Zeichen der Wertespalte ein Komma ist: Die Anzahl der Kommas muss nicht +1 sein, sondern nur: char_length(a.col) - char_length(replace(a.col,',',''))
3. Da die ID beim Abfangen +1 sein muss, ist die Verbindung < statt <=. Siehe: b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,',',''))[+1])
4. Die Auto-Inkrement-ID der Tabelle mysql.help_topic (MySQL-Version: 5.7.21-1), der Maximalwert beträgt 636. Wenn die Anzahl der Kommas in der Zeichenfolge nach group_concat größer als dieser Wert ist, müssen Sie den Wert der Auto-Increment-ID separat verarbeiten.

Anforderung 2: Die Idee ist im Grunde die gleiche wie Anforderung 1, außer dass die endgültige Abfrage anders ist

Beteiligte Funktionen:

Länge: Gibt die Anzahl der von der Zeichenfolge belegten Bytes zurück. Dies ist die Länge des berechneten Felds. Ein chinesisches Schriftzeichen oder chinesisches Symbol wird als drei Zeichen gezählt, eine Zahl, ein Buchstabe oder ein englisches Symbol als ein Zeichen.
char_length: Gibt die Anzahl der Zeichen in einer Zeichenfolge zurück. Unabhängig davon, ob es sich um ein chinesisches Zeichen, eine Zahl, einen Buchstaben oder ein Symbol (unabhängig davon, ob Chinesisch oder Englisch) handelt, wird es als Zeichen betrachtet.
replace(str,old_string,new_string): Ersetzt alle Vorkommen von old_string im String str durch new_string.
substring_index(abgeschnittenes Feld, Schlüsselwort, Anzahl der Vorkommen des Schlüsselworts): kürzt die Zeichenfolge. Wenn die Anzahl der Vorkommen des Schlüsselworts eine negative Zahl ist, wird es rückwärts bis zum Ende der Zeichenfolge gezählt.
group_concat([DISTINCT] zu verbindende Felder (können mehrere sein, durch Kommas getrennt) [Ordnung nach Sortierfeld ASC/DESC] [Trennzeichen 'separator']):
Verkettet die Werte in derselben von „group by“ generierten Gruppe und gibt ein Zeichenfolgenergebnis zurück. Das Standardtrennzeichen ist ein Komma.

Testdaten:

Tabelle löschen, wenn `row_to_col` vorhanden ist;
CREATE TABLE `row_to_col` (
 `pk` int(11) NICHT NULL AUTO_INCREMENT,
 `Wert` varchar(255) DEFAULT NULL,
 PRIMÄRSCHLÜSSEL (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `row_to_col` VALUES ('1', 'ET,AT');
INSERT INTO `row_to_col` VALUES ('2', 'AT,BT');
INSERT INTO `row_to_col` VALUES ('3', 'AT,DT');
INSERT INTO `row_to_col` VALUES ('4', 'DT,CT,AT');

siehe:
https://blog.csdn.net/liuzhoulong/article/details/51729168
https://blog.csdn.net/ldl22847/article/details/47609727

Dies ist das Ende dieses Artikels zum Konvertieren einer Spalte mit durch Kommas getrennten Werten in Zeilen und Spalten in MySQL. Weitere Informationen zum Konvertieren durch Kommas getrennter Werte in MySQL in Zeilen und Spalten finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!

Das könnte Sie auch interessieren:
  • MySQL-Zeilen-zu-Spalten-Details
  • So konvertieren Sie Zeilen in Spalten in MySQL
  • Detaillierte Beispiele zur Konvertierung von Zeilen in Spalten und Spalten in Zeilen in MySQL
  • Verwenden der dynamischen Konvertierung von Zeilen in Spalten in einer gespeicherten MySQL-Prozedur
  • MySQL Zeile zu Spalte und Spalte zu Zeile

<<:  Detailliertes Tutorial zum PyCharm- und SSH-Remote-Access-Server-Docker

>>:  Implementierung der Optimierung für das Laden ausgewählter Mehrfachdaten in Element

Artikel empfehlen

Wesentliche bedingte Abfrageanweisungen für MySQL-Datenbanken

Inhaltsverzeichnis 1. Grundlegende Grammatik 2. F...

Teilen Sie 101 MySQL-Debugging- und Optimierungstipps

MySQL ist eine leistungsstarke Open-Source-Datenb...

Detaillierte Erklärung der Verwendung des Linux-Befehls lsof

lsof (List Open Files) ist ein Tool zum Anzeigen ...

Einführung in Container-Datenvolumes in Docker

Inhaltsverzeichnis Datenvolumen des Docker-Contai...

Detaillierte Erklärung zur Verwendung der Funktion find_in_set() in MySQL

Betrachten wir zunächst ein Beispiel: In der Arti...

Lösung für viele Zeilenumbrüche und Wagenrückläufe in MySQL-Daten

Inhaltsverzeichnis Finden Sie das Problem 1. So e...

Eine kurze Diskussion über die Berechnungsmethode von key_len in MySQL erklären

Mit dem MySQL-Befehl „explain“ können Sie die Lei...

Natives js zum Erreichen eines einfachen Karusselleffekts

In diesem Artikel wird der spezifische Code von j...

Reiner CSS-Code zum Erzielen von Fluss und dynamischen Linieneffekten

Ideen: Eine äußere Box legt den Hintergrund fest;...

So installieren Sie das Pip-Paket unter Linux

1. Laden Sie das Pip-Installationspaket entsprech...

Linux Bash: ./xxx: Fehler „Binärdatei kann nicht ausgeführt werden“

Heute habe ich einem Kunden ein kleines Tool für ...

So konfigurieren Sie nginx+php+mysql in Docker

Verstehen Sie zunächst eine Methode: Aufrufen ein...