Trouble with umlauts and accents, collations and the convert-command

Hello there,

I’ve got a database here and realised search requests delivering equal results for “y” and “ü”, as well as “e” and “é” etc.
The website is in German, the entries in the database above all German, French and English. So a confusion with the chars mentioned above is not allowed to happen.
I think I spotted the problem: collation of the mysql-connection and the table: utf8_general_ci; collation of the fields in the table: latin1_swedish_ci.

For testing purposes I set up a database with everything set to latin1_german2_ci. On entering new data there is no more confusion, e.g. “Ethanol” and “éthanole”.
Unfortunately simply inserting the data from the original database does not work. Several special characters are now shown as placeholders and the confusion persists.

I tried to use mysql’s convert-command, but somehow I do not succeed in altering the encoding.

Am I right by blaming the trouble on the collations? If yes, how do I convert the data in a way that the confusions above do not happen anymore? If no, what else can I do about it?

Thanks in advance.

Sincerely yours.

Character set and collation are two different things. If the character set is OK, then you do not want to try to convert anything. You only want to change the collation (which defines sort order).

http://dev.mysql.com/doc/refman/5.0/en/charset-column.html

Thank you so far, but unfortunately this only solves half of my problem.

I forgot to mention that I already tried to set the table and its fields to charset latin1 and collation latin1_german2_ci.
This way, there is no more confusion between “y” and “ü” but “é” (and every other accent) and “e” are still the same for a SELECT-query.

I don’t know how to fix this.