mysql:Collations Must Be for the Right Character Set

Each character set has one or more collations, but each collation is associated with one and only one character set. Therefore, the following statement causes an error message because the latin2_bin collation is not legal with the latin1 character set:

每个character set都有一个或多个collations，当每个collation仅仅与一个character set关联。因此下面的表达式会导致collation latin2_bin和charater set latin1不匹配的错误。

mysql> SELECT _latin1 'x' COLLATE latin2_bin;
ERROR 1253 (42000): COLLATION 'latin2_bin' is not valid
for CHARACTER SET 'latin1'

In some cases, expressions that worked before MySQL 4.1 fail in early versions of MySQL 4.1 if you do not take character set and collation into account. For example, before 4.1, this statement works as is:

在某些情况下，在MySQL 4.1之前的版本工作的很正常的表达式，如果你不考虑到chracter set和collation，在MySQL 4.1中会不再能成功运行。例如，4.1之前的版本，下面的式子是工作的：

mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);
+-------------------------------+
| SUBSTRING_INDEX(USER(),'@',1) |
+-------------------------------+
| root                          |
+-------------------------------+

The statement also works as is in MySQL 4.1 as of 4.1.8: In MySQL 4.1, user names are stored using the utf8character set (see Section 9.1.10, “UTF-8 for Metadata”). The literal string '@' has the server character set (latin1 by default). Although the character sets are different, MySQL can coerce the latin1 string to the character set (and collation) of USER() without data loss. It does so, performs the substring operation, and returns a result that has a character set of utf8.

这个表达式同样在 MySQL 4.1以及 4.1.8能工作，user name被通过utf8 的character set存储。字符‘@’拥有服务器字符集(默认是latin1).即使字符集不同，MySQ能强制将latin1字符转到和USER()相同的character set和collation，而不会发生数据丢失。事实上，就是先进行字符串处理，然后才返回给你一个utf8的结果。

However, in versions of MySQL 4.1 before 4.1.8, the statement fails:

然而，在MySQL 4.1.8之前的MySQL 4.1版本，这个表达式就会失败。

mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);
ERROR 1267 (HY000): Illegal mix of collations
(utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE)
for operation 'substr_index'

This happens because the automatic character set conversion of '@' does not occur and the string operands have different character sets (and thus different collations):

之所以会发生，是因为自动的'@'的character set的转换没有发生，于是字符的操作有不同的character set和collations。

mysql> SELECT COLLATION(USER()), COLLATION('@');
+-------------------+-------------------+
| COLLATION(USER()) | COLLATION('@')    |
+-------------------+-------------------+
| utf8_general_ci   | latin1_swedish_ci |
+-------------------+-------------------+

One way to deal with this is to upgrade to MySQL 4.1.8 or later. If that is not possible, you can tell MySQL to interpret the literal string as utf8:

一种方式是你将MySQL更新到4.1.8和之后的版本。如果这不太可行，可以显示的告诉MySQL将字符转换成utf8的格式：

mysql> SELECT SUBSTRING_INDEX(USER(),_utf8'@',1);
+------------------------------------+
| SUBSTRING_INDEX(USER(),_utf8'@',1) |
+------------------------------------+
| root                               |
+------------------------------------+

Another way is to change the connection character set and collation to utf8. You can do that with SET NAMES 'utf8' or by setting the character_set_connection and collation_connection system variables directly.

另一种方式是改变connection的character set和collation成utf8。你可以通过 SET NAMES 'utf8' 或者通过直接设置 character set connection和collation connection系统变量。