Datenbank-Suche mit nicht-ASCII Zeichen (Kollation?)

[Linuchs]

Moin,

für die Anzeige einer DB-Tabelle mit z.Z. knapp 1200 Einträgen kann ein Filter für Lied-Titel gesetzt werden, also zunächst mal ein Wort (später mehrere Worte) als Suchbegriff.

...
WHERE titel LIKE '%schön%'

In den gefundenen Titeln markiere ich den Suchbegriff schön schön rot und kursiv:

Weniger schön ist, dass auch schön ohne Umlaut gefunden wird, was natürlich per PHP nicht markiert werden kann (weil PHP unterscheidet).

Habe recherchiert zu Datenbanksuche mit Umlaut, bin aber aus den Ergebnissen nicht schlau geworden. Und genaugenommen will ich das auch gar nicht auf Umlaute begrenzen, sondern auf alle Abarten von ASCII-Zeichen, die in europäischen Sprachen vorkommen (zunächst lateinische Schrift, griechisch, kyrillisch haben wir noch nicht) wie

å è ç ñ ...

Dann kommt noch das Problem dazu, dass schön in der DB und/oder per Suchbegriff auch schoen geschrieben werden kann. Etwa auf nicht-deutschen Tastaturen.

Da dachte ich mir, dass man checkboxen kann ö = oe / é = e / ...

Ich habe noch ein altes Lexikon. Da steht etwa: „Was unter C vermisst wird, siehe unter K und Z“. Entsprechend könnte ich formulieren: was unter ö vermisst wird, siehe unter oe und ø

Ob das Sinn macht oder eher verwirrt, muss ich später entscheiden, erstmal das Handwerk lernen.

Im Hinterkopf habe ich, dass man dem LIKE sowas hinzufügen wie utf8_unicode_ci - finde das aber nicht in der LIKE Dokumentation. Wenn ich ein DB-Feld einrichte, kann ich unter Kollation dutzende solcher Einträge wählen. Aber keine Ahnung, was sie bewirken.

Gruß, Linuchs

Edit: Habe was gefunden:

AND       titel LIKE '%schön%' COLLATE 'utf8_bin'

Nun kommt schon nicht mehr vor, aber Schön auch nicht.

[dedlfix]

Tach!

Ob das Sinn macht oder eher verwirrt, muss ich später entscheiden, erstmal das Handwerk lernen.

Ja. Leg dein konkretes Problem erstmal zur Seite und mach dich mit den generellen Gegebenheiten unter MySQL bekannt. Das wäre zumindest meine Empfehlung.

Erste Literatur dazu wäre das Kapitel zu Character Sets, Collations, Unicode. Zur Not gibt es im Archiv auch noch deutsche Handbücher von MySQL-Uraltversionen. In Version 4.5 hielt das im Wesentlichen heute noch verwendete System Einzug.

Grob gesagt, die korrekte Verwendung der Zeichenkodierung ist die Grundlage dafür, dass MySQL diverse sprachspezifische Gegebenheiten beim Sortieren und Vergleichen, beziehungsweise Stringverarbetung allgemein, berücksichtigen kann. Die jeweiligen Regeln der Sprachen sind in Kollationen definiert. Beides, Kodierung plus Kollation, bilden somit ein Gespann.

dedlfix.

[Linuchs]

Bisher habe ich beim Suchbegriff nicht zwischen Groß- und Kleinschreibung unterschieden und mache alles klein:

strtolower(trim( $_POST['such_titel'] ))

SCHÖN wird dabei zu schÖn und dieses klappt nicht:

AND       LOWER(titel) LIKE '%schÖn%' COLLATE 'utf8_bin'

Problem also nicht gelöst, sondern von SQL nach PHP verlagert.

es muss heißen

mb_strtolower(trim( $_POST['such_titel'] ))

Merkwürdig, schreibe ich doch zu Anfang des Programms

mb_internal_encoding("UTF-8");

dann klappt's mit Suchbegriff schön oder SCHÖN und auch mit É und Å

[dedlfix]

Tach!

Bisher habe ich beim Suchbegriff nicht zwischen Groß- und Kleinschreibung unterschieden und mache alles klein:

strtolower(trim( $_POST['such_titel'] ))

SCHÖN wird dabei zu schÖn

