MYSQL: Deutsche Kollation mit Zeichensatz utf8?

[TobAd]

Hallo zusammen!

Folgendes Problem: In meiner MYSQL-Datenbank(5.1.51) habe ich u.a. Namen von Personen mit Umlauten gespeichert. Der Zeichensatz der Datenbank, sowie der entsprechenden Tabellen ist utf8.

Wenn ich die Daten alphabetisch sortiert exportieren möchte, führt das zu dem Problem: Es gibt scheinbar keine Kollation, die nach (einer) deutschen Regel(n) korrekt sortiert. Die Kollationen latin1_german1_ci, latin2_german2_ci sind laut Fehlermeldung nicht mit dem Zeichensatz utf8 kompatibel.

Kann es sein, dass es keine Kollation für deutsche Sortierregeln gibt? Wie kann denn das sein? Momentan benutze ich die tschechische Kollation, die kommt dem wenigstens eingermaßen gleich und sie ordnet Groß- und Kleinbuchstaben denselben Rang zu.

Gibt es ein Workaround, falls es wirklich keine passende Kollation gibt? Muss ich die Daten erst exportieren und dann "von Hand/php" sortieren?

Danke für die Rückmeldungen!

TobAd

[dedlfix]

Hi!

Es gibt scheinbar keine Kollation, die nach (einer) deutschen Regel(n) korrekt sortiert.

Nur scheinbar.

Lo!

[Gunnar Bittersmann]

@@dedlfix:

nuqneH

Es gibt scheinbar keine Kollation, die nach (einer) deutschen Regel(n) korrekt sortiert.

Nur scheinbar.

Scheinbar doch nicht. Nicht für UTF-8.

Hinweis: Es geht um Namen. Da soll ä als ae, ö als oe, ü als ue einsortiert werden.

Qapla'

[dedlfix]

Hi!

Es gibt scheinbar keine Kollation, die nach (einer) deutschen Regel(n) korrekt sortiert.
Nur scheinbar.
Scheinbar doch nicht. Nicht für UTF-8.
Hinweis: Es geht um Namen. Da soll ä als ae, ö als oe, ü als ue einsortiert werden.

Diese spezielle Anforderung hat er bisher nicht gestellt. Wie auch immer, in der Form entspräche das latin1_german2 und ist tatsächlich nicht mit utf8_* zu haben. Aber man könnte das bei der Abfrage umkodieren. Vorher muss er jedoch erst einmal sein Zeichenkodierungsproblem lösen.

Lo!

[Der Martin]

Hallo,

Es gibt scheinbar keine Kollation, die nach (einer) deutschen Regel(n) korrekt sortiert.
Nur scheinbar.
Scheinbar doch nicht. Nicht für UTF-8.
Hinweis: Es geht um Namen. Da soll ä als ae, ö als oe, ü als ue einsortiert werden.

wer kommt denn auf so eine Idee?
Auch wenn ae,oe,ue die Ersatzschreibweise für ä,ö,ü ist, soll das in der Ersatzschreibweise verwendete 'e' doch nicht die Sortierreihenfolge beeinflussen. IMHO sollte eine Sortierung entweder streng nach dem Zeichencode gemäß Unicode erfolgen, oder a,ä,á,à,å im Hinblick auf die Sortierung alle als identisch betrachten (wobei ersteres zwar mein Favorit ist, für die Nicht-EDVler aber schwer nachvollziehbar wäre). Es ergäbe sich damit die aus meiner Sicht korrekte und intuitive Sortierfolge:

Land
 Länge
 Lanze
 Laos
 Lappen
 Lärm
 Last
 Lästerei
 Latte
 ...

Alles andere würde mich doch sehr irritieren.

So long,
 Martin

[Gunnar Bittersmann]

@@Der Martin:

nuqneH

Hinweis: Es geht um Namen. Da soll ä als ae, ö als oe, ü als ue einsortiert werden.

wer kommt denn auf so eine Idee?

?? Hast du den entscheidenden Hinweis übersehen?

Natürlich ist diese Idee bei Namen äußerst sinnvoll. Sie findet auch im Telefonbuch Verwendung.

