Sortierung von Strings in MySQL mit utf8_bin

[Auge]

Hallo

Eine Tabelle mit Benutzerdaten in einer MySQL-Datenbank enthält, wie überraschend, eine Spalte für den Benutzernamen user_name. Die Tabelle wurde mit CHARSET=utf8 und COLLATE=utf8_general_ci erstellt. Irgendwann ist aufgefallen, dass eine Suche nach "Änni" auch "Anni" ausspuckt. Als Konsequenz daraus wurde für die Spalte user_name die Kollation utf8_bin festgelegt.

Nun wird, wie es gewünscht ist, bei der Suche nach "Änni" nicht mehr "Anni" gefunden, schließlich sool es nicht möglich sein, dass sich mehrere Benutzer mit dem selben Namen registrieren, aber es soll auch nicht "Änni" von der Registrierung ausgeschlossen werden, weil es schon "Anni" gibt. Dies hat aber den Nebeneffekt, dass die Ausgabe, wenn sie nach der Spalte user_name sortiert wird, getrennt nach Namen mit und ohne Großschreibung aufgelistet wird. Warum das so ist, ist klar, utf8_bin unterscheidet strikt nach den Codepoints der Zeichen.

Da die Tabelle in einer Software, die sprachneutral agieren soll, enthalten ist, kann nicht einfach auf eine sprachabhängige Kollation umgestellt werden. Welche Möglichkeiten habe ich, eine Ausgabe unabhängig von Groß- und Kleinschreibung zu sortieren, ohne die Unterscheidung von z.B. "ä" und "a" aufzugeben?

Ist es eventuell sinnvoller, die Kollation der Spalte user_name auf utf8_general_ci zurückzustellen und nur für Vergleiche utf8_bin zu benutzen?

Tschö, Auge

[TS]

Hello,

hast Du mal probiert, als Collation utf8_unicode_ci zu verwenden? Das kannst Du auch in der Order-Klausel angeben.

Liebe Grüße
Tom S.

[dedlfix]

Tach!

Welche Möglichkeiten habe ich, eine Ausgabe unabhängig von Groß- und Kleinschreibung zu sortieren, ohne die Unterscheidung von z.B. "ä" und "a" aufzugeben?

SELECT name COLLATE utf8_general_ci FROM …

dedlfix.

[Auge]

Hallo

Welche Möglichkeiten habe ich, eine Ausgabe unabhängig von Groß- und Kleinschreibung zu sortieren, ohne die Unterscheidung von z.B. "ä" und "a" aufzugeben?

SELECT name COLLATE utf8_general_ci FROM …

Hmm, in der Ergebnismenge aus der Abfrage ist das Arrayelement $row['user_name'] (wie das Feld in echt™️ heißt) laut PHP-Fehlermeldung unbekannt. Erweitere ich deinen Ansatz um ein Alias, das mit dem Feldnamen identisch ist, funktioniert die Abfrage.

SELECT
  ein,
  feld,
  user_name COLLATE utf8_general_ci AS user_name,
  und,
  weitere,
  felder
FROM …
  ORDER BY user_name ASC

Der Ansatz von @TS, die Kollationsangabe in der ORDER-BY-Klausel zu notieren, funktioniert ebenfalls und sieht mMn auch schlüssiger aus, schließlich geht es ja um die Sortierung. Allerdings wird die ORDER-BY-Klausel in meinem Fall dynamisch zusammengebaut, womit eine Kollationsangabe falsch sein kann. Damit ich faul sein kann und nicht noch zusätzliche Fallunterscheidungen treffen muss, bleibe ich bei deiner Lösung. In anderen Anwendungsfällen ist TS' Lösung vermutlich ebenbürtig, oder gibt es da Fallstricke?

Danke

Tschö, Auge

[dedlfix]

Tach!

SELECT name COLLATE utf8_general_ci FROM …

Hmm, in der Ergebnismenge aus der Abfrage ist das Arrayelement $row['user_name'] (wie das Feld in echt™️ heißt) laut PHP-Fehlermeldung unbekannt.

Es ist halt eine Expression und nicht nur ein Feld. Es ist in $row, aber unter anderem Key als user_name. print_r() oder var_dump() klärt auf.

Erweitere ich deinen Ansatz um ein Alias, das mit dem Feldnamen identisch ist, funktioniert die Abfrage.

