UTF8, Groß-/Kleinschreibung und Umlaute

[Jürgen Herz]

Hallo,

mir liegt da ein Problem mit der Speicherung bzw. Interpretation von
Zeichen in MySQL im Magen.

Was ist zu tun damit Speicherung und Abfrage case sensitiv sind, u=u und
ü=ü, Multibyte-Zeichen auch als ein Zeichen angesehen werden und das
alles nicht auf Kosten der Performance geht?

Mit
`Titel` varchar(255) character set utf8 collate utf8_bin NOT NULL
default '',
bei DEFAULT CHARSET=utf8 - default Collation ist utf8_general_ci scheint
alles so zu funktionieren wie gewünscht.
Wobei für eine passable (ganz gefällt sie mir immer noch nicht) Sortierung
ORDER BY Titel COLLATE utf8_general_ci
nötig ist.

Aber ist das die beste Lösung?



Lange Erläuterung zum Problem:

Bis gestern hatte ich meine Textspalten wie diese hier
`Titel` varchar(255) character NOT NULL default '',
bei DEFAULT CHARSET=utf8 - default Collation ist utf8_general_ci.

Es gibt damit zwei Probleme:
1. Umlaute werden beim Einfügen übersehen. So darf nicht ein Datensatz
mit dem Unique Key 'Yürn' und eins 'Yurn' existieren. Für die Datenbank
ist anscheinend u=ü. Ebenso gibt die Abfrage SELECT Titel FROM Artikel
WHERE Titel = Yurn'; beide Sätze als Treffer aus.
2. Das Handling ist nicht case sensitiv - dabei sollte das, vor allem
für exotische Unicode-Zeichen, doch einfacher sein.

Um Abfragen doch case sensitiv zu machen, glaube ich irgendwo in der
MySQL-Doku gelesen zu haben, man solle
SELECT Titel FROM Artikel WHERE Titel COLLATE utf8_bin = 'Yurn';
nehmen. Und tatsächlich, das tut was es soll - nur ist ein SELECT damit
grauenhaft langsam (0,8 gegenüber 0,01 Sekunden) da lt. EXPLAIN der
Index nicht verwendet wird.

Jetzt steht in der MySQL-Doku (10.5.3. BINARY Operator), BINARY 'x' is
equivalent to 'x' COLLATE y, where y is the name of the binary collation
for the character set of 'x'. Müßte dann nicht
SELECT Titel FROM Artikel WHERE Titel COLLATE utf8_bin = 'Yurn';
gleich
SELECT Titel FROM Artikel WHERE Titel = BINARY 'Yurn';
sein, da ja utf8_bin die binary collation für utf8 sein sollte? Ist es
auch auf den ersten Blick vom Ergebnis her. Nur ist es fast so schnell
wie ohne Zusatz und es ist case sensitiv und u!=ü.

Leider gibt's doch ein Problem damit, denn
SELECT Titel FROM Artikel WHERE Titel LIKE BINARY 'Y_rn';
passt nur auf Yurn, nicht auf Yürn. Um das zu kriegen wird
SELECT Titel FROM Artikel WHERE Titel LIKE BINARY 'Y__rn';
(oder eben 'Y%rn') benötigt.

Wobei
SELECT Titel FROM Artikel WHERE Titel COLLATE utf8_bin LIKE 'Y_rn';
wahrscheinlich nur beide bringt weil da u und ü eben das Gleiche sind.

Grüße,
Jürgen

[Markus Popp]

Hallo,

im Grunde scheint mir, hast Du das "Problem" schon richtig erkannt.

Post by Jürgen Herz
`Titel` varchar(255) character set utf8 collate utf8_bin NOT NULL
default '',

Dies scheint mir auch die beste Lösung zu sein - in diesem Zusammenhang wäre
es auch nicht mehr erforderlich, bei den SELECT-Statements BINARY explizit
anzugeben, da mit utf8_bin mit Groß-/Kleinschreibung unterschieden wird und
auch Umlaute (im Gegensatz zu utf8_general_ci) als eigene Zeichen erkannt
werden.