Müllers und Muellers stehen zusammen:

Müller, Alicia
Mueller, Bob
Müller, Carola
Mufflon, Eva

nicht durch andere Namen getrennt:

Mueller, Bob
Mufflon, Eva
Müller, Alicia
Müller, Carola

Die Wörterbuchsortierung (ä wie a, ö wie o, ü wie u) macht bei Namensverzeichnissen wenig Sinn.

Alles andere würde mich doch sehr irritieren.

Nein, du wärst viel mehr irritiert, wenn du Lieschen Müller/Mueller suchst und nicht genau weißt, ob sie sich mit ü oder ue schreibt.

Qapla'

[Der Martin]

Hallo,

Hinweis: Es geht um Namen. Da soll ä als ae, ö als oe, ü als ue einsortiert werden.
wer kommt denn auf so eine Idee?
?? Hast du den entscheidenden Hinweis übersehen?

möglicherweise - und wenn, dann sehe ich ihn immer noch nicht.

Müllers und Muellers stehen zusammen:
Müller, Alicia
Mueller, Bob
Müller, Carola
Mufflon, Eva

Das würde erklären, warum ich beim Suchen in Telefonbüchern schon manchmal an der Sortierung von Umlauten verzweifelt oder beinahe gescheitert bin, weil ich "Wörz" natürlich weit hinter "Wolf" suche.

nicht durch andere Namen getrennt:
Mueller, Bob
Mufflon, Eva
Müller, Alicia
Müller, Carola

Aber in der Reihenfolge würde ich es erwarten.

Nein, du wärst viel mehr irritiert, wenn du Lieschen Müller/Mueller suchst und nicht genau weißt, ob sie sich mit ü oder ue schreibt.

Nein, denn dass man eventuell nach unterschiedlichen Schreibweisen suchen muss (Meyer vs. Maier, Schmidt vs. Schmitt), sollte man eigentlich gewöhnt sein. Das halte ich bei Müller vs. Mueller dann ebenso für selbstverständlich.
Sonst müsste man sich Gedanken über eine rein phonetische Sortierung machen, was aber wieder andere Probleme mit sich bringt - auch Namen werden teils regional unterschiedlich ausgesprochen.

Die Variante, ü wie ue zu sortieren, ist aber nichts Halbes und nichts Ganzes.

Ciao,
 Martin

[ChrisB]

Hi,

Das würde erklären, warum ich beim Suchen in Telefonbüchern schon manchmal an der Sortierung von Umlauten verzweifelt oder beinahe gescheitert bin, weil ich "Wörz" natürlich weit hinter "Wolf" suche.

Langsam sollten wir die Anekdoten aus der Reihe „Der Martin sieht es genau andersherum, als der Rest der Welt“ mal in einem Buch sammeln.

Entweder stammst du wirklich aus einem Paralleluniversum, in dem alles unserem genau diametral entgegengesetzt ausgerichtet ist, oder du hast dir zur Belustigung eine Forenpersönlichkeit geschaffen, die wo immer möglich diesen Anschein zu erwecken versucht ... zwischen diesen zwei Possibilitäten bin ich immer noch etwas unentschieden :-)

MfG ChrisB

[Der Martin]

Hallo,

Langsam sollten wir die Anekdoten aus der Reihe „Der Martin sieht es genau andersherum, als der Rest der Welt“ mal in einem Buch sammeln.

fang mal an, ich bin wirklich gespannt. Mir ist durchaus bewusst, dass da einiges zusammenkäme.

Entweder stammst du wirklich aus einem Paralleluniversum, in dem alles unserem genau diametral entgegengesetzt ausgerichtet ist

Nicht alles, aber vieles. Ich fühle mich in dieser Welt wirklich manchmal, als wäre ich im falschen Märchen.

oder du hast dir zur Belustigung eine Forenpersönlichkeit geschaffen, die wo immer möglich diesen Anschein zu erwecken versucht ...

Nö, ich bin wirklich so. Auch IRL.

Ciao,
 Martin

[ChrisB]

Hi,

