The unique Mysql index does not work on a specific umlaut

Question

I have a user table in which there is a column named "nickname", utf-8, varchar (20), the table is in InnoDB. There are 2 entries that have a nickname = 'gunni' and another alias = 'günni'. When I tried to apply a unique index to this column, mysql gave me this error:

ERROR 1062 (23000) on line 263: Duplicate entry 'gunni' for key 2

I checked the data there is only one record named "gunni", and if I changed the record "günni" to something else, then again apply a unique index, everything will be fine.

Why are günni and gunni duplicates? Here are the hexadecimal values ​​for them, I get this with the mysql hex () function:

gunni → 67756E6E69

günni → 67C3BC6E6E69

They are clearly different. Why does mysql treat these 2 as the same? Or is there something I don't know about unique indexes? Or even, could this be a mysql error?

Answer 1

This is because of the sorting you are using.

Everything that ends with _ci is case insensitive (and also does not require accent / umlaut). So yes, MySQL will consider "günni" and "gunni" the same, unless you change your sorting.

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