utf-8 Zeichen in latin1_general_ci Datenbank speichern

[Ralf]

Hallo,

ich muss eine Übergangslösung für ein bestehendes System basteln und an folgende Bedingenen mehr oder wenige gebunden:

• Es gibt eine MySQL DB Tabelle deren Zeichensatz auf latin1_general_ci eingestellt ist.
• nun gibt es eine Website deren header auf utf-8 gesetzt ist (header("Content-Type: text/html; charset=utf-8");). Auf dieser gibt es ein Formular welches seine Daten per POST an ein php Script schickt. Diese Daten müssen nun in der o.g. DB Tabelle gespeichert werden.

Bei deutschem Text (ink. Sonderzeichen) gibt es keine Probleme. Wohl aber bei z.B. bei kyrillischem Text.

Ich könnte jetzt die Tabelle mit ALTER TABLE table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; auf utf-8 aumstellen. Das würde wohl die Probleme lösen. Aber das ist mir nicht geheuer. Das bestehende System ist recht komplex und über viele Jahre von verschiedenen Leuten zusammen gebastelt worden. Wenn ich da Kollateralschaden anrichte bekomme ich mächtig Ärger.

Frage: (1) Was könnte es bzgl. anderer auf die gleiche Tabelle zugreifender Anwendungen für Nebeneffekte geben wenn ich die Tabelle auf utf-8 umstelle?

(2) Wie könnte ich die (z.B. kyrillischen) Sonderzeichen in meinem PHP Script so codieren, dass ich sie in der latin1_general_ci Tabelle speichern kann?

MfG
Ralf

[Gunnar Bittersmann]

@@Ralf:

nuqneH

Es gibt keine „UTF-8-Zeichen“.

Bei deutschem Text (ink. Sonderzeichen) gibt es keine Probleme.

Umlaute sind keine „Sonderzeichen“.

(2) Wie könnte ich die (z.B. kyrillischen) Sonderzeichen

Kyrillische Buchstaben sind keine „Sonderzeichen“.

in meinem PHP Script so codieren, dass ich sie in der latin1_general_ci Tabelle speichern kann?

Maskieren. Auf dieselbe Art, wie man es anderswo tut. Bspw. prozent-codiert wie in URIs.

Vergiss das gleich wieder. Man möchte Daten nicht verfälschen. Stelle deine DB auf UTF-8 um.

Qapla'

[Ralf]

in meinem PHP Script so codieren, dass ich sie in der latin1_general_ci Tabelle speichern kann?

Maskieren. Auf dieselbe Art, wie man es anderswo tut. Bspw. prozent-codiert wie in URIs.

Vergiss das gleich wieder. Man möchte Daten nicht verfälschen. Stelle deine DB auf UTF-8 um.

Warum schlägst Du es dann erst vor?

Vor dem Umstellen der DB auf utf-8 habe ich etwas Angst (siehe original posting).
Was sind die üblichen Verdächtigen bei solch einer Umstellung?
Wenn man die von latin1_general_ci auf utf-8 umgestellte DB wieder auf latin1_general_ci umstellt, erhält man dann wieder die ursprünglichen Daten?

Um den Einwand vorweg zu nehmen: Ich mache natürlich eine Datensicherung der DB vor jeder "heikelen" Opertation. Aber es könnten Nach der Umstellung und während des anschließenden Testen neue Daten hereinkommen, die dann nicht im Backup enthalten sind. Ich kann auch keine Testkopie machen, sonder muss am lebenden Patienten operieren. Fragt bitte nicht warum, das würde zu weit führen.

Gruß
Ralf

[1UnitedPower]

Vor dem Umstellen der DB auf utf-8 habe ich etwas Angst (siehe original posting).
Was sind die üblichen Verdächtigen bei solch einer Umstellung?

Ahnungslosigkeit. Du solltest dringend erstmal die Fachterminologie lernen. Was ist ein Zeichensatz? Was ist eine Kodierung? Was ist eine Kollation? Was ist der Unterschied zwischen Konvertieren und Kodieren. Ich habe selber arge Probleme mir die das Fachjargon zu merken und rufe mir die Definitionen bevor ich mich an ein entsprechendes Problem wage immer erstmal wieder ins Gedächtnis.