Folgendes Problem: In meiner MYSQL-Datenbank(5.1.51) habe ich u.a. Namen von Personen mit Umlauten gespeichert. Der Zeichensatz der Datenbank, sowie der entsprechenden Tabellen ist utf8.

Wenn ich die Daten alphabetisch sortiert exportieren möchte, führt das zu dem Problem: Es gibt scheinbar keine Kollation, die nach (einer) deutschen Regel(n) korrekt sortiert.

http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-sets.html

MfG ChrisB

[TobAd]

Hallo!

Vielen Dank für den Link, ich habe allerdings auch nachdem ich die Kollation der Datenbank und der entsprechenden Tabellen auf utf8_unicode_ci gesetzt habe folgendes Problem:

Das ü, ö und ä werden wie a behandelt und nicht wie, u, o.

Ergebnis:
Allebätsch Tanja
Ümit Ümit
Ösman Ahmet
Ällebätsch Tanja
Bauer Felix

Es stimmt also nur bei ä... Laut des Links sollte das doch aber geschehen?

[dedlfix]

Hi!

Vielen Dank für den Link, ich habe allerdings auch nachdem ich die Kollation der Datenbank und der entsprechenden Tabellen auf utf8_unicode_ci gesetzt habe folgendes Problem:

Beides interessiert für das Sortieren nicht. Wie sind die einzelnen Felder eingestellt?

Das ü, ö und ä werden wie a behandelt und nicht wie, u, o.

Kann ich bei richtig eingestelltem Feld nicht nachvollziehen.

Lo!

[TobAd]

Wie sind die einzelnen Felder eingestellt?

Hatte die entsprechenden Felder auf utf8_general_ci. Als ich das umgestellt habe, war das Ergebnis der Abfrage:
Ösmen
Ällebätsch Tanja
Allebätsch Tanja
Ümit ümit
Bauer Florian
umit umit
Weber Karina

Das ü, ö und ä werden wie a behandelt und nicht wie, u, o.

Kann ich bei richtig eingestelltem Feld nicht nachvollziehen.

Ich auch nicht...

[TobAd]

Hallo!

Kann es sein, dass die Zeichen einfach nicht korrekt sind? Wenn ich mir die Einträge mittels phpmyadmin anschaue, kodiert der Browser die Umlaute ebenfalls falsch (nämlich mit einem A und irgendeinem Geschwurbel).

Ich habe probeweise eine neue DB mit nur einer Tabelle angelegt, in der die Einträge Abe, äbe, obe, öbe korrekt geordnet werden. Dabei waren alle Kollationsangaben auf utf8_unicode_ci.

Grüße

[dedlfix]

Hi!

Kann es sein, dass die Zeichen einfach nicht korrekt sind? Wenn ich mir die Einträge mittels phpmyadmin anschaue, kodiert der Browser die Umlaute ebenfalls falsch (nämlich mit einem A und irgendeinem Geschwurbel).

Wenn MySQL nicht mit der richtigen Zeichenkodierungsinformation versorgt wird, kann das nichts werden.
http://wiki.selfhtml.org/wiki/Themen:Zeichencodierung/MySQL

Lo!

[TobAd]

Hallo!

Sorry, ich tue mir gerade mit der Fehleranalyse etwas schwer. Ich habe keine Ahnung, wo ich ansetzen muss.

Fakt ist, dass ich keine Schwierigkeiten mit der Kodierung in meiner Anwendung habe, alle Zeichen werden korrekt dargestellt. Nur phpmyadmin tut das nicht.

Das einzige Problem liegt darin, dass die Sortierreihenfolge nicht stimmt. Wo kann ich das Problem beheben?

Liegt es
a) an der Art wie die Daten gespeichert sind?
b) an der Verbindung zum Mysql-Server?
c) an der Kollation?

Vielleicht bin ich auch schwer von Begriff?

Vielen Dank für die Geduld.

TobAd

[ChrisB]

Hi,

Fakt ist, dass ich keine Schwierigkeiten mit der Kodierung in meiner Anwendung habe, alle Zeichen werden korrekt dargestellt.

Daraus, dass es „richtig aussieht“ den Schluss zu ziehen, dass es auch richtig wäre, kann ein Fehler sein.

