MySQL believes that "e" and "e" are equal, how can I set them to consider them different?

Question

I have a table with a unique constraint in the varchar field. When I try to insert "e" and "e" in two different lines, I am offered a unique violation of restrictions. Performing the following select shows that MySQL counts equivalent letters, even though their HEX values ​​are equal to D0B5 and D191, respectively.

 select '' = '', hex(''), hex(''); 

After a lot of Googling, I came across this MySQL error report, which seems to handle this problem. Sveta Smirnova’s most recent answer states that this design behavior is related to the sorting diagram for utf8_unicode_ci, European alphabets (MySQL 6.0.4) .

How do you tell MySQL that "e" is not equal to "e" for query purposes and how to change the unique constraint to take note of this fact?

Answer 1

You can check this answer: Is it possible to remove the mysql table mapping?

The behavior you see is standard. In most cases, it gives the best results. In terms of interest, you have an example of how this causes a problem for you. Did you find two words that match, except diacritic ?

In any case, the only thing you can do is change the sorting. This can be done at the server, database, table, or even field level.

Instead of duplicating a guide on how to do this; follow this link: http://dev.mysql.com/doc/refman/5.7/en/charset-syntax.html

The various supported mappings are listed here: http://dev.mysql.com/doc/refman/5.5/en/charset-charsets.html

Answer 2

If you need this for a special field, you can add a duplicate column with a different sort to prevent this problem.

 ALTER TABLE yourTable ADD COLUMN `copiedColumn` VARCHAR(100) CHARACTER SET 'binary' COLLATE 'binary'; 

In addition, you can change the sorting of the column if you do not need the current sorting in this field.

ALTER TABLE yourTable CHANGE COLUMN yourColumn yourColumn VARCHAR (100) CHARACTER SET 'binary' COLLATE 'binary';