Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql result is "special character"-insensitive

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

like image 597
Bob Singor Avatar asked Feb 18 '10 09:02

Bob Singor


1 Answers

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.

like image 56
hobbs Avatar answered Sep 18 '22 13:09

hobbs