Ja, ein Alias geht natürlich, um einen freundlichen Namen zu bekommen.

Der Ansatz von @TS, die Kollationsangabe in der ORDER-BY-Klausel zu notieren, funktioniert ebenfalls und sieht mMn auch schlüssiger aus, schließlich geht es ja um die Sortierung.

Das war auch mein erster Gedanke, aber den hatte ich wieder verworfen, weil ich im Handbuch nach einem COLLATE in der ORDER BY-Syntax gesucht und nicht gefunden hatte. Aber klar, das geht dort auch, weil name COLLATE utf8_general_ci als Expression gilt und damit genauso gültig ist, wie beispielsweise RAND().

In anderen Anwendungsfällen ist TS' Lösung vermutlich ebenbürtig, oder gibt es da Fallstricke?

Kann ich mir nicht vorstellen. Die Kollation ändert an den Daten nichts, anders als beispielsweise eine andere Kodierung zu verwenden. Also sollte es egal sein, ob du das beim SELECT angibst und dann sortierst oder ob du es nur zum Sortieren berücksichtigst.

dedlfix.

[Auge]

Hallo

SELECT name COLLATE utf8_general_ci FROM …

Hmm, in der Ergebnismenge aus der Abfrage ist das Arrayelement $row['user_name'] (wie das Feld in echt™️ heißt) laut PHP-Fehlermeldung unbekannt.

Es ist halt eine Expression und nicht nur ein Feld. Es ist in $row, aber unter anderem Key als user_name. print_r() oder var_dump() klärt auf.

Erweitere ich deinen Ansatz um ein Alias, das mit dem Feldnamen identisch ist, funktioniert die Abfrage.

Ja, ein Alias geht natürlich, um einen freundlichen Namen zu bekommen.

Wenn ich wegen des an user_name angehängten . nicht die anderen Stellen, an denen der Wert unter seinem ursprünglichen Namen verwendet wird, ändern will – und das will ich nicht –, ist das die Lösung der Wahl. 😀

Tschö, Auge

[TS]

Hello,

Du hast aber schon gesehen, dass ich utf8_unicode_ci geschrieben hatte und nicht utf8_general_ci?

Wenn es darum geht, dass Ä # A behandelt wird, ist das mMn besser. Vermutlich gehört es dann aber sogar schon in die Spaltendefinition?

Liebe Grüße
Tom S.

[Auge]

Hallo

Hello,

Du hast aber schon gesehen, dass ich utf8_unicode_ci geschrieben hatte und nicht utf8_general_ci?

Ja, ist mir nicht entgangen.

Wenn es darum geht, dass Ä # A behandelt wird, ist das mMn besser. Vermutlich gehört es dann aber sogar schon in die Spaltendefinition?

Das muss ich mir noch einmal anschauen (unicode vs. general). Mit utf8_bin in der Spaltendefinition passt das schon mit den Unterscheidungen. Eine andere Kollation brauche ich ja nur für die Sortierung der Namensliste. Die Spaltendefinition selbst möchte ich, wenn es sich vermeiden lässt, nicht noch einmal anfassen müssen.

Tschö, Auge

[TS]

Hello,

kommt drauf an, ob Du auch Versalien und Gemeine unterscheinden willst. Dann wäre ut8_bin richtig. Sonst ist utf8_unicode_ci besser in der Spaltendefinition.

Liebe Grüße
Tom S.

[TS]

Hello,

ich habe das eben selber mal auf einer Version 5.5.39 ausprobiert. Eine neuere habe im Moment ich leider nicht für Tests.

Leider funktioniert der Tabellentyp/Spaltentyp utf8_unicode_ci auf einer Spalte mit unique Index nicht mit "Jylmaz" + "Jÿlmaz". Da gibt es einen Duplicate Key Error, obwohl es laut Beschreibung eigentlich funktionieren sollte.

Das könnte jetzt selbstverständlich auch an der GUI liegen. Ich will also nicht sofort MySQL den Bug zuschieben.

Mir dünkt noch eine andere Erklärung:
Ein show character set zeigt nur eine übersichtliche Auswahl.
Wenn utf8_unicode_ci nicht installiert ist, fällt das vermutlich auf utf8_general_cizurück.

Liebe Grüße
Tom S.