Re: Special Characters (äöüß), switching to UTF8, collation-problems

[Rick James]

You probably failed to set the character settings for the client and the connection. See "SET NAMES utf8;" See
http://mysql.rjweb.org/doc.php/charcoll
for more discussion. That can also explain how to discover whether the data is 'corrupt' or merely the display of the data.

> However all collations for UTF8 are not case-sensitive except for the bin-collation.

With utf8, there are 2 main choices in MySQL:
* Ignore encoding (utf8_bin), just look at the bits.
* Case insensitive and accent insensitive: utf8_..._ci

With latin1 (which covers only Western European characters, as you mentioned), there is also a "cs" (case sensitive) option:

mysql> SHOW COLLATION LIKE 'latin1%';
+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         | Yes      |       1 |
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       1 |
| latin1_danish_ci  | latin1  | 15 |         | Yes      |       1 |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
| latin1_bin        | latin1  | 47 |         | Yes      |       1 |
| latin1_general_ci | latin1  | 48 |         | Yes      |       1 |
| latin1_general_cs | latin1  | 49 |         | Yes      |       1 |
| latin1_spanish_ci | latin1  | 94 |         | Yes      |       1 |
+-------------------+---------+----+---------+----------+---------+

http://mysql.rjweb.org/doc.php/charcoll