Gemäß Handbuch wird entsprechend der Locale-Einstellung gearbeitet. Zudem arbeiten die althergebrachten PHP-Stringfunktionen ohne "mb_" nicht mit UTF-8 sondern mit ISO-8859-1/Windows-1252.

dedlfix.

[localhorst]

Hi Linuchs,

• wieso unterscheidet PHP? Hast Du die richtige™ Funktion1) für die Suche benutzt?
• wieso machst Du dir das Leben künstlich schwer? Du benutzt doch für die Datenbankabfragen grundsätzlich PHP als Wrapper, oder? Da kannst Du dann doch die Obermenge mittels Datenbank ermitteln und die unerwünschten Fasttreffer mittels PHP rausschmeißen.

1. als erste Idee...

[Rolf B]

Hallo Linuchs,

die Suffixe an den Collations geben an, wie sich die Collation bezüglich der Schreibung verhält. _bin bedeutet: Binary. Andere Schreibweise ist ungleich (schön vs schÖn vs Schön). _ci bedeutet: Case Insensitive, damit ist "schön"="schÖn".

MySQL unterscheidet noch die Collations utf8_general_ci und utf8_german2_ci (in meiner Installation). Der Unterschied ist, dass german2 Duden-Regeln implementiert, damit ist sogar "schoen"="schÖn" wahr.

Die _ci Collation scheint in MySQL auch die _ai Eigenschaft zu haben (accent insensitive), d.h. "schen"="schén" ist sowohl in der utf8_general_ci als auch in der utf8_german2_ci Collation wahr. (Collations mit _cs, _ai oder _as Suffix scheint es in MYSQL nicht zu geben, die kenne ich aber aus dem MS SQL Server).

Welche Collation angewendet wird, hängt von der Definition der befragten Column ab, oder von der COLLATE Option in der jeweiligen Abfrage, zum Beispiel

SELECT * 
FROM table
WHERE ding like '%schön%' COLLATE utf8_german2_ci

Rolf

[localhorst]

Hallo Rolf,

wie ist das eigentlich mit dem Index, wenn man die Collation erst bei der Abfrage angibt? Kann der dann trotzdem benutzt werden, wenn die von der aus dem Column-Create abweicht?

Grüße
localhorst

[Rolf B]

Hallo localhorst,

dazu habe ich gerade keine Quelle. Ich würde aber annehmen, dass eine COLLATE Klausel, die dazu führt, dass nicht die Collation des/der indexierten Feldes/r verwendet werden kann, den Gebrauch des Index verhindert, da sich durch die Collation die Reihenfolge der Werte ändert und damit der BTREE des Index nicht mehr nutzbar ist.

In MYSQL 8 kann man einen Index aus einer Expression erzeugen und darin auch die COLLATE Option verwenden. In 5.7 geht das noch nicht, und in MariaDB sehe ich es auch nicht, da muss dann wohl die Column die passende Collation haben (oder man muss eine generated column mit passender Collation hinzufügen).

Rolf

[localhorst]

Hallo Rolf,

dazu habe ich gerade keine Quelle. Ich würde aber annehmen, dass eine COLLATE Klausel, die dazu führt, dass nicht die Collation des/der indexierten Feldes/r verwendet werden kann, den Gebrauch des Index verhindert, da sich durch die Collation die Reihenfolge der Werte ändert und damit der BTREE des Index nicht mehr nutzbar ist.

In MYSQL 8 kann man einen Index aus einer Expression erzeugen und darin auch die COLLATE Option verwenden. In 5.7 geht das noch nicht, und in MariaDB sehe ich es auch nicht, da muss dann wohl die Column die passende Collation haben (oder man muss eine generated column mit passender Collation hinzufügen).

Danke für deine Überlegungen.
So würde ich auch denken.

Bei 1.200 Rows mag das nicht ins Gewicht fallen. Bei 1.200.000 dürfte es dann schon lange unangenehm sein.

Mit Explain könnte man das doch bestimmt auch sichtbar machen!?
Leider habe ich keine DB zum Testen zur Verfügung.

Hat hier jemand Zeit und Gelegenheit?

Grüße localhorst

[Rolf B]

Hallo localhorst,

mit explain bekommst Du das definitiv zu sehen. Der sagt Dir ja, ob er einen Index Seek, Index Scan oder Table Scan macht.

Rolf