blocks|key|1636939|text|如果对该字段搜索总是区分重音，那么将该字段的排序规则声明为utf8_bin+(这将比较utf8编码的字节是否相等)，或者使用区分重音字符和非重音字符的特定于语言的排序规则。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1636940|col_name+varchar(10)+collate+utf8_bin|code-block|syntax|javascript|1636941|如果搜索通常是不区分重音的，但您想为此搜索破例，请尝试；|1636942|WHERE+col_name+=+'abád'+collate+utf8_bin|1636943|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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. 

<pre><code>col_name varchar(10) collate utf8_bin
</code></pre>

If searches are normally accent-insensitive, but you want to make an exception for this search, try;

<pre><code>WHERE col_name = 'abád' collate utf8_bin
</code></pre>

blocks|key|1637059|text|在我的版本(MySql+5.0)中，没有任何utf8字符集collate可用于不区分大小写、区分重音的搜索。utf8唯一区分重音的排序规则是utf8_bin。但是，它也区分大小写。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1637060|我的工作就是使用这样的东西：|1637061|SELECT+*+FROM+`words`+WHERE+LOWER(column)+=+LOWER('aBád')+COLLATE+utf8_bin|code-block|syntax|javascript|1637062|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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.

My work around has been to use something like this:

<pre><code>SELECT * FROM `words` WHERE LOWER(column) = LOWER('aBád') COLLATE utf8_bin
</code></pre>

blocks|key|1637138|text|为了便于将来参考，MySQL的bug是http://bugs.mysql.com/bug.php?id=19567。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1637139|entityMap|0|LINK|mutability|MUTABLE|url|http://bugs.mysql.com/bug.php?id=19567^0|J|12|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

The MySQL bug, for future reference, is <a href="http://bugs.mysql.com/bug.php?id=19567" rel="nofollow">http://bugs.mysql.com/bug.php?id=19567</a>.

blocks|key|1637236|text|检查数据库表排序规则类型是否以"_ci“结尾，这表示不区分大小写...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1637237|将其更改为排序规则相同或最近的名称，但不带"_ci“...|1637238|例如..。将"utf8_general_ci“更改为"utf8_bin”Mke|1637239|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Check to see if the database table collation type end with &quot;_ci&quot;, This stands for case insensitive...
Change it to collation the same or nearest name without the &quot;_ci&quot; ...
For example... change &quot;utf8_general_ci&quot; to &quot;utf8_bin&quot;
Mke

blocks|key|1637174|text|我也收到了同样的错误。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1637175|我已经将我的表的排序规则更改为utf8_bin+(通过phpMyAdmin)，这个问题就解决了。|1637176|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

I was getting the same error.
I've changed the collation of my table to utf8_bin (through phpMyAdmin) and the problem was solved.

blocks|key|1421968|text|SELECT+*++FROM+`words`+WHERE+column+=+'abád'+collate+latin1_General_CS+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1421969|(或包括cs的排序规则)|unstyled|1421970|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>SELECT * FROM `words` WHERE column = 'abád' collate latin1_General_CS 
</code></pre>

(or your collation including cs)

blocks|key|1636871|text|您可以尝试在mysql中搜索字符的十六进制变量HEX()，并在您的编程语言中使用类似的函数并进行匹配。当我在一个列表中，一个人可以选择一个人的第一个字母时，这对我来说很有效。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1636872|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1422149|text|好了，您刚才描述了utf8_general_ci排序规则是关于什么的(相比之下，a，a，à，-，ä，all等于a)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1422150|在utf8_general_ci和utf8_unicode_ci方面，MySQL+server+5.1也有一些变化，因此它也依赖于服务器版本。最好查查文件。|1422151|所以，如果是MySQL+server+5.0，我会选择utf8_unicode_ci而不是utf8_general_ci，这显然不适合你的用例。|1422152|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Well, you just described what utf8_general_ci collation is all about (a, á, à, â, ä, å all equals to a in comparison).

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.

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.

blocks|key|1422385|text|这对我在utf8_general_ci数据库中的MySql+Server5.1中不区分重音和不区分大小写的搜索是有效的，其中的列是一个LONGBLOB。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1422386|select+*+from+words+where+'%25word%25'+LIKE+column+collate+utf8_unicode_ci|code-block|syntax|javascript|1422387|使用|1422388|select+*+from+words+where'%25word%25'+LIKE+column+collate+utf8_general_ci|1422389|结果区分大小写，但不区分重音。|1422390|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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.

<pre><code>select * from words where '%word%' LIKE column collate utf8_unicode_ci
</code></pre>

with 

<pre><code>select * from words where'%word%' LIKE column collate utf8_general_ci
</code></pre>

the result is case sensitive but not accent sensitive.

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

abad 
abád

I am using a query that looks like this:

<pre><code>SELECT * FROM `words` WHERE `word` = 'abád'
</code></pre>

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 

abád 

I have also tried this query:

<pre><code>SELECT * FROM `words` WHERE BINARY `word` = 'abád'
</code></pre>

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

How to conduct an Accent Sensitive search in MySql

数据库

服务器

MySQL

我有一个包含utf8通用ci排序规则的MySQL表。在表格中，我可以看到两个条目：放弃阿巴德我使用的查询如下所示：SELECT *  FROM `words` WHERE `word` = 'abád'查询结果给出了这两个词：放弃阿巴德有没有办法表明我只想让MySQL找到带重音的单词？我希望查询只返回阿巴德我也尝试过这个查询：SELECT *  FROM `words` WHERE BINARY 

问如何在MySql中执行区分重音的搜索
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySql中执行区分重音的搜索EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySql中执行区分重音的搜索
EN