UTF8, Groß-/Kleinschreibung und Umlaute

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

Re: UTF8, Groß-/Kleinschreibung und Umlaute

Hallo,

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

> `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.

> 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

Re: UTF8, Groß-/Kleinschreibung und Umlaute

=?ISO-8859-1?Q?Jürgen_Herz?= wrote:

> 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

> 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=ü.

Richtig. Das ist eine Eigenart von utf8_general_ci.

> 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.

> 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.

> Nur ist es fast so schnell wie ohne Zusatz

Sei doch froh.

> 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

Re: UTF8, Groß-/Kleinschreibung und Umlaute

Markus Popp wrote:

> 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.

>> `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.

> 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.

> 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

Re: UTF8, Groß-/Kleinschreibung und Umlaute

Axel Schwenke wrote:

>> 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).

>> 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=ü.
>
> 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.

>> Nur ist es fast so schnell wie ohne Zusatz
>
> Sei doch froh.

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

>> 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