Nach MySQL-Update die Umlaute korrekt darstellen

2010-01-30 13:32

Wenn man Software updated, dann kann einiges schief laufen. So erging es mir neulich, nachdem ich beim Hoster eines von mir technisch betreuten Blogs einen Serverumzug in Auftrag gab, da der alte Server nicht die Vorraussetzungen für Wordpress 2.9 mit sich brachte.

Damit einher ging dann auch die Umstellung von MySQL 4.0 auf MySQL 5. Das Wordpress-Update verlief problemlos, nur beim Betrachten der Seite viel auf, dass die Umlaute schlicht unleserlich waren. Sehr ärgerlich, gerade auf einer deutschsprachigen Seite.

Nun stand ich vor der Wahl per Hand hunderte, wenn nicht tausende von Umlauten zu ändern oder mir eine Lösung zu suchen, welche das ganze weitgehend automatisch macht! Natürlich habe ich mich für letzteres entschieden ;)

Ich bin anscheinend nicht der einzige Administrator mit dem Problem und fand dann recht schnell eine Übersicht über kaputte Umlaute und ihre richtigen Gegenstücke.

Aber das alleine reichte mir noch nicht. Ich wollte alle Tabellen von einem Latin1-Zeichensatz auf Unicode, UTF8, umstellen. Dazu fertigte ich mittels phpMyAdmin einen Dump, Export, der Datenbank an. Mit einem Texteditor ersetzte ich alle Vorkommen von CHARSET=latin1 durch CHARSET=UTF8. Durch diese Änderung wird die Kollation der Tabellen automatisch geändert. Danach löschte ich alle betroffenen Tabellen und fügte meinen bearbeiteten Export wieder ein.

Nun habe ich zuvor auch probiert per Suchen und Ersetzen die kaputten Umlaute direkt in meinem Export zu ändern, aber das funktionierte beim anschließenden Import nicht. Glücklicherweise bietet MySQL eine Replace-Funktion an, der ich mich bedient habe.

So habe ich dann mit der Möglichkeit von phpMyAdmin, dass man selbst SQL-Abfragen eingibt (zu finden unter dem SQL-Tab) und einer Reihe von Abfragen mein Umlaut-Problem gelöst. Natürlich kann man auch, Shell-Zugang zum MySQL vorausgesetzt, dort diese Querys abschicken. Oder man packt sich die Abfragen in ein PHP-Script. Wie auch immer, meine SQL-Querys sahen so aus:

#Post-Inhalt

update wp_posts set post_content = replace(post_content,"ü","ü");

update wp_posts set post_content = replace(post_content,"ä","ä");

update wp_posts set post_content = replace(post_content,"ö","ö");

update wp_posts set post_content = replace(post_content,"ß","ß");

update wp_posts set post_content = replace(post_content,"Ãœ","Ü");

update wp_posts set post_content = replace(post_content,"Ä","Ä");

update wp_posts set post_content = replace(post_content,"Ö","Ö");

Post-Titel

update wp_posts set post_title = replace(post_title,"ü","ü");

update wp_posts set post_title = replace(post_title,"ä","ä");

update wp_posts set post_title = replace(post_title,"ö","ö");

update wp_posts set post_title = replace(post_title,"ß","ß");

update wp_posts set post_title = replace(post_title,"Ãœ","Ü");

update wp_posts set post_title = replace(post_title,"Ä","Ä");

update wp_posts set post_title = replace(post_title,"Ö","Ö");

Kommentar-Inhalt

update wp_comments set comment_content = replace(comment_content,"ü","ü");

update wp_comments set comment_content = replace(comment_content,"ä","ä");

update wp_comments set comment_content = replace(comment_content,"ö","ö");

update wp_comments set comment_content = replace(comment_content,"ß","ß");

update wp_comments set comment_content = replace(comment_content,"Ãœ","Ü");

update wp_comments set comment_content = replace(comment_content,"Ä","Ä");

update wp_comments set comment_content = replace(comment_content,"Ö","Ö");

Kommentar-Author

update wp_comments set comment_author = replace(comment_author,"ü","ü");

update wp_comments set comment_author = replace(comment_author,"ä","ä");

update wp_comments set comment_author = replace(comment_author,"ö","ö");

update wp_comments set comment_author = replace(comment_author,"ß","ß");

update wp_comments set comment_author = replace(comment_author,"Ãœ","Ü");

update wp_comments set comment_author = replace(comment_author,"Ä","Ä");

update wp_comments set comment_author = replace(comment_author,"Ö","Ö");

Link-Name

update wp_links set link_name = replace(link_name,"ü","ü");

update wp_links set link_name = replace(link_name,"ä","ä");

update wp_links set link_name = replace(link_name,"ö","ö");

update wp_links set link_name = replace(link_name,"ß","ß");

update wp_links set link_name = replace(link_name,"Ãœ","Ü");

update wp_links set link_name = replace(link_name,"Ä","Ä");

update wp_links set link_name = replace(link_name,"Ö","Ö");

Link-Beschreibung

update wp_links set link_description = replace(link_description,"ü","ü");

update wp_links set link_description = replace(link_description,"ä","ä");

update wp_links set link_description = replace(link_description,"ö","ö");

update wp_links set link_description = replace(link_description,"ß","ß");

update wp_links set link_description = replace(link_description,"Ãœ","Ü");

update wp_links set link_description = replace(link_description,"Ä","Ä");

