MySQL 认为 'е' 和 'ё' 相等，我如何设置它以将它们视为不同? [英] MySQL considers 'е' and 'ё' equal, how do I set it to consider them different?

问题描述

我有一个对 varchar 字段具有唯一约束的表.当我尝试在两个不同的行中插入 'e' 和 'ё' 时，系统会提示我违反唯一约束.执行下面的 select 表明 MySQL 认为这些字母是等效的，尽管它们的 HEX 值分别是 D0B5 和 D191.

I have a table with a unique constraint on a varchar field. When I try to insert 'e' and 'ё' in two different rows I'm prompted with a unique constraint violation. Executing the following select shows that MySQL considers the letters equivalent in spite of their HEX values being D0B5 and D191 respectively.

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

经过大量的谷歌搜索后，我发现了 this MySQL 错误报告，其中似乎处理这个问题.Sveta Smirnova 的最后回复指出，这种行为是设计使然，并参考了 Collat​​ionutf8_unicode_ci 图表，欧洲字母表 (MySQL 6.0.4).

Following a fair amount of Googling I came across this MySQL bug report which seems to deal with this issue. The very last response by Sveta Smirnova states that this behavior is by design and refers to the Collation chart for utf8_unicode_ci, European alphabets (MySQL 6.0.4).

为了查询目的，我如何告诉 MySQL 'е' 不等于 'ё' 以及如何更改唯一约束以注意这一事实?

How do I tell MySQL that 'е' is not equal to 'ё' for query purposes and how do I change the unique constraint to take note of this fact?

推荐答案

您可能希望查看此答案:是否可以删除mysql表的排序规则?

You may wish to check this answer: Is it possible to remove mysql table collation?

您所看到的行为是标准行为.在大多数情况下，它会产生最好的结果.从一个兴趣点来看，您是否有一个示例说明这是如何给您带来问题的.除了 diacritic 之外，您是否找到了两个匹配的词?

The behavior you're seeing is standard. In most cases it produces the best results. From a point of interest do you have an example of how this is causing a problem for you. Have you found two words which match except for the diacritic?

无论哪种方式，您唯一能做的就是更改排序规则.这可以在服务器、数据库、表甚至字段级别完成.

Either way the only thing you can do about it is to change the collation. This can be done at the server, database, table or even field level.

而不是我复制有关如何执行此操作的手册；请点击此链接:http://dev.mysql.com/doc/refman/5.7/en/charset-syntax.html

Rather than my duplicating the manual on how to do this; please follow this link: http://dev.mysql.com/doc/refman/5.7/en/charset-syntax.html

这里列出了支持的不同排序规则:http://dev.mysql.com/doc/refman/5.5/en/charset-charsets.html

There's a listing here of the different collations supported: http://dev.mysql.com/doc/refman/5.5/en/charset-charsets.html

这篇关于MySQL 认为 'е' 和 'ё' 相等，我如何设置它以将它们视为不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！