MySQL Error: Illegal mix of collations

Say you wanted to do a simple join such as:

1
SELECT count(1) FROM table_a a LEFT OUTER JOIN table_b b ON (a.varchar_column = b.varchar_column) WHERE b.varchar_column IS NULL;

And you got:

1
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

All you need to do is update your table’s charset AND collation:

1
ALTER TABLE table_b CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

PS – table_b or table_a, whichever you decide that should match the other’s charset and collation.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>