Illegal mix of collations MySQL Error

user_49153 0
Viewed 219958

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?

mysql-error-1267 mysql sql
10 Answers
SET collation_connection = 'utf8_general_ci';

then for your databases

ALTER DATABASE your_database_name CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

MySQL sneaks swedish in there sometimes for no sensible reason.

user_1244140 
CONVERT(column1 USING utf8)

Solves my problem. Where column1 is the column which gives me this error.

user_46536310

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';
user_551590

Use following statement for error

be careful about your data take backup if data have in table.

 ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
user_31545970

I had my table originally created with CHARSET=latin1. After table conversion to utf8 some columns were not converted, however that was not really obvious. You can try to run SHOW CREATE TABLE my_table; and see which column was not converted or just fix incorrect character set on problematic column with query below (change varchar length and CHARSET and COLLATE according to your needs):

 ALTER TABLE `my_table` CHANGE `my_column` `my_column` VARCHAR(10) CHARSET utf8 
 COLLATE utf8_general_ci NULL;
user_17365370

I found that using cast() was the best solution for me:

cast(Format(amount, "Standard") AS CHAR CHARACTER SET utf8) AS Amount

There is also a convert() function. More details on it here

Another resource here

user_81476800

Change the character set of the table to utf8

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8

user_80610220

My user account did not have the permissions to alter the database and table, as suggested in this solution.

If, like me, you don't care about the character collation (you are using the '=' operator), you can apply the reverse fix. Run this before your SELECT:

SET collation_connection = 'latin1_swedish_ci';
user_22230270
Related