Post by Jürgen Herz
SELECT Titel FROM Artikel WHERE Titel COLLATE utf8_bin = 'Yurn';
nehmen. Und tatsächlich, das tut was es soll - nur ist ein SELECT damit
grauenhaft langsam (0,8 gegenüber 0,01 Sekunden) da lt. EXPLAIN der
Index nicht verwendet wird.

Ich schätze, das liegt daran, dass MySQL durch die Konvertierung im Query
nicht mehr auf vorhandene Indizes zurückgreifen kann. Wird allerdings
utf8_bin in der Tabellendefinition gesetzt und der Zeichensatz nicht im
Query (was dadurch überflüssig sein müsste) konvertiert, sollte es wieder
funktionieren und das Query wieder so schnell laufen, wie gehabt.

Vorsicht ist bei FULLTEXT Indizes geboten, da diese grundsätzlich case
insensitive sind. Diese können (wenn ich alles richtig in Erinnerung hab) in
diesem Fall nicht mehr angewandt werden.

Ich hoffe, das hilft mal ein bisschen weiter ;-).

Markus

[Jürgen Herz]

Post by Markus Popp
im Grunde scheint mir, hast Du das "Problem" schon richtig erkannt.

Schön, das freut mich. Wie gesagt geht ja nach der Umstellung auf
utf8_bin alles. Nur wollte ich sichergehen, nicht nach einiger Zeit mit
einer gefüllten Datenbank da sitzen die plötzlich ein unerwartetes
Verhalten zeigt.

Post by Markus Popp

Post by Jürgen Herz
`Titel` varchar(255) character set utf8 collate utf8_bin NOT NULL
default '',

Dies scheint mir auch die beste Lösung zu sein - in diesem Zusammenhang wäre
es auch nicht mehr erforderlich, bei den SELECT-Statements BINARY explizit
anzugeben, da mit utf8_bin mit Groß-/Kleinschreibung unterschieden wird und
auch Umlaute (im Gegensatz zu utf8_general_ci) als eigene Zeichen erkannt
werden.

Ist es auch nicht, die Kollationen und BINARY waren nur ohne utf8_bin in
der Felddefinition nötig.

Post by Markus Popp
Ich schätze, das liegt daran, dass MySQL durch die Konvertierung im Query
nicht mehr auf vorhandene Indizes zurückgreifen kann. Wird allerdings
utf8_bin in der Tabellendefinition gesetzt und der Zeichensatz nicht im
Query (was dadurch überflüssig sein müsste) konvertiert, sollte es wieder
funktionieren und das Query wieder so schnell laufen, wie gehabt.

Hm ja, das wird's wohl sein.

Post by Markus Popp
Vorsicht ist bei FULLTEXT Indizes geboten, da diese grundsätzlich case
insensitive sind. Diese können (wenn ich alles richtig in Erinnerung hab) in
diesem Fall nicht mehr angewandt werden.

Das Volltext-Feld habe ich absichtlich nicht auf bin gesetzt, da in
diesem Fall eine generelle Case-Unabhängigkeit bei der Suche bevorzuge.
Aber danke für den Hinweis.

Danke,
Jürgen

[Axel Schwenke]

Post by Jürgen Herz
Was ist zu tun damit Speicherung und Abfrage case sensitiv sind, u=u und
ü=ü, Multibyte-Zeichen auch als ein Zeichen angesehen werden und das
alles nicht auf Kosten der Performance geht?

Verwende: DEFAULT CHARSET uft8 DEFAULT COLLATION utf8_bin

Post by Jürgen Herz
Bis gestern hatte ich meine Textspalten wie diese hier
`Titel` varchar(255) character NOT NULL default '',
bei DEFAULT CHARSET=utf8 - default Collation ist utf8_general_ci.
1. Umlaute werden beim Einfügen übersehen. So darf nicht ein Datensatz
mit dem Unique Key 'Yürn' und eins 'Yurn' existieren. Für die Datenbank
ist anscheinend u=ü.

