(mysql.info.gz) Charset-collation-charset

Info Catalog

(mysql.info.gz) Charset-collate-tricky

(mysql.info.gz) Charset-defaults

(mysql.info.gz) Charset-collation-effect

 
 10.3.12 Collations Must Be for the Right Character Set
 ------------------------------------------------------
 
 Recall that each character set has one or more collations, and 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:
 
      mysql> SELECT _latin1 'x' COLLATE latin2_bin;
      ERROR 1251: COLLATION 'latin2_bin' is not valid
      for CHARACTER SET 'latin1'
 
 In some cases, expressions that worked before MySQL 4.1 fail as 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> SELECT SUBSTRING_INDEX(USER(),'@',1);
      +-------------------------------+
      | SUBSTRING_INDEX(USER(),'@',1) |
      +-------------------------------+
      | root                          |
      +-------------------------------+
 
 After an upgrade to MySQL 4.1, the statement fails:
 
      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'
 
 The reason this occurs is that usernames are stored using UTF8 (
 Charset-metadata). As a result, the `USER()' function and the literal
 string `'@'' have different character sets (and thus different
 collations):
 
      mysql> SELECT COLLATION(USER()), COLLATION('@');
      +-------------------+-------------------+
      | COLLATION(USER()) | COLLATION('@')    |
      +-------------------+-------------------+
      | utf8_general_ci   | latin1_swedish_ci |
      +-------------------+-------------------+
 
 One way to deal with this is to tell MySQL to interpret the literal
 string as `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.
 

Info Catalog

(mysql.info.gz) Charset-collate-tricky

(mysql.info.gz) Charset-defaults

(mysql.info.gz) Charset-collation-effect