Illegal mix of collations MySQL Error

Question

I'm getting this strange error while processing a large number of data...

Error Number: 1267

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

SELECT COUNT(*) as num from keywords WHERE campaignId='12' AND LCASE(keyword)='hello again 昔 から ある å ´æ‰€'

What can I do to resolve this? Can I escape the string somehow so this error wouldn't occur, or do I need to change my table encoding somehow, and if so, what should I change it to?

Answer 1

SET collation_connection = 'utf8_general_ci'

then for your databases

ALTER DATABASE db CHARACTER SET utf8 COLLATE utf8_general_ci

ALTER TABLE table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

MySQL sneaks swedish in there sometimes for no sensible reason.

Answer 2

You should set both your table encoding and connection encoding to UTF-8:

ALTER TABLE keywords CHARACTER SET UTF8; -- run once

and

SET NAMES 'UTF8';
SET CHARACTER SET 'UTF8';