Richtig. Das ist eine Eigenart von utf8_general_ci.

Post by Jürgen Herz
SELECT Titel FROM Artikel WHERE Titel COLLATE utf8_bin = 'Yurn';
nehmen. Und tatsächlich, das tut was es soll - nur ist ein SELECT damit
grauenhaft langsam (0,8 gegenüber 0,01 Sekunden) da lt. EXPLAIN der
Index nicht verwendet wird.

Auch richtig. Ein Index über eine Spalte verwendet deren Collation zur
Sortierung. Eine andere Collation bedeutet eine andere Sortierung und
macht damit den Index für dieses Statement wertlos.

Post by Jürgen Herz
Müßte nicht
SELECT Titel FROM Artikel WHERE Titel COLLATE utf8_bin = 'Yurn';
gleich
SELECT Titel FROM Artikel WHERE Titel = BINARY 'Yurn';
sein

Von der Logik her ist es das. Aber wohl unterschiedlich implementiert.

Post by Jürgen Herz
Nur ist es fast so schnell wie ohne Zusatz

Sei doch froh.

Post by Jürgen Herz
Leider gibt's doch ein Problem damit, denn
SELECT Titel FROM Artikel WHERE Titel LIKE BINARY 'Y_rn';
passt nur auf Yurn, nicht auf Yürn. Um das zu kriegen wird
SELECT Titel FROM Artikel WHERE Titel LIKE BINARY 'Y__rn';
(oder eben 'Y%rn') benötigt.

Das halte ich für einen Bug. Bitte reporten unter bugs.mysql.com.


XL

[Jürgen Herz]

Post by Axel Schwenke

Post by Jürgen Herz
Was ist zu tun damit Speicherung und Abfrage case sensitiv sind, u=u und
ü=ü, Multibyte-Zeichen auch als ein Zeichen angesehen werden und das
alles nicht auf Kosten der Performance geht?

Verwende: DEFAULT CHARSET uft8 DEFAULT COLLATION utf8_bin

Gut, also im Prinzip was ich gemacht habe, die Kollation bei der
Spaltendefinition auf bin (nur habe ich den Default auch general_ci
gelassen, da die Unterscheidung bei den nicht-Index-Feldern sogar
wünschenswert ist).

Post by Axel Schwenke

Post by Jürgen Herz
1. Umlaute werden beim Einfügen übersehen. So darf nicht ein Datensatz
mit dem Unique Key 'Yürn' und eins 'Yurn' existieren. Für die Datenbank
ist anscheinend u=ü.

Richtig. Das ist eine Eigenart von utf8_general_ci.

Eine ziemlich seltsame und nicht gerade erwartete Eigenschaft.
Müsste ich glatt mal schauen ob das bei utf8_swedish_ci usw. auch so ist.

Post by Axel Schwenke

Post by Jürgen Herz
Nur ist es fast so schnell wie ohne Zusatz

Sei doch froh.

Bin ich auch. Ich glaube, das "Nur" sollte "Nun" heißen.

Post by Axel Schwenke

Post by Jürgen Herz
Leider gibt's doch ein Problem damit, denn
SELECT Titel FROM Artikel WHERE Titel LIKE BINARY 'Y_rn';
passt nur auf Yurn, nicht auf Yürn. Um das zu kriegen wird
SELECT Titel FROM Artikel WHERE Titel LIKE BINARY 'Y__rn';
(oder eben 'Y%rn') benötigt.

Das halte ich für einen Bug. Bitte reporten unter bugs.mysql.com.

Was ich vergaß zu schreiben: Ich habe noch die 4.1 (mehr gibt's bei
Debian stable out-of-the-box nicht) laufen, bevor ich Bug schreie, teste
ich immer gerne die aktuelle Version.

Danke,
Jürgen