MYSQL区分大小写搜索utf8_bin字段
我创建了一个表并将排序规则设置为 utf8，以便能够向字段添加唯一索引。 现在我需要进行不区分大小写的搜索，但是当我使用 collate 关键字执行一些查询时，我得到：
mysql> select * from page where pageTitle="Something" Collate utf8_general_ci;
错误 1253 (42000)：排序规则“utf8_general_ci”对于以下内容无效 字符集“latin1”
mysql> select * from page where pageTitle="Something" Collate latin1_general_ci;
错误 1267 (HY000)：非法混合排序规则（utf8_bin、IMPLICIT）和 (latin1_general_ci,EXPLICIT) 用于操作“=”
我对 SQL 很陌生，所以我想知道是否有人可以提供帮助。
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;
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;
ERROR 1267 (HY000): Illegal mix of collations (utf8_bin,IMPLICIT) and
(latin1_general_ci,EXPLICIT) for operation '='
I am pretty new to SQL, so I was wondering if anyone could help.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
MySQL 中的字符串具有字符集和排序规则。 utf8 是字符集，utf8_bin 是其排序规则之一。 要将字符串文字与 utf8 列进行比较，请通过在其前面加上 _charset 表示法将其转换为 utf8：
现在排序规则仅对某些字符集有效。 utf8 区分大小写的排序规则似乎是 utf8_bin，您可以像这样指定它：
通过这些转换，查询应该可以工作：
_charset 前缀适用于字符串文字。 要更改字段的字符集，可以使用 CONVERT ... USING。 当您想要将 pageTitle 字段转换为另一个字符集时，这非常有用，例如：
要查看名为“TAB”的表中名为“col”的列的字符和排序规则，请尝试：
所有字符集的列表可以通过以下方式找到排序规则：
可以通过以下方式找到 utf8 的所有有效排序规则：
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:
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:
With these conversions, the query should work:
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:
To see the character and collation for a column named 'col' in a table called 'TAB', try:
A list of all character sets and collations can be found with:
And all valid collations for utf8 can be found with:
另请注意，如果使用“Collate utf8_general_ci”或“Collate latin1_general_ci”，即“强制”整理 - 这样的转换将阻止使用现有索引！ 这可能会成为未来性能的瓶颈。
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.
试试这个，它对我有用
SELECT * FROM
usersWHERE UPPER(name) = UPPER('josé') COLLATE utf8_bin;Try this, Its working for me
SELECT * FROM
usersWHERE UPPER(name) = UPPER('josé') COLLATE utf8_bin;请问为什么在执行 SELECT 时需要显式更改排序规则？ 为什么不按照您想要在排序时检索记录的方式进行整理呢？
您的搜索区分大小写时遇到的问题是您有二进制排序规则。 尝试使用通用排序规则。 有关区分大小写和排序规则的更多信息，请查看此处：
字符串搜索中的区分大小写
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?
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