Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
I have two databases in mysql, I have written a sql to compare two table (dbA.contact & dbB.actor).
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?
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.
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.