Probleme bei Insert mit Case-sensitive

[Duftox]

Folgendes Problem habe ich derzeit: Wenn ich eine Tabelle mit Kollation utf8-general-ci habe, dann ist ein SQL-Select case-insensitive. Soweit so akzeptabel. Um den Select case-sensitive zu machen, brauche ich ja nur das Key-Wort BINARY zu nutzen: SELECT * FROM userid WHERE BINARY user_id = 'ABC'. Schon bekomme ich auch wirklich nur "ABC" zurück und nicht z.B. "abc".

Aber was tue ich, wenn ich "abc" anlegen will? INSERT INTO userid SET user_id = 'abc' führt zu einem Duplicate-Key. Das Naheliegenste für mich wäre jetzt gewesen, dass ich ebenfalls das KEY-Wort BINARY irgendwo setzen muss, aber das akzeptiert er nirgendwo.

Komme ich nicht darum herum, die Tabellen-Definition zu ändern, oder gibt es doch die Möglichkeit, das BINARY irgendwo direkt im Insert unterzubringen?

[Ralf Jansen]

Klingt problematisch wenn man sich beim anlegen (bei einem INSERT) für die collation für diesen einen Datensatz entscheiden könnte. An dieser Stelle werden Indexe aufgebaut insbesondere auch die für uniqueness. Wenn bei jedem INSERT eine andere Defintion von Uniqueness gilt wie willst du sinnvoll entsprechende Indexe aufbauen? Wie willst du sinnvoll Duplikate behandeln wenn es keine eindeutige stabile Definition gibt? Ohne das kannst du eigentlich fast alle Indexe wegwerfen und mußt mit einer langsamen Datenbank leben. Vermutlich ist es am sinnvollsten eine case sensitive Spalte zu definieren. Und auf der einen caseinsensitiven Index (der dann aber nicht unique sein kann) zusätzlich anzulegen. Ich vermute sonst wird wenn du dich während dem select für eine andere Collation zum vergleichen entscheidest kein Index gezogen. Ist also potentiell sau langsam.

[Duftox]

Klingt problematisch wenn man sich beim anlegen (bei einem INSERT) für die collation für diesen einen Datensatz entscheiden könnte.

Also ich will nicht ausschließen, dass ich generell ein unvollständiges Verständnis von der Collation habe und den Sachverhalt deshalb noch nicht so ganz begreife. Aber die Collation ist doch die ganze Zeit die gleiche, nämlich utf8-general-ci, oder? Die soll doch auch so bleiben. Entscheidet der Zusatz BINARY beim Select nicht nur drüber, wie SQL sucht, also ob es Case-sensitive oder case-insensitive sucht? Wenn ich, wie oben schon erwähnt einen INSERT mit Key "ABC" mache, dann liefert mir ein SELECT mit BINARY user_id = 'ABC' ja auch korrekterweise einen Treffer zurück, während z.B. BINARY user_id = 'abc' null Treffer liefert.

Also hätte ich jetzt angenommen, dass dann auch die zwei Keys "ABC" und "abc" gleichzeitig in der Tabelle existieren können. Oder ist dies eine Fehlannahme? Oder wenn doch, wie kann ich sie dort reinbekommen?

[Christian Marquardt]

Oder ist dies eine Fehlannahme?

Ja
Ein Spalte die "ABC" und "abc" enthalten soll kann keine PK/UNIQUE-Spalte sein. Und das ist das Problem
Jede andere Spalte kann natürlich beides enthalten.
Also anderen PK definieren und einen Vergleich -wie von Ralf vorgeschlagen - mit einer anderen Spalte vornehmen

[Duftox]

Ein Spalte die "ABC" und "abc" enthalten soll kann keine PK/UNIQUE-Spalte sein. Und das ist das Problem

Aber was bewirkt denn dann der Zusatz BINARY genau? Ändert das die Kollation oder wie ist das zu verstehen? Weil wenn ich die Tabelle so definiere: CREATE TABLE userid ( user_id STRING BINARY PRIMARY KEY NOT NULL), also BINARY schon beim CREATE mit reinpacke, dann kann ich ja einen Insert mit "ABC" und einen mit "abc" machen.

[Christian Marquardt]

https://dev.mysql.com/doc/refman/8.0...varbinary.html

https://www.w3schools.com/sql/func_mysql_binary.asp

Convert a value to a binary string

[Duftox]

Ja, danke Dir Christian. Ich hatte natürlich vorher auch selbst schon ein bisschen gesucht, aber Dein Link bringt es ja nochmal sehr gut auf den Punkt, dass es eben um den character-by-character comparison geht, der dazu führt, dass "hallo" und "HALLO" für ihn gleich sind. Also nehme ich mal an, dass dieser besagte "Character-by-Character" comparison eine Spezial-Funktion darstellt, die es uns Usern ermöglicht, eine Groß-/Klein-Schreibungs-unabhängige Suche durchzuführen. Was wohl der Normalfall sein dürfte. Denn wenn ich bei Google "Kernspintomograph" suche, dann würde sich mein Verständnis in Grenzen halten, wenn Google mir eine Seite nicht anzeigt, nur weil es dort klein geschrieben ist. Somit ist für mich nachvollziehbar, dass der Character-by-Character Comparison bei mySQL den Default darstellt.

