如何在 MySql 中进行重音敏感搜索
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/500826/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to conduct an Accent Sensitive search in MySql
提问by Chris
I have a MySQL table with utf8 general ci collation. In the table, I can see two entries:
我有一个带有 utf8 通用 ci 排序规则的 MySQL 表。在表中，我可以看到两个条目：
abad
abád
阿巴德
阿巴德
I am using a query that looks like this:
我正在使用如下所示的查询：
SELECT * FROM `words` WHERE `word` = 'abád'
The query result gives both words:
查询结果给出了两个词：
abad
abád
阿巴德
阿巴德
Is there a way to indicate that I only want MySQL to find the accented word? I want the query to only return
有没有办法表明我只希望 MySQL 找到带重音的单词？我希望查询只返回
abád
阿巴德
I have also tried this query:
我也试过这个查询：
SELECT * FROM `words` WHERE BINARY `word` = 'abád'
It gives me no results. Thank you for the help.
它没有给我任何结果。感谢您的帮助。
回答by
If your searches on that field are always going to be accent-sensitive, then declare the collation of the field as utf8_bin (that'll compare for equality the utf8-encoded bytes) or use a language specific collation that distinguish between the accented and un-accented characters.
如果您对该字段的搜索始终对重音敏感，则将该字段的排序规则声明为 utf8_bin（将比较 utf8 编码字节的相等性）或使用特定于语言的排序规则来区分重音符号和非- 重音字符。
col_name varchar(10) collate utf8_bin
If searches are normally accent-insensitive, but you want to make an exception for this search, try;
如果搜索通常不区分重音，但您想为此搜索设置例外，请尝试；
WHERE col_name = 'abád' collate utf8_bin
回答by David
In my version (MySql 5.0), there is not available any utf8 charset collate for case insensitive, accent sensitive searches. The only accent sensitive collate for utf8 is utf8_bin. However it is also case sensitive.
在我的版本 (MySql 5.0) 中，没有任何 utf8 字符集整理可用于不区分大小写、区分重音的搜索。utf8 唯一区分重音的校对是 utf8_bin。但是，它也区分大小写。
My work around has been to use something like this:
我的工作是使用这样的东西：
SELECT * FROM `words` WHERE LOWER(column) = LOWER('aBád') COLLATE utf8_bin
回答by colan
The MySQL bug, for future reference, is http://bugs.mysql.com/bug.php?id=19567.
供以后参考的 MySQL 错误是http://bugs.mysql.com/bug.php?id=19567。
回答by Mike
Check to see if the database table collation type end with "_ci", This stands for case insensitive...
检查数据库表整理类型是否以“_ci”结尾，这代表不区分大小写...
Change it to collation the the same or nearest name without the "_ci" ...
将其更改为不带“_ci”的相同或最接近的名称的排序规则...
For example... change "utf8_general_ci" to "utf8_bin" Mke
例如...将“utf8_general_ci”更改为“utf8_bin”Mke
回答by Robert Sinclair
Accepted answer is good, but beware that you may have to use COLLATE utf8mb4_bin instead!
接受的答案很好，但请注意，您可能必须改用 COLLATE utf8mb4_bin ！
WHERE col_name = 'abád' collate utf8mb4_bin
Above fixes errors like:
以上修复了以下错误：
MySQL said: Documentation 1253 - COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'
MySQL 说：文档 1253 - COLLATION 'utf8_bin' 对 CHARACTER SET 'utf8mb4' 无效
回答by Silvio Delgado
I was getting the same error.
我遇到了同样的错误。
I've changed the collation of my table to utf8_bin (through phpMyAdmin) and the problem was solved.
我已将表的排序规则更改为 utf8_bin（通过 phpMyAdmin），问题解决了。
Hope it helps! :)
希望能帮助到你！:)
回答by Mónica Cifuentes
That works for me for an accent insensitive and case insensitive search in MySql server 5.1 in a database in utf8_general_ci, where column is a LONGBLOB.
这适用于我在 utf8_general_ci 中的数据库中的 MySql server 5.1 中进行不区分重音和不区分大小写的搜索，其中列是 LONGBLOB。
select * from words where '%word%' LIKE column collate utf8_unicode_ci
with
和
select * from words where'%word%' LIKE column collate utf8_general_ci
the result is case sensitive but not accent sensitive.
结果区分大小写但不区分重音。
回答by cjk
SELECT * FROM `words` WHERE column = 'abád' collate latin1_General_CS
(or your collation including cs)
（或您的整理，包括 cs）
回答by ólafur Waage
You can try searching for the hex variable of the character, HEX() within mysql and use a similar function within your programming language and match these. This worked well for me when i was doing a listing where a person could select the first letter of a person.
您可以尝试在 mysql 中搜索字符的十六进制变量 HEX() 并在您的编程语言中使用类似的函数并匹配这些。当我做一个人可以选择一个人的第一个字母的列表时，这对我很有效。
回答by Tonci Grgin
Well, you just described what utf8_general_ci collation is all about (a, á, à, a, ?, ? all equals to a in comparison).
好吧，您刚刚描述了 utf8_general_ci 排序规则的全部内容（a, á, à, a, ?, ? all equals to a 比较）。
There have also been changes in MySQL server 5.1 in regards to utf8_general_ci and utf8_unicode_ci so it's server version dependent too. Better check the docs.
MySQL 服务器 5.1 在 utf8_general_ci 和 utf8_unicode_ci 方面也发生了变化，因此它也依赖于服务器版本。最好检查文档。
So, If it's MySQL server 5.0 I'd go for utf8_unicode_ci instead of utf8_general_ci which is obviously wrong for your use-case.
因此，如果它是 MySQL 服务器 5.0，我会选择 utf8_unicode_ci 而不是 utf8_general_ci，这对于您的用例来说显然是错误的。
