MYSQL区分大小写搜索utf8_bin字段 [英] MYSQL case sensitive search for utf8_bin field
问题描述
我创建了一个表，并将排序规则设置为 utf8 ，以便能够向字段添加唯一索引。现在我需要做不区分大小写的搜索，但是当我用collate关键字执行一些查询，我得到：
I created a table and set the collation to utf8 in order to be able to add a unique index to a field. Now I need to do case insensitive searches, but when I performed some queries with the collate keyword and I got:
mysql> select * from page where pageTitle="Something" Collate utf8_general_ci;
错误1253（42000）：COLLATION'utf8_general_ci'
CHARACTER SET'latin1'
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
mysql> select * from page where pageTitle="Something" Collate latin1_general_ci;
错误1267（HY000）：非法混合的排序规则（utf8_bin，IMPLICIT ）和
（latin1_general_ci，EXPLICIT）for operation'='
ERROR 1267 (HY000): Illegal mix of collations (utf8_bin,IMPLICIT) and (latin1_general_ci,EXPLICIT) for operation '='
我对SQL很陌生，如果有人可以帮助。
I am pretty new to SQL, so I was wondering if anyone could help.
推荐答案
MySQL中的字符串有字符集和归类。 Utf8是字符集，utf8_bin是其中的一个排序规则。要比较你的字符串字符串和utf8列，将其转换为utf8前缀的_charset符号：
A string in MySQL has a character set and a collation. Utf8 is the character set, and utf8_bin is one of its collations. To compare your string literal to an utf8 column, convert it to utf8 by prefixing it with the _charset notation:
_utf8 'Something'
现在排序规则仅对某些字符集有效。 utf8的大小写敏感整理似乎是utf8_bin，您可以指定为：
Now a collation is only valid for some character sets. The case-sensitive collation for utf8 appears to be utf8_bin, which you can specify like:
_utf8 'Something' collate utf8_bin
使用这些转换，查询应该可以工作：
With these conversions, the query should work:
select * from page where pageTitle = _utf8 'Something' collate utf8_bin
_charset前缀与字符串文字一起使用。要更改字段的字符集，请使用CONVERT ... USING。当您想将pageTitle字段转换为另一个字符集时，这是非常有用的，如：
The _charset prefix works with string literals. To change the character set of a field, there is CONVERT ... USING. This is useful when you'd like to convert the pageTitle field to another character set, as in:
select * from page
where convert(pageTitle using latin1) collate latin1_general_cs = 'Something'
要查看字符在名为'TAB'的表中名为'col'的列的排序规则，请尝试：
To see the character and collation for a column named 'col' in a table called 'TAB', try:
select distinct collation(col), charset(col) from TAB
所有字符集和排序规则的列表可以使用：
A list of all character sets and collations can be found with:
show character set
show collation
utf8的所有有效归类可以找到：
And all valid collations for utf8 can be found with:
show collation where charset = 'utf8'
这篇关于MYSQL区分大小写搜索utf8_bin字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
