MySQL issue & Solution: Illegal mix of collations for operation ‘=’

MySQL Error

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

Overview

I have two databases in mysql, I have written a sql to compare two table (dbA.contact & dbB.actor).

Example:

SELECT dbB.*
FROM dbA.contact co, dbB.actor ac
WHERE co.name = ac.name

When I execute the request, I will get above error, why it errors?

Reasons

According to the message, it means that:

• Table on the left (dbA.contact, field co.name) using charset collation utf8_general_ci
• Table on the right (dbB.actor, field ac.name) using charset collation utf8_unicode_ci

So in order to compare these two tables from different database, we need to make them to have the same charset and collations.

Solutions

You have to decide which table to follow the charset and collation?

In my case, I can only modify at dbA.contact so here I did:

alter table `dbA`.`contact` convert to character set utf8 collate utf8_unicode_ci;

This has solved my headache!

If you have more to add, please share your experience in comment.