Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT)

Question

Here is my query:

INSERT INTO location_province(name, country)   
SELECT child.name
      ,location_country.id
  FROM location_1 child
 INNER JOIN location_1 parent
    ON child.parent_id = parent.id
 INNER JOIN location_country
    ON location_country.name = parent.name
 WHERE child.location_type = 1

It throws this error:

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

What's wrong and how can I fix it?

---

Note: Adding COLLATE utf8_unicode_ci in the end of query doesn't work either.

Answer 1

Yeah, that's because of the JOIN ON clauses and per error, collation of those columns involved in ON condition doesn't matches. Collation of those column must match. I mean the below lines

ON child.parent_id = parent.id  ------ 1
 INNER JOIN location_country
    ON location_country.name = parent.name ------ 2

Check the tables on which you are joining and verify the same

Well, change the collation while joining like

 INNER JOIN location_country
    ON location_country.name collate utf8_general_ci = parent.name collate utf8_general_ci 

Answer 2

"Illegal mix of collation ... for operator =" exception is caused by textual columns ( ex. VARCHAR type) in the where clause. To demonstrate the issue try creating two identical tables with different collation then join them:

create table t1_gen (label varchar(10) collate utf8_general_ci);
insert into t1_gen values ('foobar');

create table t2_uni (label varchar(10) collate utf8_unicode_ci);
insert into t2_uni values ('foobar');

and here is the join that will cause the exact same exception. Collation of the two columns is indeed mismatched:

select * from t1_gen, t2_uni where t1_gen.label = t2_uni.label;

the exception will change if you change the order of the fields in the where clause.

select * from t1_gen, t2_uni where t2_uni.label = t1_gen.label;

to make this query work we are explicitly adding the collation to the mismatched column in the where clause:

select * from t1_gen, t2_uni where t1_gen.label collate utf8_unicode_ci = t2_uni.label;

cheers,