Zurück zu meinem Gedankengang: Auch wenn der Character-by-Character Comparison Groß/Kleinschreibung ignoriert, dann ändert dies ja nichts an der Tatsache, dass ein "H" (Ascii 72) nunmal etwas völlig anderes ist als ein "h" (Ascii 104) Also ist der Inhalt des Key-Feldes mit dem Wert "hallo" ja nun etwas vollkommen anderes als der Wert "HALLO" und der "Duplicate Key Fehler" kommt nur, weil er einen toleranten Character-by-Character Comparison durchführt anstatt eines byte-by-byte comparisons, wie man ihn z.B. bei Select mit dem Zusatz BINARY bewirken kann.

Und damit wäre ich ja dann zurück bei meiner Ausgangsfrage, warum es den z.B. für ein SQL-SELECT gibt, nicht aber für ein SQL-INSERT.

[Ralf Jansen]

Beim SELECT steuerst du ausschließlich die Vergleichsoperation wie du zum Ergebnis Set kommst. Du veränderst in keiner Weise die Tabelle und wie die gespeichert wird.
Bei einem INSERT beinflusst du aber den Inhalt der Tabelle und was wie gespeichert wird. Du widersprichst dann eventuell dem Tabellenaufbau den du beim Erzeugen der Tabelle festgelegt hast.

Im Moment hast du festgelegt das die Spalte eindeutige Werte enthalten soll und zwar ohne das casing zu berücksichtigen. Du hast möglicherweise schon Millionen von Datensätzen in der Tabelle die dieser Regel folgen und voraussichtlich in einer Weise gespeichert wurden um suchen unter zuhilfename dieser Regel schnell zu machen (heißt meist es gibt spezielle Indizes)
Wenn du jetzt ankommst und einen Datensatz einfügt der der Regel nicht entspricht, unter der Annahme du dürftest das, ist jede optimierte Datenhaltung annähernd sinnlos geworden. Wenn du auf eine solche Tabelle einen SELECT absetzt kann keine optimierte Regel angewendet werden weil du dich nicht an die Regel gehalten hast. Heißt also, in dem gedachten Fall du dürftest das was du gerne willst, ein SELECT wäre furchtbar lahm oder er findet einfach nicht alles.

Dein Beispiel mit google ist offensichtlich arg hinkend. Ein Unique Index ist etwas völlig anderes als ein VolltextIndex. Bei google ist es scheiß egal ob du alles findest, nur einen Teil findest, oder ob eine Suche überhaupt 1-1 wiederholbar ist. Man möchte da einfach Treffer zu irgendwas von irgendwo völlig unscharf. Da werden keinen eindeutigen Abhängigkeiten abgelegt ganz anders als in einer relationalen Datenbank.
Also ändere die Tabellendefinition oder denk dir was anderes aus das die Eindeutigkeit dieser Spalte nach der aktuellen Definition erhält.

Es erscheint eh sehr merkwürdig das etwas das id im Namen trägt wie die genannte Spalte irgendeinen Sinn enthält so das casing/ nicht casing wichtig wäre. Meist ist man richtig unterwegs wenn das eine völlig künstliche Größe ist die keinen Anspruch auf Lesbarkeit hat sondern ein rein technisches Interna abbildet (eine Beziehung oder etwas eindeutig macht). Testfrage dazu wäre müssen User einer Anwendung die diese Datenbank benutz irgendwas von dieser id wissen? Können die die sogar sehen? Wenn dem so ist läuft da vermutlich was falsch.

[Duftox]

Ok, ich verstehe. Wenn ich die Tabelle also ohne Zusatz BINARY aufbaue, dann baut er sich andere (für case-insensitive Suche geeignete) Indizes auf. Das klingt erstmal nachvollziehbar. Daraus folgt allerdings (wichtige Erkenntnis für mich!!!) dass ich dann bei großen Tabellen beim Read den Zusatz BINARY auch vermeiden sollte, denn er würde dann case-sensitiv suchen, obwohl er nur case-insensitive Indizes hat und wie von dir erläutert dadurch sehr langsam werden. BINARY im READ zeugt also von schlechtem Design, weil - wenn man es wirklich braucht - dann hätte gleich die Tabelle entsprechend definiert werden sollen.

Eine wirklich sehr wertvolle Info für mich, ich werde jetzt mit gutem Gewissen das BINARY in das CREATE TABLE reinpacken und kann dann auf die Angabe im Read verzichten (und auf die theoretisch benötigte im Insert).

Meine ursprüngliche Annahme, war halt einfach nur falsch. Ich arbeite seit Urzeiten z.B. mit Oracle-Datenbanken, und dort ist "hallo" nun etwas vollkommen anderes wie "HALLO". Das mySQL das anders handhabt, also es zwar unterschiedlich speichert aber gleich behandelt, das muss man dann erstmal in den Kopf reinbekommen.

Ich bin Euch jedenfalls sehr dankbar, dass Ihr euch die Zeit genommen habt, mir zu dieser Erkenntnis zu verhelfen.