mysql result is "special character"-insensitive

Question

Hi,

It seems that when i alter a mysql table (on a utf-8 table/columns) unique that it returns a duplicate entry error.

Example:

ALTER TABLE name ADD UNIQUE(name)

error:

Duplicate entry 'Adé' for key 'name_UNIQUE'

I think it's because of the follow to rows in my database

Ade, Adé

Is it possible to alter a table unique with special characters?

Thank you,

Bob

Answer 1

Instead of using the = operator, you may want to use LIKE or LIKE BINARY

More info here.

Answer 2

You need to set the collation on the column (or the whole table or the database -- I don't think connection scope will work for unique constraints) to one that respects the difference between "e" and "é". See here for an example of the effects that different collation settings can have.

Assuming that your charset is utf8, the safest collation you can use to avoid getting false-positives on your unique constraint would be utf8_bin, in which two strings never compare equal unless they're the identical string. You'll need to be careful about things like unicode normalization, though, or else you might end up with two keys that are the identical sequence of characters, in different normalization forms, which makes them unequal. Just a little thing to watch out for.