MYSQL 区分大小写搜索 utf8_bin 字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/901066/
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
MYSQL case sensitive search for utf8_bin field
提问by Andomar
I created a table and set the collation to utf8in 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:
我创建了一个表并将排序规则设置为utf8，以便能够向字段添加唯一索引。现在我需要进行不区分大小写的搜索，但是当我使用 collate 关键字执行一些查询时，我得到：
mysql> select * from page where pageTitle="Something" Collate utf8_general_ci;
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
错误 1253 (42000)：COLLATION 'utf8_general_ci' 对 CHARACTER SET 'latin1' 无效
mysql> select * from page where pageTitle="Something" Collate latin1_general_ci;
ERROR 1267 (HY000): Illegal mix of collations (utf8_bin,IMPLICIT) and (latin1_general_ci,EXPLICIT) for operation '='
错误 1267 (HY000): 操作 '=' 的排序规则 (utf8_bin,IMPLICIT) 和 (latin1_general_ci,EXPLICIT) 的非法混合
I am pretty new to SQL, so I was wondering if anyone could help.
我对 SQL 很陌生，所以我想知道是否有人可以提供帮助。
回答by Andomar
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:
MySQL 中的字符串具有字符集和排序规则。utf8 是字符集，utf8_bin 是其排序规则之一。要将您的字符串文字与 utf8 列进行比较，请通过在其前面加上 _charset 符号将其转换为 utf8：
_utf8 'Something'
Now a collation is only valid for some character sets. The case-sensitivecollation for utf8 appears to be utf8_bin, which you can specify like:
现在排序规则仅对某些字符集有效。该区分大小写的UTF8归类似乎是utf8_bin，您可以指定，如：
_utf8 'Something' collate utf8_bin
With these conversions, the query should work:
通过这些转换，查询应该可以工作：
select * from page where pageTitle = _utf8 'Something' collate utf8_bin
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:
_charset 前缀适用于字符串文字。要更改字段的字符集，可以使用 CONVERT ... USING。当您想将 pageTitle 字段转换为另一个字符集时，这很有用，例如：
select * from page
where convert(pageTitle using latin1) collate latin1_general_cs = 'Something'
To see the character and collation for a column named 'col' in a table called 'TAB', try:
要查看名为“TAB”的表中名为“col”的列的字符和排序规则，请尝试：
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
And all valid collations for utf8 can be found with:
可以通过以下方式找到 utf8 的所有有效排序规则：
show collation where charset = 'utf8'
回答by Maksim
Also please note that in case of using "Collate utf8_general_ci" or "Collate latin1_general_ci", i.e. "force" collate - such a converting will prevent from usage of existing indexes! This could be a bottleneck in future for performance.
另请注意，如果使用“整理 utf8_general_ci”或“整理 latin1_general_ci”，即“强制”整理 - 这种转换将阻止使用现有索引！这可能是未来性能的瓶颈。
回答by user3041121
Try this, Its working for me
试试这个，它对我有用
SELECT * FROM usersWHERE UPPER(name) = UPPER('josé') COLLATE utf8_bin;
SELECT * FROM usersWHERE UPPER( name) = UPPER('josé') COLLATE utf8_bin;
回答by PatrikAkerstrand
May I ask why you have a need to explicitly change the collation when you do a SELECT? Why not just collate in the way you want to retrieve the records when sorted?
请问为什么在执行 SELECT 时需要显式更改排序规则？为什么不按照您希望在排序时检索记录的方式进行整理？
The problem you are having with your searches being case sensitive is that you have a binary collation. Try instead to use the general collation. For more information about case sensitivity and collations, look here: Case Sensitivity in String Searches
您在搜索区分大小写时遇到的问题是您有一个二进制排序规则。尝试改用通用排序规则。有关区分大小写和排序规则的更多信息，请查看此处：字符串搜索中的区分大小写
