In case, you have executed some request as following example
UPDATE `td_invoice_zzz` zi, ts_zone zo SET `zzz_com_zon_id` = zo.zon_id WHERE zzz_com_zon_code = zo.zon_code;
And you receive the error about illegal mix of collations as:
Error Code: 1267. Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
You are trying to compare two fields that have different collation in MySQL.
In above case, field on left of the where clause (zzz_com_zon_code) is utf8_general_ci and the right (zo.zon_code) is utf8_unicode_ci.
I already raised this kind of issue in previous post with explanation and solution by alter the field collation, here I just re-raise and provide another solution directly on the request as showing as following:
UPDATE `td_invoice_zzz` zi, ts_zone zo SET `zzz_com_zon_id` = zo.zon_id WHERE zzz_com_zon_code COLLATE utf8_unicode_ci = zo.zon_code;
This might helps people who doesn’t want to alter their collation on the field but if you want to change it, just read my post here.