[Erledigt] MySQL: Konvertierungsprobleme latin1 -> utf8

[Partyschaum]

Moin,

auch auf die Gefahr hin, dass ihr zur "Mein Zeichensatz sieht so grumpelig aus kann ich echt nicht mehr Hören"-Gruppe gehört - ich selbst dachte bis vor kurzem noch, Zeichensätze hätte ich im Griff - möchte ich mich trotzdem kurz an einer Problemskizze versuchen...

Aufgabe: Umzug von Tabellen aus Datenbank A nach Datenbank B.

Problem: Die Tabellen aus Datenbank A sind in latin1 codiert, die in Datenbank B sollen utf8 werden.

Lösungsansatz bisher:

1. Export des Schemas aus Datenbank A
2. Anpassen der Schema-Datei gemäß UTF-8 (DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci)
3. Aufsetzen des Schemas in Datenbank B
4. Export der Tabelleninhalte aus Datenbank A
5. Import der Inhalte in das Schema der Datenbank B

Annahme: Wenn das Schema auf UTF-8 basiert und der Import in Schritt 5. einfache INSERT-Statements sind (mysqldump) konvertiert MySQL da schon irgendwie nach UTF-8.

Meine Annahme hat sich natürlich nicht bestätigt.

Um herauszubekommen, was CONVERT() in MySQL überhaupt so tut, hab ich mal folgendes ausprobiert:

SET NAMES latin1;
SELECT CONVERT( 'äöüß' USING utf8 ) AS utf8,
HEX( CONVERT( 'äöüß' USING utf8 ) ) AS hex_utf8,
CONVERT( 'äöüß' USING latin1 ) AS latin1,
HEX( CONVERT( 'äöüß' USING latin1) ) AS hex_latin1;

Wie erwartet, konnte ich hier einen Unterschied ausmachen...

+------+------------------+--------+------------+
| utf8 | hex_utf8         | latin1 | hex_latin1 |
+------+------------------+--------+------------+
| äöüß | C3A4C3B6C3BCC39F | äöüß   | E4F6FCDF   |
+------+------------------+--------+------------+

So weit so gut...

Jetzt will ich noch mal genauer prüfen, was in der Tabelle so angekommen ist:

SET NAMES utf8;
SELECT ueberschrift FROM onlinedb_angebote WHERE agnr = 'C30130';
+--------------------------------------------------------------------------+
| ueberschrift                                                             |
+--------------------------------------------------------------------------+
| (;-) Möbl.1-Zi-WG,&Duschbad,Kühlschrank,Kochstelle,Mikrowelle (-;) |
+--------------------------------------------------------------------------+

SET NAMES latin1;
SELECT ueberschrift FROM onlinedb_angebote WHERE agnr = 'C30130';

+------------------------------------------------------------------------+
| ueberschrift                                                           |
+------------------------------------------------------------------------+
| (;-) Möbl.1-Zi-WG,&Duschbad,Kühlschrank,Kochstelle,Mikrowelle (-;) |
+------------------------------------------------------------------------+

Ok, was in der Tabelle steht ist immer noch latin1. Soweit ich weiß passt das ja auch. Ist dieses ISO-8859-Dings nicht eine Teilmenge von UTF-8? Die Dinge aus dem 1. Semester sind lange her...

Mein nächster Ansatz soll so aussehen:

SET NAMES utf8;
UPDATE onlinedb_angebote SET ueberschrift = CONVERT( ueberschrift USING utf8 );

Bevor ist das mache hab ich folgendes ausprobiert:

SELECT SUBSTR( ueberschrift, 1, 10 ) as quelle,
SUBSTR( CONVERT( ueberschrift USING latin1 ), 1, 10 ) AS quelle_latin1,
SUBSTR( CONVERT( ueberschrift USING utf8 ), 1, 10 ) AS quelle_utf8,
SUBSTR( HEX( ueberschrift ), 1, 10 ) AS hex_quelle,
SUBSTR( HEX( CONVERT( ueberschrift USING latin1 ) ), 1, 10 ) AS hex_latin1,
SUBSTR( HEX( CONVERT( ueberschrift USING utf8 ) ), 1, 10 ) AS hex_utf8
FROM onlinedb_angebote WHERE agnr = 'C30130';

