[MySQL] Illegal mix of collations – Solved in direct request

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.

You can find other posts on MySQL matters or any of database issue.