How to resolve the error - illegal mix of collations?

Question

I'm getting this strange error “illegal mix of collations” 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?

Ajit Yadav · Accepted Answer

To resolve the error - illegal mix of collations - Set the collations MySQL like this: SET collation_connection = 'utf8_general_ci'; For your databases, you can use the below code: 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; Using this code you will not get this error.

How to resolve the error - illegal mix of collations?

About this question

Your answer

1 Answer

More SQL Server discussions

Learn & Explore

SQL Server Tutorials

SQL Server Interview Q&A

Latest SQL Server Blogs

Want answers to more SQL Server questions?

Stay in the loop — it’s free

You’re on the list!