update wp_links set link_description = replace(link_description,"Ö","Ö");

WP-Terms

update wp_terms set name = replace(name,"ü","ü");

update wp_terms set name = replace(name,"ä","ä");

update wp_terms set name = replace(name,"ö","ö");

update wp_terms set name = replace(name,"ß","ß");

update wp_terms set name = replace(name,"Ãœ","Ü");

update wp_terms set name = replace(name,"Ä","Ä");

update wp_terms set name = replace(name,"Ö","Ö");

WP-Term-Taxonomy

update wp_term_taxonomy set description = replace(description,"ü","ü");

update wp_term_taxonomy set description = replace(description,"ä","ä");

update wp_term_taxonomy set description = replace(description,"ö","ö");

update wp_term_taxonomy set description = replace(description,"ß","ß");

update wp_term_taxonomy set description = replace(description,"Ãœ","Ü");

update wp_term_taxonomy set description = replace(description,"Ä","Ä");

update wp_term_taxonomy set description = replace(description,"Ö","Ö");

Damit habe ich die Wordpress-Tabellen, in welche sich falsche Umlaute geschlichen hatten, dazu gebracht alle kaputten Umlaute durch die richtigen Umlaute zu ersetzen. Ich hoffe, ich kann damit noch anderen Menschen helfen!

PS: Natürlich sollte man auch darauf achten, dass in der wp_config.php der Zeichensatz für die Datenbank auf UTF8 gesetzt ist. Bei mir war das schon vorher der Fall :)

#Post-Inhalt update wp_posts set post_content = replace(post_content,"ü","ü"); update wp_posts set post_content = replace(post_content,"ä","ä"); update wp_posts set post_content = replace(post_content,"ö","ö"); update wp_posts set post_content = replace(post_content,"ß","ß"); update wp_posts set post_content = replace(post_content,"Ãœ","Ü"); update wp_posts set post_content = replace(post_content,"Ä","Ä"); update wp_posts set post_content = replace(post_content,"Ö","Ö"); #Post-Titel update wp_posts set post_title = replace(post_title,"ü","ü"); update wp_posts set post_title = replace(post_title,"ä","ä"); update wp_posts set post_title = replace(post_title,"ö","ö"); update wp_posts set post_title = replace(post_title,"ß","ß"); update wp_posts set post_title = replace(post_title,"Ãœ","Ü"); update wp_posts set post_title = replace(post_title,"Ä","Ä"); update wp_posts set post_title = replace(post_title,"Ö","Ö"); #Kommentar-Inhalt update wp_comments set comment_content = replace(comment_content,"ü","ü"); update wp_comments set comment_content = replace(comment_content,"ä","ä"); update wp_comments set comment_content = replace(comment_content,"ö","ö"); update wp_comments set comment_content = replace(comment_content,"ß","ß"); update wp_comments set comment_content = replace(comment_content,"Ãœ","Ü"); update wp_comments set comment_content = replace(comment_content,"Ä","Ä"); update wp_comments set comment_content = replace(comment_content,"Ö","Ö"); #Kommentar-Author update wp_comments set comment_author = replace(comment_author,"ü","ü"); update wp_comments set comment_author = replace(comment_author,"ä","ä"); update wp_comments set comment_author = replace(comment_author,"ö","ö"); update wp_comments set comment_author = replace(comment_author,"ß","ß"); update wp_comments set comment_author = replace(comment_author,"Ãœ","Ü"); update wp_comments set comment_author = replace(comment_author,"Ä","Ä"); update wp_comments set comment_author = replace(comment_author,"Ö","Ö"); #Link-Name update wp_links set link_name = replace(link_name,"ü","ü"); update wp_links set link_name = replace(link_name,"ä","ä"); update wp_links set link_name = replace(link_name,"ö","ö"); update wp_links set link_name = replace(link_name,"ß","ß"); update wp_links set link_name = replace(link_name,"Ãœ","Ü"); update wp_links set link_name = replace(link_name,"Ä","Ä"); update wp_links set link_name = replace(link_name,"Ö","Ö"); #Link-Beschreibung update wp_links set link_description = replace(link_description,"ü","ü"); update wp_links set link_description = replace(link_description,"ä","ä"); update wp_links set link_description = replace(link_description,"ö","ö"); update wp_links set link_description = replace(link_description,"ß","ß"); update wp_links set link_description = replace(link_description,"Ãœ","Ü"); update wp_links set link_description = replace(link_description,"Ä","Ä"); update wp_links set link_description = replace(link_description,"Ö","Ö"); #WP-Terms update wp_terms set name = replace(name,"ü","ü"); update wp_terms set name = replace(name,"ä","ä"); update wp_terms set name = replace(name,"ö","ö"); update wp_terms set name = replace(name,"ß","ß"); update wp_terms set name = replace(name,"Ãœ","Ü"); update wp_terms set name = replace(name,"Ä","Ä"); update wp_terms set name = replace(name,"Ö","Ö"); #WP-Term-Taxonomy update wp_term_taxonomy set description = replace(description,"ü","ü"); update wp_term_taxonomy set description = replace(description,"ä","ä"); update wp_term_taxonomy set description = replace(description,"ö","ö"); update wp_term_taxonomy set description = replace(description,"ß","ß"); update wp_term_taxonomy set description = replace(description,"Ãœ","Ü"); update wp_term_taxonomy set description = replace(description,"Ä","Ä"); update wp_term_taxonomy set description = replace(description,"Ö","Ö");