Monday, 21 July 2014

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation - Multilingual Website

In MySqL Database, we stored lots of data like city address, city description and city full description etc. Many times we stored different language's character like Chinese OR Japanese which take multibyte to single single character.
When we stored multibyte-character then it means we can also search the data from multibyte-character string.

So, Basically while we are working with multilingual character then we face two common type of problems.
  1. Multibyte Character not storing properly in Database.
  2. Search with multibyte character, MySql send error like "Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation"
Both are different issues,  but solution is Simple and same.

Problem 1: Character not storing properly: Collation must be "utf8_general_ci" to stored multibyte character properly in database.

Sample Table for store multi characters
  `name` varchar(256) CHARACTER SET utf8 DEFAULT NULL,
  `detail` text CHARACTER SET utf8,
  `created_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)

Problem 2: Search with multi character, Db is sending error: While Searching multicharacter, your database sending below error "Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation OR similar issues"

To get rid of this, you have to update the collation of your search fieldCollation must be utf8_general_ci for search multicharacter
ALTER TABLE `users1` CHANGE `name` `name` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_general_ci;

If you don't want to change the Collation of field we can also pass the utf8_general_ci explicity explicitly in search query.
select * from users WHERE name = "gdańsk" COLLATE utf8_unicode_ci

5 Best Related Posts are Following:1. Mysql Privileges - Types of privileages in MySql - How do I grant privileges in MySQL
2. Calculate a Sum of Time using MySQL
3. Mysql Interview Questions And Answers - Atomicity, Dedlock, Client Program, Mysql Mode, Mysql Sensitivity Of Identifiers And Mysql Errors
4. Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation - Multilingual Website
5. Difference Between MyISAM and innoDB - MySQL

1 comment:

  1. I am gettting this error "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation" with select statement.

    For example: select * from users where name ="СПБ";

    I just change the data type with following Query
    ALTER TABLE `users` CHANGE `name` `name` VARCHAR(750) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

    Now its working :)

    Thanks for sharing Nice article