Nur phpmyadmin tut das nicht.

Wie hast du die Verbindungskodierung eingestellt?

MfG ChrisB

[TobAd]

Wie hast du die Verbindungskodierung eingestellt?

Die Verbindungskodierung über phpmyadmin ist auf utf8_unicode_ci, die Zeichen werden fehlerhaft dargestellt. Das ist aber nicht mein primäres Problem (auch wenn es mich interessiert, warum das nicht richtig dargestellt wird). Das primäre Problem ist die Sortierung: die ist falsch.

In meinen php-Skripten habe ich keine Verbindungskodierung eingestellt. Die Zeichen werden korrekt dargestellt. Die Sortierung ist falsch.

Was ich im wiki und sonst nirgends gefunden habe: Wie hängt denn Verbindungskodierung und Kollation zusammen? Ich dachte, die Abfrage wird in MYSQL intern gemacht, in ein Paket gepackt und zu php geschickt und zwar schon sortiert? Dann muss doch der Fehler schon vorliegen, bevor überhaupt ein Datenbit über eine Verbindung mit eine gewissen Kodierung verschickt wurde?

Grüße,
TobAd

[dedlfix]

Hi!

Die Verbindungskodierung über phpmyadmin ist auf utf8_unicode_ci, die Zeichen werden fehlerhaft dargestellt. Das ist aber nicht mein primäres Problem (auch wenn es mich interessiert, warum das nicht richtig dargestellt wird). Das primäre Problem ist die Sortierung: die ist falsch.

Nein, dein primäres Problem ist die nicht ausgehandelte Verbindungskodierung. Daraus resultiert, dass MySQL die Zeichen nicht richtig interpretieren kann, was wiederum dazu führt, dass die Sortierung nicht passt.

Üblicherweise macht der phpMyAdmin alles richtig. Man kann dort im Prinzip nichts verkehrtes einstellen. Und wenn der PMA alles richtig anzeigt, machen auch andere Anwendungen alles richtig.

In meinen php-Skripten habe ich keine Verbindungskodierung eingestellt. Die Zeichen werden korrekt dargestellt. Die Sortierung ist falsch.

Stell die Verbindungskodierung ein, dann kann MySQL richtig arbeiten.

Was ich im wiki und sonst nirgends gefunden habe: Wie hängt denn Verbindungskodierung und Kollation zusammen?

Kodierung und Kollation hängt insofern zusammen, als dass nur bei richtig kodierten Daten die Kollation richtig arbeiten kann.

Ich dachte, die Abfrage wird in MYSQL intern gemacht, in ein Paket gepackt und zu php geschickt und zwar schon sortiert? Dann muss doch der Fehler schon vorliegen, bevor überhaupt ein Datenbit über eine Verbindung mit eine gewissen Kodierung verschickt wurde?

MySQL kann nicht richtig arbeiten, wenn es zur ausgehandelten oder Default festgelegten Kodierung keine passenden Daten gesendet bekommt. Die fehlerhafte Sortierung ist ein Folgefehler.

Lo!

[TobAd]

Stell die Verbindungskodierung ein, dann kann MySQL richtig arbeiten.

Mit folgender Einstellung:
mysql_query('SET NAMES ISO-8859-1', $dbconnect);
werden die Zeichen korrekt angezeigt. Die Sortierung ist aber immer noch falsch.
Mit mysql_query('SET NAMES utf8', $dbconnect); werden die Zeichen nicht korrekt angezeigt und die Sortierung ist auch falsch.

Eine grundsätzliche Frage: Mit mysql_query('SET NAMES utf8', $dbconnect); lege ich doch fest, welche Kodierung zu Grunde gelegt wird, wenn ein Client mit dem Server agiert.
Eine Abfrage wie

  
SELECT vorname, nachname  
FROM name  
ORDER BY nachname, vorname  

hat nun keine Umlaute, d.h. der Mysql-Server wird die Anfrage verstehen und ausführen und dann nur noch auf interne Parameter zurückgreifen, nämlich, um zu sortieren. Dann schickt er sie zurück, dann kann eine fehlerhafte Verbindungskodierung dafür sorgen, dass die Daten nicht richtig angezeigt werden. Aber auch, dass sie nicht richtig sortiert sind?

