MYSQL UTF-8 opgeslagen met Latin1 omzetten naar UTF-8

[Johnny]

Ik heb een oude website met een MYSQL-database die ik wil exporteren, maar de non-ASCII-karakters worden daarbij niet goed overgezet en ik kan maar geen manier vinden om ze goed te converteren naar UTF-8.

De oude website gebruikte UTF-8 ook als codering in zowel de database als de weergave en alle karakters worden goed weergegeven en opgeslagen.

Als ik echter in phpMyAdmin kijk zie ik dat de vreemde tekens kapot zijn (é is é etc.). Ik ben er vrij zeker van dat dit is omdat de oude website geen SET NAMES 'utf8' gebruikte en de databaseverbinding dus op Latin1 stond.

Ik heb al diverse manieren geprobeerd om de gegevens te converteren, maar deze werken niet omdat de data is UTF-8 is, waarbij ieder multibyte-karakter (van 2 bytes) is omgezet naar Latin1 en daarbij is opgsplitst in 2 karakters, omdat ze buiten de ASCII-range vallen zijn en vervolgens weer opgeslagen als twee afzonderlijke multibyte UTF-8 karakters (4 bytes totaal).

Weet iemand een manier om dit (bij voorkeur via MySQL) om te zetten naar correcte UTF-8?

[GlowMouse]

Data dumpen, je dumpfile aanpassen (waarbij je de data niet aanpast en de velddefinitie wel) en weer importeren een optie?

[.oisyn]

Je kunt je tables toch ook gewoon aanpassen zodat ze wel UTF-8 gebruiken?

SQL:

ALTER TABLE t MODIFY col TEXT CHARACTER SET utf8

En dus niet CONVERT TO CHARACTER SET gebruiken want dan gaat ie alles omzetten. Bij bovenstaande SQL verandert hij alleen de metadata van de tabel, niet de daadwerkelijke data.

[GlowMouse]

.oisyn schreef op donderdag 03 mei 2012 @ 09:53:
Je kunt je tables toch ook gewoon aanpassen zodat ze wel UTF-8 gebruiken?

SQL:

ALTER TABLE t MODIFY col TEXT CHARACTER SET utf8

En dus niet CONVERT TO CHARACTER SET gebruiken want dan gaat ie alles omzetten. Bij bovenstaande SQL verandert hij alleen de metadata van de tabel, niet de daadwerkelijke data.

Als ik de documentatie lees (helemaal onderaan, vlak boven de comments), moet je eerst naar een blob (of ander type als je een geen TEXT gebruikt) converteren om een conversie van data te voorkomen.

[Johnny]

Het omzetten naar blob em/of aanpassen via ALTER TABLE heb ik al geprobeerd. Het probleem is dat de data al is opgeslagen als UTF-8. Maar het karakter "é" (en vele anderen) is opgeslagen als de twee karakters "Ã" en "©", dus binair helemaal anders dan hetzelfde karakter in Latin1.

Inmiddels heb ik ook al een mogelijke oplossing bedacht. Ik schrijf een PHP-script dat een Latin1-verbinding opent met de UTF-8 tabellen, net zoals de oude website en dan ook een verbinding met de nieuwe database en zet de gegevens daar weer in. Het is wel meer werk dan even een query runnen, maar ik heb er goede hoop op dat het gaat werken.

[Solopher]

Zo'n script heb ik hier al gevonden:

http://www.varesano.net/b...e%20mysql%20utf8%20tables

Ik zit momenteel een klein beetje met hetzelfde probleem, het is alleen een project van een paar jaar oud waar ik nu voor het eerst moet naar kijken.

Altijd fijn :-).

[.oisyn]

GlowMouse schreef op donderdag 03 mei 2012 @ 10:45:
[...]

Als ik de documentatie lees (helemaal onderaan, vlak boven de comments), moet je eerst naar een blob (of ander type als je een geen TEXT gebruikt) converteren om een conversie van data te voorkomen.

To avoid data type changes of the type just described, do not use CONVERT TO CHARACTER SET. Instead, use MODIFY to change individual columns. For example:

ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;
ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M) CHARACTER SET utf8;

[GlowMouse]

Waarbij data type hierop terugslaat:

For a column that has a data type of VARCHAR or one of the TEXT types, CONVERT TO CHARACTER SET will change the data type as necessary to ensure that the new column is long enough to store as many characters as the original column.

[.oisyn]

maar dat gaat dus over CONVERT TO CHARACTER SET, niet over MODIFY CHARACTER SET.
.edit: oh wacht ik snap wat je bedoelt. Inderdaad, bij CONVERT TO past hij het type aan zodat die groot genoeg blijft om alle tekst in op te slaan, en MODIFY doet dat niet. De oplossing is wel vrij simpel - eerst naar character set 'binary' converten.

Johnny schreef op donderdag 03 mei 2012 @ 13:04:
Het omzetten naar blob em/of aanpassen via ALTER TABLE heb ik al geprobeerd. Het probleem is dat de data al is opgeslagen als UTF-8.

Precies, en dus moet je je db even vertellen dat hij de data die hij al heeft moet interpreteren als utf-8 en niet als latin1

SQL:

ALTER TABLE t MODIFY col TEXT CHARACTER SET binary;
ALTER TABLE t MODIFY col TEXT CHARACTER SET utf8;

In feite hetzelfde als eerst converteren naar BLOB. Net hier getest, werkt prima.

[ Voor 104% gewijzigd door .oisyn op 03-05-2012 15:03 ]

[Solopher]

Oké, ik heb het op deze manier opgelost.... Moest ff quick en dirty.

Heb via mysqldump een dump bestand gemaakt waarbij ik de verbinding op latin1 had gezet, kreeg vervolgens een dump met hierin de fouten.

Heb dat dump bestand geopend met Notepad++ en vervolgens op Encode to UTF8 gezet.
Daarna heb ik in een nieuwe database die dump die ik had bewerkt met Notepad++ geimorteerd en toen opgegeven dat ik een utf8 verbinding wou.

Dit was voor mij de snelst werkende oplossing.