MySQL encoding weird characters

Question

As many people already had, I have a problem in MySQL with the encoding of my data. More specifically, the collation of the table seems to be utf8_general_ci. The data inserted is inserted well, but when a select is done, some characters get translated badly:

Marie-Thérèse becomes Marie-Thérèse.

Is it possible to do a select and translate these characters back to the original value, or is it impossible? It's harder to change the original table in my case, so I'd rather solve it in my select query.

Answer 1

In the end it seems like the problem was with running it all through a cronbjob. We run a script through a cronjob that generates the insert statements. Apparently, when running the script manually, everything goes well, but when running the same script through a cronjob, the data got messed up. We solved that by this article:http://www.logikdev.com/2010/02/02/locale-settings-for-your-cron-job/ We had to add a variable LANG in the etc/environment file.

Answer 2

When using phpmyadmin (or the like) and looking at those entries, are those entries okay?

update: if not, the inserts are probably flawed already, and the connection from the insertion script must be adapted.

If so, then it's not technically MySQL's fault but the software connecting to it. See for example: UTF-8 all the way through . You have to set some parameters on/after opening the connection.

btw: The collation should be irrelevant. http://dev.mysql.com/doc/refman/5.7/en/charset-general.html

The gist is: a collation tells the you, how you have to order/compare strings, which is mainly important for special characters like äöü in German or àéô in French/... because their local/regional collation say, ä is - for ordering purposes - exactly like a (for example), in another collation, ä could be distinctly after a or even after z.