[MySQL] Unicode vervangen

[Rvanlaak]

Ha,

Ben de laatste paar uren bezig geweest met het zoeken naar de oplossing voor een latin1/unicode probleem. In mijn database zijn producten opgeslagen door middel van een import uit een excel bestand. Hierin stonden bij de titel en omschrijving bepaalde exotische tekens in zoals ç en ô. Bij de conversie is deze omgezet naar de latin1 versie, zoals op http://jeppesn.dk/utf-8.html wordt weergegeven (rechter kolom).

Nu ben ik dus eens gaan Googlen, waarna ik er achter kwam dat ik sowieso in de database de wijzigingen door moest gaan voeren. Het was geen optie om de character encoding op de site zelf aan te passen, evenals dat het aanpassen van de character_set_results variabele in MySQL geen resultaat had.

In PHP heb ik het al voor elkaar de conversie goed te doen, namelijk door middel van utf_decode();

Mijn vraag is nu; hoe kan ik de conversie het beste aanpakken? Bestaat er een sql commando welke meteen alle characters omzet naar het bijbehorende karakter? Of zal ik door middel van een php script alles moeten updaten?

Voorbeeld:

Colloïdaal
moet worden
Colloïdaal

[remmelt]

Probeer zo snel mogelijk, dus zo vroeg mogelijk in de conversie, al je tekst op te slaan als UTF-8.

Je DB moet natuurlijk op Unicode staan en je kan PHP ook vertellen dat 'ie UTF met Mysql praat, hier ga ik even van uit.

Ik zou de Excel dingen inlezen in PHP, ze dan omzetten naar UTF-8 en dan opslaan. Zo ben je meteen van alle Latin1 af. Mijn advies is dan ook om de boel nog een keer te importeren, dat is het schoonst omdat je dan alle Latin1 buiten de poort houdt.

[ Voor 15% gewijzigd door remmelt op 08-10-2008 14:54 ]

[Rvanlaak]

Ja, dat was ook mijn eerste advies geweest.

Echter was ik op zoek naar een snelle manier om de tekens in de database te vervangen. Ik had namelijk het import bestand niet. Na onderzoek kwam ik er dus uit, dat als het eenmaal in de database staat dat er dan niets meer aan te doen is, en het vervangen moest gaan worden.

Vandaar dat ik op zoek ben/was naar een sql commando welke (net zoals REPLACE doet) de tekens omzet in de leesbare variant.

[Verwijderd]

Kan je met php/c/whatever niet simpelweg de string weer interpreteren als unicode, en die vervolgens naar een unicode tabel wegschrijven?

In C(++) kan je de string casten naar een wchar_t*, en dan zou het moeten werken. Moet je wel je locale in C op UTF-8 zetten, of welke unicode-versie Excel ook gebruikt heeft.

[Rvanlaak]

Ik heb nu een php scriptje geschreven. Hierin staan alle vreemde tekens in een array, met de bijbehorende 'vertaling'. Door middel van een foreach loopje bouw ik nu een replace query op. Dit, in combinatie met een form waarin ik de tabelnaam en veldnamen invoer; zorgt voor het nodige resultaat. Namelijk een tabel waarin ik de vreemde chars vervang.

Maar, zoals ik het begrijp heeft MySQL hier geen directe ondersteuning voor? Ik wil dergelijke problemen namelijk in de toekomst snel kunnen voorkomen/oplossen.

[Johnny]

Als je database en tabellen ingesteld staan als latin1, en de inhoud ook die encoding gebruikt dan kun je die met een query omzetten naar een andere encoding.

SQL:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

En de default charset van de database doe je als volgt:

SQL:

ALTER DATABASE database_name DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;

[remmelt]

Dit is geen mysql probleem. Mysql interpreteert de data zoals jij zegt dat 'ie het moet interpreteren, in dit geval dus utf-8. Als jij er dan data instopt die niet utf-8 is, dan ligt dat niet aan mysql.

Ik ben hier ook al een aantal keer tegenaan gelopen en heb al verscheidene minder mooie tot uiterst lelijke hacks in projecten voorbij zien komen. Het blijft gedoe met die vreemde tekens, met name omdat wij ze amper gebruiken en je er pas later aan denkt. Mijn stelregel is dan ook: alles unicode, zo vroeg mogelijk.

[Verwijderd]

Johnny schreef op woensdag 08 oktober 2008 @ 17:22:
Als je database en tabellen ingesteld staan als latin1, en de inhoud ook die encoding gebruikt dan kun je die met een query omzetten naar een andere encoding.

SQL:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

En de default charset van de database doe je als volgt:

SQL:

ALTER DATABASE database_name DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;

Maar zal dit de strings intact laten, of zal het ze per character converteren naar UTF-8? 'Ã' en '¯' zijn immers ook geldige UTF-8 tekens.

[.oisyn]

remmelt schreef op woensdag 08 oktober 2008 @ 17:27:
Dit is geen mysql probleem. Mysql interpreteert de data zoals jij zegt dat 'ie het moet interpreteren, in dit geval dus utf-8. Als jij er dan data instopt die niet utf-8 is, dan ligt dat niet aan mysql.

Idd. Uit je topic kan ik het niet helemaal uitmaken welke encodings je nou wilt gebruiken, maar wat je iig moet doen is de encoding achterhalen waarmee het de db in is gegaan en dan je tabel/db instellen op die encoding zonder de inhoud te converteren. Daarna moet je het instellen op de encoding die je wilt hebben waarbij je wél de inhoud moet converteren.

[Verwijderd]

Het staat zelfs in de manual: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

Warning

The CONVERT TO operation converts column values between the character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like utf8). In this case, you have to do the following for each such column:

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

The reason this works is that there is no conversion when you convert to or from BLOB columns.

[pedorus]

En er is zelfs een specifieke pagina over:
http://dev.mysql.com/doc/...n/charset-conversion.html.
Die methode daar met BINARY(size) lijkt me vaak iets sneller dan met BLOB (afhankelijk van het veld natuurlijk).

[ Voor 11% gewijzigd door pedorus op 08-10-2008 18:25 ]

[Megamind]

Je kan toch een PHP script schrijven waar je utf8_encode doet, zodat je weer normale tekens krijgt, en dan deze als UTF8 wegschrijven

code:

mysql_query("SET NAMES 'UTF8' COLLATE 'utf8_unicode_ci';");
mysql_query("SET CHARACTER SET UTF8;");