Mysql unique index doesn't work on a certain umlaut

Question

I have a users table in which there's a column called 'nickname', utf-8 encoded, varchar(20), the table is in InnoDB. There're 2 records one has a nickname = 'gunni' and the other nickname = 'günni'. When I tried to apply a unique index onto this column, mysql gave me this error :

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

I checked the data there's only one record that has the name 'gunni', and if I change the 'günni' record to something else then apply the unique index again, everything works fine.

How come 'günni' & 'gunni' be duplicates? Here is the hex values for them, I get this with mysql's hex() function :

gunni -> 67756E6E69

günni -> 67C3BC6E6E69

They're obviously different. How come mysql treats these 2 as the same? Or is there something I don't know about unique indexes? Or even, could this be a mysql bug?

Enwomb

which storage engine are you using? – Bradleigh

@sAc: storage engine = InnoDB – Enwomb

Answer 1

It's because of the collation you are using.

Anything that ends with _ci is case-insensitive (and also accent/umlaut insensitive). So yes, MySQL will consider "günni" and "gunni" the same thing, unless you change your collation.

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

Paraph

Yes, utf8_bin works. Also tried latin_general_ci which works. But utf8_general_ci didn't. – Enwomb

Here's the collation fix for the impatient: CREATE TABLE mytbl (...) COLLATE utf8_bin;. Or use for individual columns. – Nephology