Illegal mix of collations - how to solve it?

With some queries in MySQL one might encounter an 'illegal mix of collations' error similar to this:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' 


It occurs when joining, comparing, concatenating strings of different (incompatible) collations (assembly of information in a proper logical/numerical order).

The 'utf8_general_ci' and the 'utf8_unicode_ci' collations above can differ from case to case, it just happened so in my specific case.

In my case, I was trying to join two tables on those columns with different collations, e.g. (query 1):

SELECT * FROM a LEFT JOIN b ON (a.email=b.email);


Where ,
Table a:
`email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

And table b:
`email` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL

The solution is forcing the same / compatible collations on the two columns, query 1 becomes:

SELECT * FROM a LEFT JOIN b ON (a.email COLLATE utf8_unicode_ci =b.email);


There are other solutions, where possible, like changing the collation of the columns / tables by design. e.g.:

ALTER TABLE a CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

or:

ALTER TABLE a MODIFY COLUMN email varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;


Good luck!