mysql - MySQL 如何在 UTF-8 中工作 "case insensitive"和 "accent insensitive"

Question

我在“utf8 -- UTF-8 Unicode”中有一个模式作为字符集和“utf8_spanish_ci”的排序规则。

所有内部表都是 InnoDB，具有与上述相同的字符集和排序规则。

问题来了:

像这样的查询

SELECT *
FROM people p
WHERE p.NAME LIKE '%jose%';

我得到 83 个结果行。我应该有 84 个结果，因为我知道。

更改位置:

WHERE p.NAME LIKE '%JOSE%';

我得到完全相同的 83 行。使用 JoSe、Jose、JOSe 等组合。报告所有相同的 83 行。

当游戏中出现口音时，问题就来了。如果这样做:

WHERE p.NAME LIKE '%josé%';

我没有得到任何结果。 0 行。

但如果我这样做:

WHERE p.NAME LIKE '%JOSÉ%';

我只得到一个结果行，所以 1 行。这是唯一带有重音“jose”并大写的行。

我已经尝试过使用 josÉ、JoSÉ 或我所做的任何组合，只要重音字母保持大写或不大写，因为它确实存储在数据库中并且它仍然返回唯一的行。如果我突然将“É”更改为“é”，无论我在 JOSE 中对大写进行何种组合，它都不会返回任何行。

所以结论:

• 如果游戏中没有拉丁字符，则不区分大小写。
• 如果出现拉丁字符，则区分大小写。
• 区分重音，就像我搜索 JOSE 或 jose 一样，我只得到 83 行，而不是我需要的 84 行。

我想要什么？

• 要搜索“jose”、“JOSE”、“José”、“JOSÉ”、“JÒSE”、“jöse”、“JoSÈ”，...必须返回我知道存在的 84 行。我如何将我的搜索变成不区分大小写和不区分“拉丁语”。

LIKE 上的COLLATION 等解决方案对我不起作用，不知道为什么...

我能做什么？

编辑:

如果我这样做:

WHERE p.NAME LIKE '%jose%' COLLATE utf8_general_ci;

我得到错误:

COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

而且我还更改了列上所有可能的排序规则!

如果我做类似的事情:

WHERE p.NAME LIKE _utf8 '%jose%' COLLATE utf8_general_ci;

报同样的83行，好像我什么都没弄...

Answer 1

您已经尝试使用不区分重音的排序规则进行搜索和排序。

http://dev.mysql.com/doc/refman/5.0/en/charset-collation-implementations.html

问题是，您的 NAME 列似乎存储在 latin1(8 位)字符集中。这就是为什么 mySQL 会这样对你发牢骚:

  COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

如果你尝试，你可能会得到你想要的结果

 WHERE CONVERT(p.NAME USING utf8) LIKE _utf8 '%jose%' COLLATE utf8_general_ci;

但是，要小心!

当您在 WHERE 语句中的列上使用任何类型的函数(在此示例中为 CONVERT)时，您会挫败 MySQL 使用索引优化搜索的尝试。如果这个项目变得很大(也就是说，如果您的表中有很多行)，您需要以 utf8 格式存储数据，而不是 latin1。 (您可能已经知道您的 LIKE '%whatever%' 搜索词也会破坏 MySQL 的索引。)