0

I am getting below error while searching hindi content from table

Database query failedIllegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'

This is my query

SELECT COUNT(art.id) as num FROM lehren_articles art INNER JOIN 
lehren_category_master cat ON art.category_id=cat.id INNER JOIN 
lehren_sub_category subcat ON art.subcategory_id=subcat.id INNER JOIN 
lehren_main_menu menu ON art.menu_id=menu.id INNER JOIN lehren_authors auth 
ON art.author_id=auth.id where art.id<>''  and ((cat.name LIKE '%क्या रीमा लागू  
%' OR art.title LIKE '%क्या रीमा लागू  %' OR art.id LIKE '%क्या रीमा लागू  %' OR 
art.created LIKE '%क्या रीमा लागू  %' OR menu.menu_name LIKE '%क्या रीमा लागू  %' 
OR subcat.name LIKE '%क्या रीमा लागू  %' OR auth.name LIKE '%क्या रीमा लागू  %' OR 
art.display_cube LIKE '%क्या रीमा लागू  %' OR art.language LIKE '%क्या रीमा लागू  
%' OR art.bucket LIKE '%क्या रीमा लागू  %' OR art.status LIKE '%क्या रीमा लागू  %' 
OR art.modified_date LIKE '%क्या रीमा लागू  %'))
The Hungry Dictator
  • 3,444
  • 5
  • 37
  • 53
Bhavik
  • 495
  • 2
  • 10

1 Answers1

0

You need to change your collation on your database and table. 

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 was developed by Swedish developers so thats why the default collation is set to swedish.

Have a look at this link too for more details -> https://airbladesoftware.com/notes/fixing-mysql-illegal-mix-of-collations/

Colwin
  • 2,655
  • 3
  • 25
  • 25