Is there a MySQL utf8 assembly that doesn't combine characters with an accent?

Question

I have a utf8 table and store data like:

+-------+--------+ | name | gender | +-------+--------+ | ESMÉ | F | | ESME | F | +-------+--------+ 

However, when I try to add a unique key (name, gender), these two lines violate the restriction. I was able to achieve my goal using utf8_bin sort, but then I lose case sensitivity.

So, I assume that I'm really looking for a way to save utf8 data, but it does not have accented and unstressed characters that will be considered equivalent in my unique key.

Answer 1

I do not think that what you want is directly possible. Sorting determines how you sort and compare your date. For your key, you want the two values ​​to be perceived as different, but in your normal comparison, you want to be perceived as the same thing.

There is a trick around this, but when specifying your queries: just cast your results to utf8 without specifying sorting. This will start the result and return the default setting.

The following example shows what I mean:

 mysql> show create table test_col; +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test_col | CREATE TABLE `test_col` ( `name` varchar(200) COLLATE utf8_bin DEFAULT NULL, UNIQUE KEY `ixuniq` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin | +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> insert into test_col (name) values ('Y'),(unhex('c39d'));Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test_col; +------+ | name | +------+ | Y | | Ý | +------+ 2 rows in set (0.00 sec) mysql> select * from test_col where name='Y'; +------+ | name | +------+ | Y | +------+ 1 row in set (0.00 sec) mysql> select * from test_col where convert(name using 'utf8')='y';+------+ | name | +------+ | Y | | Ý | +------+ 2 rows in set (0.00 sec) 

Answer 2

I have exactly the same problem, matching exists in Latin, it is latin1_general_ci, but does not exist in utf8. You have 3 solutions:

• Use latin
• create or find the one who created the sorting http://dev.mysql.com/doc/refman/5.0/en/ldml-collation-example.html (and let me know ;-)
• use UPPERCASE when comparing characters (or inserting them)