How to Fix a MySQL Character Encoding Mismatch in WordPress

An upgrade to a newer WordPress version, or (as in my case) to a newer version of MySQL, might trigger a problem which has been described since version 2.2: old UTF-8 characters are then represented by strange characters, say “é” will appear instead of “é”.

After reading some posts (e.g. this or this one) about how to fix it, I decided to solve it using one of the simple plugins out there. I tried both, UTF-8 Database converter and WP Sanitize Plugin.

But they just converted the database encoding the simple way. Which was fine, since I don’t care about non-European characters that much. However, there were still a lot of obvious character encoding errors, because the binary encoding was still wrong in many cases.

So I made multiple SQL queries to replace the wrong characters by their correct counterparts, and there it is: I fixed it without using the Shell commands of the aforementioned posts.

If you have the same problem, it will take you seconds to fix your character encoding issue, after having used one of the plugins above (say, UTF-8 Sanitize) and followed its instructions. Then execute the following SQL queries within your blog’s database:

Beware!

  1. Do not change the position of the last query: it should remain the last one.
  2. In that last query, for the wrong character “Ô, you have to choose which of these characters you want it substituted for: “à” (default, preferred if you write mostly in French), “Á”, “í”, “Í” (probably better choices for Spanish blogs), or even “Ï” or “Ý”.

I know, I know, it’s not a perfect solution – but it was indeed easier for me…

Leave a Reply

Your email address will not be published.