Character encoding issues in MySQL

Question

In my database we have fields where the data is not readable. I now know why it happened but I don't know how to fix it.

I found a way to get the info back from the database:

SELECT id,
       name 
  FROM projects 
 WHERE LENGTH(name) != CHAR_LENGTH(name);

One of the rows returned shows:

id   | name
-------------------------
1008 | Cajón el Diablo

This should be:

id   | name
-------------------------
1008 | Cajón el Diablo

Can somebody help me figure out how to fix this problem? How can I convert this using SQL? Is SQL not good? If not, how about Python?

Answer 1

Your mySQL data is most likely UTF-8 encoded.

The tool or client you are viewing the data with is either

• Not talking to the mySQL server in UTF-8 (SET NAMES utf8)

• Outputting UTF-8 characters in an environment that has an encoding different from UTF-8 (e.g. a web page encoded in ISO-8859-1).

You need to either specify the correct character set when connecting to the mySQL database, or convert the incoming characters so they can be output correctly.

For more information, you would have to tell us what collation your database and tables is in, and what you are using to look at the data.

If you want to get into the basics of this, this is very good reading: The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)