MYSQL 区分大小写搜索 utf8_bin 字段 [英] MYSQL case sensitive search for utf8_bin field

问题描述

我创建了一个表并将排序规则设置为 utf8 以便能够向字段添加唯一索引.现在我需要进行不区分大小写的搜索，但是当我使用 collat​​e 关键字执行一些查询时，我得到了:

mysql>select * from page where pageTitle="Something" Collat​​e utf8_general_ci;

<块引用>

ERROR 1253 (42000): COLLATION 'utf8_general_ci' 不适用于字符集 'latin1'

mysql>select * from page where pageTitle="Something" Collat​​e latin1_general_ci;

<块引用>

错误 1267 (HY000):排序规则 (utf8_bin,IMPLICIT) 和(latin1_general_ci,EXPLICIT) 用于操作 '='

我对 SQL 很陌生，所以我想知道是否有人可以提供帮助.

解决方案

MySQL 中的字符串有一个 字符集和排序规则.utf8 是字符集，utf8_bin 是其排序规则之一.要将您的字符串文字与 utf8 列进行比较，请通过在其前面加上 _charset 符号将其转换为 utf8:

_utf8 '东西'

现在排序规则仅对某些字符集有效.utf8 的区分大小写的 排序规则似乎是 utf8_bin，您可以像这样指定:

_utf8 'Something' 整理 utf8_bin

通过这些转换，查询应该可以工作:

select * from page where pageTitle = _utf8 'Something' collat​​e utf8_bin

_charset 前缀适用于字符串文字.要更改字段的字符集，可以使用 CONVERT ... USING.当您想将 pageTitle 字段转换为另一个字符集时，这很有用，例如:

select * from page其中 convert(pageTitle using latin1) collat​​e latin1_general_cs = 'Something'

要查看名为TAB"的表中名为col"的列的字符和排序规则，请尝试:

从TAB中选择不同的排序规则(col)，字符集(col)

可以通过以下方式找到所有字符集和排序规则的列表:

显示字符集显示整理

所有 utf8 的有效排序规则都可以通过以下方式找到:

show collat​​ion where charset = 'utf8'

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.

解决方案

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'

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

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'

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

And all valid collations for utf8 can be found with:

show collation where charset = 'utf8'

这篇关于MYSQL 区分大小写搜索 utf8_bin 字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！