MySQL 将 ÅÄÖ 视为 AAO?

2021-11-20 00:00:00 unicode encoding mysql collation

这两个查询给了我完全相同的结果:

These two querys gives me the exact same result:

select * from topics where name='Harligt';
select * from topics where name='Härligt';

这怎么可能?似乎 mysql 在搜索时将 åäö 转换为 aao.有什么办法可以关闭它吗?

How is this possible? Seems like mysql translates åäö to aao when it searches. Is there some way to turn this off?

据我所知，我在任何地方都使用 utf-8 编码.终端和php都出现同样的问题.

I use utf-8 encoding everywhere as far as i know. The same problem occurs both from terminal and from php.

推荐答案

是的，这是非特定于语言的 unicode 排序规则中的标准行为.

Yes, this is standard behaviour in the non-language-specific unicode collations.

9.1.13.1.Unicode 字符集

为了进一步说明，以下等式在 utf8_general_ci 和 utf8_unicode_ci 中都成立(有关在比较或进行搜索时的效果，请参阅第 9.1.7.7 节，整理效果示例"):

To further illustrate, the following equalities hold in both utf8_general_ci and utf8_unicode_ci (for the effect this has in comparisons or when doing searches, see Section 9.1.7.7, "Examples of the Effect of Collation"):

Ä = AÖ = Oü = U

Ä = A Ö = O Ü = U

另见整理效果示例

你需要

  • 使用没有这个特性"的归类(即 utf8_bin，但是有其他后果)

  • use a collation that doesn't have this "feature" (namely utf8_bin, but that has other consequences)

使用不同的排序规则仅用于查询.这应该有效:

use a different collation for the query only. This should work:

 select * from topics where name='Harligt' COLLATE utf8_bin;

如果你想做一个不区分大小写的 LIKE 但不有 Ä = A 元音转换，这会变得更加困难.我知道没有不区分大小写的 mySQL 排序规则，并且不会进行这种隐式变音转换.如果有人知道，我很想听听.

it becomes more difficult if you want to do a case insensitive LIKE but not have the Ä = A umlaut conversion. I know no mySQL collation that is case insensitive and does not do this kind of implicit umlaut conversion. If anybody knows one, I'd be interested to hear about it.

相关:

  • 寻找不区分大小写的 MySQL 排序规则 where a" != "ä"
  • MYSQL 区分大小写搜索 utf8_bin 字段

相关文章