Wenn man die von latin1_general_ci auf utf-8 umgestellte DB wieder auf latin1_general_ci umstellt, erhält man dann wieder die ursprünglichen Daten?

latin1_general_ci ist bloß eine Kollation, sie beeinflusst nur die Sortierung der Daten.

Um den Einwand vorweg zu nehmen: Ich mache natürlich eine Datensicherung der DB vor jeder "heikelen" Opertation. Aber es könnten Nach der Umstellung und während des anschließenden Testen neue Daten hereinkommen, die dann nicht im Backup enthalten sind.

Dann beantrage einen Datenbankfreeze und weise auf mögliche Konsequenzen hin, wenn du keine Zusage bekommst. Am besten natürlich zu einer Zeit, bei der sowieso wenige Datenbank-Operationen stattfinden.

Folgender Artikel hilft mir immer wieder in die Materie einzusteigen: http://nicj.net/mysql-converting-an-incorrect-latin1-column-to-utf8/

[dedlfix]

Tach!

latin1_general_ci ist bloß eine Kollation, sie beeinflusst nur die Sortierung der Daten.

Jein. Eine Kollation kommt nicht ohne Zeichenkodierungsangabe aus. Wenn man die Kollation auf einen im vorderen Teil anderen Wert ändert, ändert man damit auch die Kodierung.

dedlfix.

[dedlfix]

Tach!

• Es gibt eine MySQL DB Tabelle deren Zeichensatz auf latin1_general_ci eingestellt ist.

Ausschlaggebend für die Daten ist letzlich die Kodierung der einzelnen (String-)Felder. Die Tabellenangabe ist nur ein Defaultwert für neu anzulegende Felder, wenn man dabei keine konkrete Angabe macht. (Das heißt auch, dass Felder derselben Tabelle auf unterschiedliche Kodierungen eingestellt sein können.)

Der nächste wichtige Punkt ist, dass die Kodierungsangabe der Daten nicht das alleinige zu beachtende Kriterium ist. Der MySQL-Server ist eine Blackbox. Die Feldkodierungsangabe gibt nur an, was für Daten drin abgelegt werden können. Der Weg von und zum Client steht auf einem anderen Blatt. Zwischen Client und Feld wird gegebenenfalls in beide Richtungen umkodiert. Was auf einer bestimmten Verbindung gesprochen wird, sollte nach (je)dem Aufbau ausgehandelt werden. Unterlässt man das, nimmt MySQL irgendeinen konfigurierten Defaultwert an. Spricht man dann was anderes, landen falsch kodierte Daten in den Feldern. Das muss nicht unbedingt auffallen, wenn man beim Rückweg denselben Fehler nur umgekehrt macht. Man sieht das nur daran, das Programme, die die Verbindungskodierung korrekt aushandeln (wie zum Beispiel phpMyAdmin), falsche Daten anzeigen.

Da die Verbindungskodierungsaushandlung beeinflusst, welche Kodierung man von und zum System verwenden kann/erhält, ist es ganz ungünstig, wenn man am Defaultwert Änderungen vornimmt und Clients im System hat, die sich bisher nicht darum geschert haben. Die bekommen dann natürlich Murks, und was sie senden, wird auch Murks. - Stellt man nur die Felder um, auf einen anderen Wert als die Default-Verbindungskodierung, dann klappt die Kommunikation mit diesen irgnoranten Clients weiterhin, bis auf Zeichen die nicht in dieser Kodierung dargestellt werden können. Beispielsweise sei die Default-Verbindungskodierung latin1 und die Felddaten utf8, dann können zwar Umlaute umkodiert werden, aber Nicht-latin1-Zeichen (wie kyrillische) gehen verloren. (In dem verlinkten Dokument stehen auch alle Konfigurationswerte aufgelistet, so dass du die Default-Werte in deinem System raussuchen kannst: beispielsweise im phpMyAdmin über "Variables".)

in meinem PHP Script so codieren, dass ich sie in der latin1_general_ci Tabelle speichern kann?

Die einzige Möglichkeit, 65536 Zeichen mit 256 möglichen Codes darzustellen, ist Ersatzdarstellungen zu verwenden. HTML macht das mit NCRs und Entitys, UTF-8 macht das mit seinen bekannten Bytesequenzen. (Ja, Unicode kennt mehr Zeichen, aber MySQLs utf8 kennt nur die BMP. Die anderen werden erst mit utf8mb4 ab MySQL 5.5 unterstützt - falls man das braucht.)

