0

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.

Valentin Grégoire
  • 1,110
  • 2
  • 12
  • 29
  • It is possible to fix the data. But first, provide `SELECT col, HEX(col) FROM ...` to sample how bad the data is. And provide `SHOW CREATE TABLE`. (There are multiple scenarios; need to find which one before launching into the right or wrong solution.) – Rick James Jul 27 '16 at 18:03

2 Answers2

1

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.

Community
  • 1
  • 1
Jakumi
  • 8,043
  • 2
  • 15
  • 32
  • I am not using PHP. When I run my select command in my MySQL Workbench, the entries are wrong. Perhaps, in the insert statement, I need to provide an encoding or something. – Valentin Grégoire Jul 27 '16 at 07:49
  • that's likely the right approach. i updated my post, usually it's just the retrieval ... – Jakumi Jul 27 '16 at 07:55
0

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.

Valentin Grégoire
  • 1,110
  • 2
  • 12
  • 29