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

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

1. Holen Sie sich alle nicht wiederkehrenden Werte, wie

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

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!