MySQL illegal mix of collationsy exception causes and workarounds

Original December 25, 2008 11:54:00

• Label:
• MySQL/
• Collation/
• Character/
• Variables/
• Database/
• Server

This exception was found today when using a cursor for a database temp table.
After finding the information, the final result. Here to share with you. Character set problem or must unify is the simplest.

Create temporary table TEMP2 (MC1 varchar) default ', MC2 varchar default ', MC3 varchar (default '), MC4 varchar (2 0) Default ', MC5 varchar (default ", ParentID varchar), levelnum int); --Generate temporary tables

Declare mycur67 CURSOR for select concat (replace (Space (levelnum-1), Space (1), '------'), MC1,MC2,MC3,MC4,MC5) as MC, ParentID as location from Temp2;

The exception information is:
Illegal mix of collations for operation ' Concat '

Remove the Concat code from the following section, and run the result as
Declare mycur67 CURSOR for select concat (replace (Space (levelnum-1), Space (1), '------'), MC1) as mc,parentid as location From Temp2;

Error: Illegal mix of collations (utf8_general_ci,coercible) and (gb2312_chinese_ci,implicit) for operation ' Concat '

visible, is caused by inconsistencies in the encoding type of the 2 fields.

When my database was created, the gb2312 encoding was selected, and the field Operation defaults to UTF8 encoding. Absolute uniform use of UTF-8

Create temporary table TEMP2 (MC1 varchar) default ', MC2 varchar default ', MC3 varchar (default '), MC4 varchar (2 0) Default ", MC5 varchar (default), ParentID varchar (+), Levelnum int) default Charset=utf8;

The running result is normal.

after finding the information, the following is very clear.
Mysql> Show variables like "%character%"; Show variables like "%collation%"; +--------------------------+----------------------------+
| variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | Latin1 |
| character_set_connection | Latin1 |
| Character_set_database | Latin1 |
| Character_set_results | Latin1 |
| Character_set_server | Latin1 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/share/mysql/charsets/|
+--------------------------+----------------------------+
7 Rows in Set (0.00 sec)

+----------------------+-------------------+
| variable_name | Value |
+----------------------+-------------------+
| collation_connection | Latin1_swedish_ci |
| Collation_database | Latin1_swedish_ci |
| Collation_server | Latin1_swedish_ci |
+----------------------+-------------------+
3 Rows in Set (0.00 sec)

Fix It with
Set collation_database=utf8_general_ci;
Set collation_connection=utf8_general_ci;
Set collation_server=utf8_general_ci;

MySQL illegal mix of collationsy exception causes and workarounds