Wenn du in der Datenbank keinerlei Stringverarbeitung (zum Beispiel Sortieren und Vergleichen) betreibst, dann kannst du auch Ersatzdarstellungen mit mehreren Byte verwenden, wenn pro Zeichen nur eins vorgesehen ist. Beachte allerdings, dass die Feldlänge in dem Fall eine auf Byte begrenzende Wirkung kat (weil von ein Byte gleich ein Zeichen ausgegangen wird).

Eine mögliche Ersatzdarstellung wäre, wenn du UTF-8 zum MySQL sprichst, der aber von Latin1 ausgeht. Dann wird jedes Byte der UTF-8-Byte-Sequenzen als einzelnes Zeichen interpretiert. Beim Rückweg liest das UTF-8-System diese Einzel-Zeichen wieder als UTF-8-Bytesequenz, und alles scheint bestens - bis auf Stringverarbeitung im MySQL-System. Und es müsste dann generell so verfahren werden, also bereits enthaltene Latin1-Zeichen oberhalb von 0x7F müssten erstmal in UTF-8-Sequenzen umkodiert werden. Sämtliche Clients müssten ebenfalls so eingestellt sein, dass sie damit umgehen können. Insgesamt ist das aber auch nur eine Krücke, die man vermeiden möchte.

Maskieren. Auf dieselbe Art, wie man es anderswo tut. Bspw. prozent-codiert wie in URIs.
Vergiss das gleich wieder. Man möchte Daten nicht verfälschen. Stelle deine DB auf UTF-8 um.
Warum schlägst Du es dann erst vor?

Man kann auch mal aufzeigen, welche Wege zielführend erscheinen, es aber nicht wirklich sind.

Was sind die üblichen Verdächtigen bei solch einer Umstellung?

Die Umstellung kann misslingen, wenn Zeichenkodierungsangaben und Inhalt nicht übereinstimmen, also wenn bereits Murks abgelegt ist. (Wie erwähnt, phpMyAdmin verwenden. Zeigt der alles richtig an, ist in der Regel alles bestens.)

Wenn man die von latin1_general_ci auf utf-8 umgestellte DB wieder auf latin1_general_ci umstellt, erhält man dann wieder die ursprünglichen Daten?

Jein. Nur wenn das unmittelbar danach erfolgte. Sind bereits Zeichen jenseits von ISO-8859-1/Latin1 eingefügt worden, gehen diese verloren (werden zu Fragezeichen).

Um den Einwand vorweg zu nehmen: Ich mache natürlich eine Datensicherung der DB vor jeder "heikelen" Opertation.

Wenn du unter Datensicherung nicht das Kopieren der Tabellen verstehst, sondern einen Dump, dann wird dieser auch nur mit derselben Blackbox-Kommunikation wie bei einer normalen Client-Verbindung ausgeführt. Das heißt, dass dabei Umkodierungen stattfinden können, dabei denen Datenverlust auftreten kann.

Die Datensicherung durch Umkopieren (in eine zweite Tabelle oder Datenbank) kann auch durch passende SQL-Statements erfolgen (so wie der phpMyAdmin das macht). Das muss kein Umkopieren der Dateien sein, zu dem man das MySQL herunterfahren muss.

Generell ist noch zu sagen, dass du keine Freude haben wirst, die Datenbank auf UTF-8 umzustellen, wenn Clients im System sind, die mit UTF-8 nicht umgehen können. Die können zwar weiterhin Latin1/ISO-8859-1 mit dem Server sprechen, aber Zeichen außerhalb davon werden wegen der eingebauten Umkodierung als ? angezeigt, und wenn der Client solche Daten speichert, dann wird das ein wirklicher Verlust werden, weil dann nur das Fragezeichen im Feld landet.

dedlfix.

[Jörg Reinholz]

Ich zeigs Dir mal als Beispiel:

Ich stelle die Spalte(!) auf Latein 1 ein:

mysql> ALTER TABLE __testCHANGEwert wert TEXT CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL;

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Ich füge Daten ein. Einmal mit Zeichen, die sowohl in UTF-8 als auch Latein 1 darstellbar sind:

mysql> insert into __test (wert) values ('Jörg');
Query OK, 1 row affected, 1 warning (0.01 sec)

