Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

If I add both then the uniqueness constraints fail, even though my field is in UTF-8. What can be done? I'm running 5.5.16

mysql> show create table foo_person;
+------------+------------------------------------------------+
| Table      | Create Table                                   |
+------------+------------------------------------------------+
| foo_person | CREATE TABLE `foo_person` (
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+------------+------------------------------------------------+

mysql> INSERT INTO foo_person (`name`) VALUES ('resumé');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO foo_person (`name`) VALUES ('resume');
ERROR 1062 (23000): Duplicate entry 'resume' for key 'PRIMARY'

mysql> select * from foo_person;
+---------+
| name    |
+---------+
| resumé  |
+---------+
1 row in set (0.00 sec)
share|improve this question
    
First thing would be to reconsider using a text field for the primary key - numeric keys typically offer much better performance, and you wouldn't have the e/é issue –  Max Vernon Aug 9 '13 at 23:10
    
@MaxVernon He would still have the same issue, if he wanted to apply a Unique constraint on the name column. –  ypercube Aug 10 '13 at 9:16
    
@ypercube Correct and adding to that, SELECT * FROM foo_person WHERE name='resume' would return 2 results. Which is unwanted since they are two different words. –  Kit Sunde Aug 10 '13 at 9:25
1  
@MaxVernon: Is there a logical explanation for integer keys offering better performance over text keys or is it just theory? when an index is created(on primary key column here), i don't think the actual values are used anywhere, index just holds pointers to the rows and so i say there's no difference in performance. I have a table with about 4.7M rows with a primary key on VARCHAR column and it's equally fast and it actually saves me space on additional column for id. –  Fr0zenFyr Aug 13 '13 at 8:18
    
@Fr0zenFyr I was curious about that as well, this could be another question. I'd up vote it. –  Kit Sunde Aug 13 '13 at 8:43
show 5 more comments

1 Answer 1

up vote 3 down vote accepted

It seems that MySQL does not support Accent Sensitive collations. (See http://dev.mysql.com/doc/refman/5.6/en/charset-collation-implementations.html)

Therefore your only option is to go with a binary collation, e.g. utf8_bin. However, that would also make the column case sensitive, which is different from your current collation.

See also http://stackoverflow.com/questions/500826/how-to-conduct-an-accent-sensitive-search-in-mysql

share|improve this answer
    
I winder if this can be used to define a collation that meets the requirements (case insensitive and accent sensitive): Adding a UCA Collation to a Unicode Character Set –  ypercube Aug 10 '13 at 9:29
    
Thank you that was helpful, the question linked to bugs.mysql.com/bug.php?id=19567 which covers this issue. I believe I can (for this particular use case) switch to utf8_bin as everything is lowercased. –  Kit Sunde Aug 10 '13 at 9:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.