MySQL Illegal mix of collations

A character encoding details how each symbol is represented in binary (and therefore stored in the computer). For example, the symbol é (U+00E9, latin small letter E with acute) is encoded as 0xc3a9 in UTF-8 (which MySQL calls utf8) and 0xe9 in Windows-1252 (which MySQL calls latin1).

A character set is the alphabet of symbols that can be represented using a given character encoding. Confusingly, the term is also used to mean the same as character encoding.

A collation is an ordering on a character set, so that strings can be compared. For example: MySQL’s latin1_swedish_ci collation treats most accented variations of a character as equivalent to the base character, whereas its latin1_general_ci collation will order them before the next base character but not equivalent (there are other, more significant, differences too: such as the order of characters like å, ä, ö and ß).

MySQL will decide which collation should be applied to a given expression as documented under Collation of Expressions: in particular, the collation of a column takes precedence over that of a string literal.

Example Query :

select * from my_table where some_field='asd兵qwe'

The WHERE clause of your query compares the following strings:

a value in `some_field` encoded in the column’s character set (Windows-1252) and expressing a preference for its collation latin1_swedish_ci (with a coercibility value of 2);

the string literal ‘asd兵qwe’, encoded in the connection’s character set (UTF-8, as configured by Doctrine) and expressing a preference for the connection’s collation utf8_general_ci (with a coercibility value of 4).

Since the first of these strings has a lower coercibility value than the second, MySQL attempts to perform the comparison using that string’s collation: latin1_swedish_ci. To do so, MySQL attempts to convert the second string to latin1—but since the `兵` character does not exist in that character set, the comparison fails.

solution is very simple. You need to change the table and field CHARACTER SET.

ALTER TABLE `my_table` ENGINE=MYISAM ROW_FORMAT=DEFAULT CHARSET=utf8 ;
ALTER TABLE `my_table` CHANGE `some_field` `some_field` VARCHAR(40) CHARACTER SET utf8 COLLATE utf8_general_ci;

we have simply changed the character set from latin to utf which will help saving all kind of data.

i hope that will help. Let know if you face any difficulty.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.