Ich füge nochmals Daten ein. Diesmal mit Zeichen, die zwar in UTF-8 aber nicht in Latein 1 darstellbar sind:

mysql> insert into __test (wert) values ('добрый день');
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select * from __test;
+----+-------------+
| id | wert        |
+----+-------------+
|  0 | Jörg        |
|  0 | ?????? ???? |
+----+-------------+
2 rows in set (0.00 sec)

Die Konsole läuft mit UTF-8.

Das bedeutet für Dich: Geben die Betrachter der Webseite Zeichen ein, welche Latein 1 nicht darstellbar sind, so steht dann "Salat" in der Datenbank.

Ok. Ich ändere zu UTF-8:

mysql> ALTER TABLE __testCHANGEwert wert TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Versuch der Ausgabe:

mysql> select * from __test;
+----+-------------+
| id | wert        |
+----+-------------+
|  0 | Jörg        |
|  0 | ?????? ???? |
+----+-------------+
2 rows in set (0.00 sec)

Das bedeutet für Dich: Der "Salat" in der Datenbank ist auch nicht zu reparieren, wenn Du nachträglich auf UTF-8 umstellst. Ansonsten macht MySQL das mit den deutschen Umlauten bei der Umstellung alles richtig, sonst gäbe es auch hier "Salat".

Erneutes insert mit denselben Daten, die zwar in UTF-8 aber nicht in Latein 1 darstellbar sind:

mysql> insert into __test (wert) values ('добрый день');
Query OK, 1 row affected, 1 warning (0.01 sec)

Ausgabe:

mysql> select * from __test;
+----+-----------------------+
| id | wert                  |
+----+-----------------------+
|  0 | Jörg                  |
|  0 | ?????? ????           |
|  0 | добрый день           |
+----+-----------------------+
3 rows in set (0.00 sec)

So geht es also. Was bedeutet das für Dich? Wie jetzt mit den Daten, die zwar in UTF-8 aber nicht in Latein1 darstellbar sind umgegangen wird ist damit von der Anwendung abhängig und zwar davon, wie diese mit MySQL kommuniziert und wie diese diese Daten weiter verarbeiten.

Stellst Du also auf UTF-8 um, dann werden in den anderen Anwendungen wahrscheinlich künftig Fragezeichen oder nichts statt der kyrillischen Zeichen angezeigt.

Stellst Du nicht um passiert das selbe. Du siehst, die Wahl der Kodierung hat eine ziemlich zentrale Bedeutung. Die Entscheidung umzustellen oder nicht und künftige Daten in UTF-8-Kodierung einzutragen oder nicht können wir Dir nicht abnehmen. Es entsteht die Frage, ob es akzeptabel ist, wenn in den älteren Anwendungen für dort unbekannte Zeichen jeweils ein "?" oder nichts ausgegeben wird.

Eines noch.

Bei der Umstellung solltest Du bedenken, dass utf8_general_ci anders sortiert als das Telefonbuch:

Regeln für latin1_german1_ci (Wörterbuchsortierung, DIN 5007-1):
    Ä = A
    Ö = O
    Ü = U
    ß = s
    Regeln für latin1_german2_ci (Telefonbuchsortierung, DIN 5007-2):
    Ä = AE
    Ö = OE
    Ü = UE

Für die UTF-8-Sortierung wurde bislang nur DIN 5007-1 implementiert. Sie ist Teil von utf8_unicode_ci. Die Sortierung nach DIN 5007-2 kommt wohl frühestens mit MySQL 6.

Auch hier besteht also Klärungsbedarf.

Abschließender Rat:

SOWAS mit so weitreichenden Folgen planen und entscheiden in der Regel studierte Informatiker oder Großkopferte (mit Entscheidungsbefugnis) die sich für gleichwertig halten oder von anderen für dazu fähig gehalten werden. Wieso sollst Du (ich zitiere Dich) daran "herumbasteln"? Da es um eine Übergangslösung geht würde ich - wegen der Seiteneffekte hinsichtlich Dir nicht im Detail bekannter oder gar unbekannter Anwendungen - an der Datenbank nichts ändern bis jemand anders das anders entscheidet. Die Operation am wachen und sich bewegenden Patient fällt damit aus.

Jörg Reinholz