如何在 MySql 中进行重音敏感搜索

2021-11-20 00:00:00 utf-8 mysql

我有一个带有 utf8 通用 ci 排序规则的 MySQL 表.在表格中，我可以看到两个条目:

I have a MySQL table with utf8 general ci collation. In the table, I can see two entries:

糟糕
废话

我使用的查询如下所示:

I am using a query that looks like this:

SELECT * FROM `words` WHERE `word` = 'abád'

查询结果给出了两个词:

The query result gives both words:

糟糕
废话

有没有办法表明我只希望 MySQL 找到带重音的单词?我希望查询只返回

Is there a way to indicate that I only want MySQL to find the accented word? I want the query to only return

废话

我也试过这个查询:

SELECT * FROM `words` WHERE BINARY `word` = 'abád'

它没有给我任何结果.谢谢你的帮助.

It gives me no results. Thank you for the help.

推荐答案

如果您对该字段的搜索总是对重音敏感，则将该字段的排序规则声明为 utf8_bin(这将比较 utf8-encoded 字节)或使用特定于语言的排序规则来区分重音字符和非重音字符.

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.

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

相关文章