Wenn das Kind geboren ist, geb ich einen aus...

[dedlfix]

Hi!

Stell die Verbindungskodierung ein, dann kann MySQL richtig arbeiten.
Mit folgender Einstellung:
mysql_query('SET NAMES ISO-8859-1', $dbconnect);
werden die Zeichen korrekt angezeigt. Die Sortierung ist aber immer noch falsch.

Das kann auch eine Verkettung von mehreren Fehlern sein. Du schaust dir sicher nicht die vom DBMS empfangenen Bytes direkt an, sondern schickst sie an einen Browser. Auch hier muss die Zeichenkodierung korrekt mitgeteilt werden. Nicht dass da nur zufällig was gewolltes rauskommt. Aber lassen wir mal diese Baustelle beiseite und betrachten nur den MySQL-Teil: Wie hattest du die Verbindungskodierung eingestellt, als du die Daten eingetragen lassen hast?

ISO-8859-1 ist kein gültiger Wert für SET NAMES. Das ergibt nur eine Fehlermeldung, wenn du sie auswerten würdest. Latin1 (auch kleingeschrieben) wäre richtig.

Mit mysql_query('SET NAMES utf8', $dbconnect); werden die Zeichen nicht korrekt angezeigt und die Sortierung ist auch falsch.

Betrachte das was MySQL sendet direkt, indem du die Daten echo urlencode($feldinhalt); ausgibst. Dann siehst du, was du geliefert bekommst. Wenn du latin1 einstellst, musst du pro Umlaut einmal %xx bekommen. Wenn du das zweimal bekommst, hat MySQL falsche kodierte Daten im Bauch. Bei UTF-8 müssen pro Umlauf zwei %xx-Sequenzen kommen. Wenn du ein Fragezeichen oder vier Sequenzen bekommst, ist wieder was falsch.

Eine grundsätzliche Frage: Mit mysql_query('SET NAMES utf8', $dbconnect); lege ich doch fest, welche Kodierung zu Grunde gelegt wird, wenn ein Client mit dem Server agiert.

Ja. Wenn diese Kodierung aber nicht mit der Feldkodierung übereinstimmt, kodiert MySQL die Daten entsprechend um.

Eine Abfrage wie
SELECT vorname, nachname [...]
hat nun keine Umlaute, d.h. der Mysql-Server wird die Anfrage verstehen und ausführen und dann nur noch auf interne Parameter zurückgreifen, nämlich, um zu sortieren. Dann schickt er sie zurück, dann kann eine fehlerhafte Verbindungskodierung dafür sorgen, dass die Daten nicht richtig angezeigt werden. Aber auch, dass sie nicht richtig sortiert sind?

Nein. Zur richtigen Sortierung müssen sie richtig gespeichert sein und dazu vorher richtig kodiert und ausgehandelt gesendet worden sein.

Lo!

[TobAd]

Aber lassen wir mal diese Baustelle beiseite und betrachten nur den MySQL-Teil: Wie hattest du die Verbindungskodierung eingestellt, als du die Daten eingetragen lassen hast?

Ich habe MySQL nie mitgeteilt, welche Kodierung ich verwende, ich stoße zum ersten Mal auf diese Möglichkeit

Latin1 (auch kleingeschrieben) wäre richtig.

Wenn ich latin1 als Verbindungskodierung benutze werden die Zeichen korrekt dargestellt, die Sortierung ist noch immer falsch.

Betrachte das was MySQL sendet direkt, indem du die Daten echo urlencode($feldinhalt); ausgibst. Dann siehst du, was du geliefert bekommst. Wenn du latin1 einstellst, musst du pro Umlaut einmal %xx bekommen. Wenn du das zweimal bekommst, hat MySQL falsche kodierte Daten im Bauch. Bei UTF-8 müssen pro Umlauf zwei %xx-Sequenzen kommen. Wenn du ein Fragezeichen oder vier Sequenzen bekommst, ist wieder was falsch.

