Sometimes, when designing queries, we assume that the database and tables inherit the same general settings. Due to this assumption, we run into many different MYSQL errors.
In this post, I will give you some tips to resolve this specific MYSQL error.
Basically, what it means is that you're trying to compare 2 fields with an incompatible collation.
For example, if you want to run this query:
SELECT* FROM table_1 WHERE A in (SELECT B FROM table_2)But keep in mind that column A has utf8_unicode_ci as default collation, and column B has latin1_general_ci as default collation. So, if you run that query, you will get this error:
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT)
How can I make it work?
Well, it's very simple. You can use COLLATE to convert a field to the desired collation.
Example:
SELECT* FROM table_1 WHERE A in (SELECT B COLLATE utf8_unicode_ci FROM table_2)
Or
SELECT* FROM table_1 WHERE A COLLATE latin1_general_ci in (SELECT B FROM table_2)Both queries will work fine now.
I hope you find it useful. Until the next post!!!