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

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.

Multibyte Character not storing properly in Database.
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

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) CHARACTER SET utf8 DEFAULT NULL,
  `detail` text CHARACTER SET utf8,
  `created_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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 field. Collation 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