Meine Oma würde jetzt sagen: "Schock-schwere-Not!!einself!!"

...denn wieso sehe ich da keine Unterschiede in den konvertierten hex-Werten?

+------------+---------------+-------------+------------+------------+------------+
| quelle     | quelle_latin1 | quelle_utf8 | hex_quelle | hex_latin1 | hex_utf8   |
+------------+---------------+-------------+------------+------------+------------+
| (;-) Möbl. | (;-) Möbl.    | (;-) Möbl.  | 283B2D2920 | 283B2D2920 | 283B2D2920 |
+------------+---------------+-------------+------------+------------+------------+

Ihr merkt, ich komme hier gerade so überhaupt nicht weiter.

Deshalb bin ich für jeden Tipp oder (Denk-) Fehlerhinweis dankbar!

Beste Grüße aus Kiel,

der Hauke

[Chriz]

MySQL :: MySQL 5.1 Referenzhandbuch :: 10.6.2 CONVERT() und CAST()

SELECT CONVERT(_latin1'Müller' USING utf8);
INSERT INTO utf8table (utf8column)
    SELECT CONVERT(latin1field USING utf8) FROM latin1table;

Hilft das irgendwie?

Ich würde das mal mit und mal ohne SET NAMES testen.

[G.Schuster]

Beachte beim Ex-/Import auch das DefaultCharset in der Serverconfig.
Wenn das Latin1 ist (aber DB UTF solltest du prüfen, ob du die Verbindung auch auf Latin1 setzen musst für den Export.
Bei mir jedenfalls gab es das Problem, dass bei DefaultCharset Latin1, Verbindung UTF8 und DB UTF8 die exportierten Daten doppelt codiert wurden und so beim LOAD DATA LOCAL INFILE Zeichensalat rauskam.

[Partyschaum]

Ein mehr oder weniger entspanntes Wochenende, ausreichend Schlaf und mit neuem Elan an mein altes Problem...

Das Quellsystem:

#> mysql -u onlinedb2 -p --default-character-set=latin1 onlinedb2

mysql> show variables like "%character%"; show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
7 rows in set (0.01 sec)

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> SHOW FULL COLUMNS FROM onlinedb_angebote;
+--------------------+--------------+-------------------+------+-----+------------+-------+---------------------------------+---------+
| Field              | Type         | Collation         | Null | Key | Default    | Extra | Privileges                      | Comment |
+--------------------+--------------+-------------------+------+-----+------------+-------+---------------------------------+---------+
| agnr               | varchar(10)  | latin1_swedish_ci |      | PRI |            |       | select,insert,update,references |         |
| hcid               | tinyint(4)   | NULL              |      | PRI | 0          |       | select,insert,update,references |         |
| status             | char(1)      | latin1_swedish_ci |      |     | 1          |       | select,insert,update,references |         |
| immobilienart      | char(1)      | latin1_swedish_ci |      |     |            |       | select,insert,update,references |         |
| objektkategorie    | tinyint(2)   | NULL              |      |     | 0          |       | select,insert,update,references |         |
| ueberschrift       | varchar(100) | latin1_swedish_ci |      |     |            |       | select,insert,update,references |         |

...

Die Kollation ist bei allen Tabellen latin1_swedish_ci.

#> mysqldump -uonlinedb2 -p \
> --default-character-set=latin1 \
> --set-charset=latin1 \
> --no-create-db \
> --no-create-info \
> --complete-insert \
> onlinedb2 tabelle_1 tabelle_2 ... tabelle_n > onlinedb2.data.sql

#> file onlinedb2.full.sql
onlinedb2.full.sql: Non-ISO extended-ASCII English text, with very long lines

#> mysqldump -uonlinedb2 -p \
> --default-character-set=latin1 \
> --set-charset=latin1 \
> --no-create-db \
> --no-data \
> onlinedb2 tabelle_1 tabelle_2 ... tabelle_n> onlinedb2.schema.sql

#> file onlinedb2.data.sql
onlinedb2.data.sql: Non-ISO extended-ASCII English text, with very long lines

Ich schaue mir den Export in meinem PuTTY an...

#> less onlinedb2.data.sql
VALUES ('Do 93',12,'1','4',2,'Erdgesch
osswohnung im 4 Familienhaus mit kleiner Terasse','2 - room apartment in Dortmund','Appartement de 2 pi350ces 340 Dortmund','Piso de 2 habitatcione
s en Dortmund','Somborn und L374tgendortmund ist ein grosser Stadtteil etwa 9Kilometer westlich vom Zentrum. Es gibt einen d366rflichen, sch366nen
Ortskern

Komische Zeichen bzw. Zahlen in den Inserts!

Vermutung: Da hat doch jemand eindeutig keine latin1-Werte in die latin1-Felder geschrieben...

Ich überprüfe mein PuTTY: Mein Translation Setting ist auf ISO-8859-1:1998 (Latin-1, West Europe).

Ich stelle das Translation Setting im PuTTY auf UTF-8 und verbinde mich - sicher ist sicher - erneut mit dem Server. Ein "less onlinedb2.data.sql" gibt mir immer noch dieselben grumpeligen Zeichen aus.

Weitere Vermutung: "file" sagt mir ja, dass es sich um eine NON-ISO extended-ASCII Datei handelt. Scheint also irgendetwas fies durcheinander gewürfeltes zu sein und untermauert meine Vorherige Vermutung. Kein ISO-Dings und kein UTF-8. Ich wage einen mutigen Schritt...

#> iconv -f latin1 -t UTF-8 onlinedb.data.sql > onlinedb.data.iconv.sql
#> file onlinedb2.data.iconv.sql
onlinedb2.data.iconv.sql: UTF-8 Unicode English text, with very long lines

#> less onlinedb2.data.iconv.sql
VALUES ('Do 93',12,'1','4',2,'Erdgeschosswohnung im 4 Familienhaus mit kl
einer Terasse','2 - room apartment in Dortmund','Appartement de 2 pièces Dortmund','Piso de 2 habitatciones en Dortmund','Somborn und Lütgendortmund ist ein g
rosser Stadtteil etwa 9Kilometer westlich vom Zentrum. Es gibt einen dörflichen, schönen Ortskern

*yippie ya yeah schweinebacke*

Das sieht schon viel besser aus... damit wage ich jetzt mal einen erneuten Import im Zielsystem.

Das Zielsystem:

Ich verbinde mich mit meinem auf UTF-8 gestellten PuTTY auf den Zielserver.

In onlinedb2.schema.sql editiere Ich die Tabellen wie folgt:

CREATE TABLE `onlinedb_XYZ` (

[...]

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Jetzt schaue ich mir mal die Einstellungen im MySQL an...

mysql -uhomeco_8 -p --default-character-set=utf8 homeco_db8

mysql> show variables like "%character%"; show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | utf8_general_ci   |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

Nun gut, auf 'character_set_server' und 'collation_server' hab ich keinen Einfluss, da der Kunde "fremdhostet"... der Schlingel!

Jetzt der Import des Schemas...

#> mysql -uhomeco_8 -p --default-character-set=utf8 homeco_db8 < onlinedb2.schema.iconv.sql

mysql> show full columns from onlinedb_angebote;
+--------------------+--------------+-----------------+------+-----+------------+-------+---------------------------------+---------+
| Field              | Type         | Collation       | Null | Key | Default    | Extra | Privileges                      | Comment |
+--------------------+--------------+-----------------+------+-----+------------+-------+---------------------------------+---------+
| agnr               | varchar(10)  | utf8_general_ci | NO   | PRI |            |       | select,insert,update,references |         |
| hcid               | tinyint(4)   | NULL            | NO   | PRI | 0          |       | select,insert,update,references |         |
| status             | char(1)      | utf8_general_ci | NO   |     | 1          |       | select,insert,update,references |         |
| immobilienart      | char(1)      | utf8_general_ci | NO   |     |            |       | select,insert,update,references |         |
| objektkategorie    | tinyint(2)   | NULL            | NO   |     | 0          |       | select,insert,update,references |         |
| ueberschrift       | varchar(100) | utf8_general_ci | NO   |     |            |       | select,insert,update,references |         |
| ueberschrift_eng   | varchar(100) | utf8_general_ci | NO   |     |            |       | select,insert,update,references |         |
| ueberschrift_franz | varchar(100) | utf8_general_ci | NO   |     |            |       | select,insert,update,references |         |
| ueberschrift_span  | varchar(100) | utf8_general_ci | NO   |     |            |       | select,insert,update,references |         |

Das sieht doch super aus!

Jetzt die Daten...

#> mysql -uhomeco_8 -p --default-character-set=utf8 homeco_db8 < onlinedb2.data.iconv.sql

mysql> select ueberschrift from onlinedb_angebote limit 30;

[...]

| WBS ERFORDERLICH: Günstige 2-R-Whg. in Sudenburg; san. Altbau, 2.OG, ca. 54m², gefl. Bad mit Wan ...    |
| möbl. 2-Bett-Zimmer in Pension, teilsan. Altbau, 2.OG; ca. 22m²; Badmitbenutzung (mit Fenster) m ...    |
| möbl. 3-Bett-Zimmer in Pension, teilsan. Altbau, 1.OG; ca. 17m²; Badmitbenutzung mit Dusche; Küc ...    |
| Tolle 3-R-Whg. mit EBK in Sudenburg; san. Altbau; 2. OG; ca. 88 m²; alle Wohnräume und Flur mit ...     |
| (;-) Möbl.2-Zi-App. im Boardingh,integr.Kü, Duschbad; Balkon(-:)                                        |
| (;-) Möbl.2-Zi-App. im Boardingh,sep. Küche, Duschbad,Balkon, (-:)                                      |
| (;-) Möbl.1-Zi-App. im Boardingh, Miniküche&amp;Duschbad,Balkon, (-;)                                   |

Ich mache den Gegencheck und verbinde mich ausdrücklich mit latin1 Codierung...

> mysql -uhomeco_8 -p --default-character-set=utf8 homeco_db8 < onlinedb2.data.iconv.sql

mysql> select ueberschrift from onlinedb_angebote limit 30;

| WBS ERFORDERLICH: G¦nstige 2-R-Whg. in Sudenburg; san. Altbau, 2.OG, ca. 54m¦, gefl. Bad mit Wan ... |
| m¦bl. 2-Bett-Zimmer in Pension, teilsan. Altbau, 2.OG; ca. 22m¦; Badmitbenutzung (mit Fenster) m ... |
| m¦bl. 3-Bett-Zimmer in Pension, teilsan. Altbau, 1.OG; ca. 17m¦; Badmitbenutzung mit Dusche; K¦c ... |
| Tolle 3-R-Whg. mit EBK in Sudenburg; san. Altbau; 2. OG; ca. 88 m¦; alle Wohnr¦ume und Flur mit ...  |
| (;-) M¦bl.2-Zi-App. im Boardingh,integr.K¦, Duschbad; Balkon(-:)                                     |
| (;-) M¦bl.2-Zi-App. im Boardingh,sep. K¦che, Duschbad,Balkon, (-:)                                   |
| (;-) M¦bl.1-Zi-App. im Boardingh, Minik¦che&amp;Duschbad,Balkon, (-;)                                |

...uhhh, das sieht ekelig aus. Ekelig ist in diesem Fall aber gut.

MISSION ACCOMPLISHED!

[Partyschaum]

Addendum:

Die iconv-Schritte hätte ich mir gleich sparen können, da ja UTF-8 Werte bereits IN den Feldern standen. Somit hab ich mir mit dem iconv lediglich die Euro-Symbole zerschossen...

Statt dessen macht ein mysqldump --default-character-set=utf8 --set-charset=utf8 da wesentlich mehr Sinn.