Ausschnitt aus dem Ergebnis (mit latin1):
B%C3%B6hme - Becker - Carl - D%C3%B6rr - Feldmann
Ausschnitt aus dem Ergebnis (mit utf8):
B%C3%83%C2%B6hme - Becker - Carl - D%C3%83%C2%B6rr - Feldmann
Eigentlich sollte es heißen: Böhme, Becker, Carl, Dörrm, Feldmann (in korrekter Reihenfolge natürlich Becker, Böhme...)

Heißt das, dass die Daten nicht einheitlich kodiert wurden?

Und wenn ja, kann ich den Datenbestand auf eine einheitliche Kodierung bringen?

Vielen Dank,
TobAd

[dedlfix]

Hi!

Wenn ich latin1 als Verbindungskodierung benutze werden die Zeichen korrekt dargestellt, die Sortierung ist noch immer falsch.

Ja, weil du zwar UTF-8-kodierte Daten gesendet hast - damals beim Eintragen - und MySQL annahm, es wäre Latin1. Die Sortierung funktioniert von selbst richtig, wenn du das Kodierungsproblem gelöst bekommst. Leg dein Augenmerk darauf und lass die Sortierung beiseite.

Ausschnitt aus dem Ergebnis (mit latin1):
B%C3%B6hme - Becker - Carl - D%C3%B6rr - Feldmann
Ausschnitt aus dem Ergebnis (mit utf8):
B%C3%83%C2%B6hme - Becker - Carl - D%C3%83%C2%B6rr - Feldmann
Eigentlich sollte es heißen: Böhme, Becker, Carl, Dörrm, Feldmann (in korrekter Reihenfolge natürlich Becker, Böhme...)

Das bestätigt die Theorie. MySQL liefert dir die Daten so wie es sie bekommen hat - mit zwei Latin1-Zeichen pro damals UTF-8-kodiertem Umlaut. Aus Sicht deiner Anwendung sieht das wie UTF-8 aus.

Bei UTF-8 als Verbindungskodierung werden die zwei Latin1-Zeichen nach UTF-8 umkodiert und das ergibt dann je zwei Byte pro Latin1-Zeichen, also insgesamt vier.

Heißt das, dass die Daten nicht einheitlich kodiert wurden?

Sie wurden zwar einheitlich behandelt, aber falsch interpretiert - als Latin1 und nicht als UTF-8. Und damit kann MySQL sie unter anderem nicht korrekt sortieren.

Und wenn ja, kann ich den Datenbestand auf eine einheitliche Kodierung bringen?

Wenn es nur Testdaten sind, dann lösch sie und fang nochmal mit gesetzter Verbindungskodierung nach jedem Connect an.

Wenn sie erhalten bleiben sollen, dann exportier sie als Latin1. Das kann der phpMyAdmin nur, wenn seinem PHP die iconv-Extension zur Verfügung steht, ansonsten nimmt er immer UTF-8 - und du erhältst (siehe oben) eine doppelte UTF-8-Kodierung. In dem Fall müssen sie in einem Texteditor als UTF-8 eingelesen werden und als ISO-8859-1 gespeichert werden. Nun hast du echte UTF-8-Daten entweder aus dem Latin1-Export oder aus der Editor-Konvertierung. Im Kopf des Dumps könnte sich auch noch eine /*auskommentierte*/ SET NAMES ...-Zeile befinden, die gelöscht werden muss. Nun kannst du einen Import ausführen, dabei aber UTF-8 als Kodierung angeben. Wenn der PMA anschließend alles richtig anzeigt, hast du gewonnen.

Den ganzen Vorgang am besten mit kopierten Tabellen ausführen, nicht dass noch mehr kaputt geht.

Lo!

[TobAd]

Wenn der PMA anschließend alles richtig anzeigt, hast du gewonnen.

Gewonnen! Super und vielen Dank, dedlfix! Die Umlaute werden korrekt angezeigt, die Sortierreihenfolge ist korrekt. Habe nun als Standardverbindungskodierung utf8 eingestellt. Was für eine schwere Geburt... Nochmals danke für die Geduld!

Grüße,
TobAd