How To Solve MySQL Character Sets and Collations Mixing Problems

Character sets and collations enable MySQL to perform comparisons on strings, so if have two or more columns with different character sets and/or collations are used in a way that performs some comparison of some sort on them (either using =, , [Err] 1267 – Illegal mix of collations (latin1_danish_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '=' Unfortunately, there is no single command at the database level that could convert the character sets and collations of all tables within a database. In fact, the only command which appears to get the job done only changes the default values of the character set and/or collation of a database, and does not affect existing tables, but only has effect on new tables to be created. ALTER DATABASE {database_name} CHARACTER SET {character_set} COLLATE {collation}; Likely for us, there is just a command/syntax for changing these values at the table level, syntax shown below. ALTER TABLE {table_name} CONVERT TO CHARACTER SET {character_set} COLLATE {collation}; Example: ALTER TABLE employees CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; This problem can be resolved by ensuring that all the tables and their corresponding columns have the same character set and collation.

✔ Read More...

I guess you came to this post by searching similar kind of issues in any of the search engine and hope that this resolved your problem. If you find this tips useful, just drop a line below and share the link to others and who knows they might find it useful too.

Stay tuned to my blog, twitter or facebook to read more articles, tutorials, news, tips & tricks on various technology fields. Also Subscribe to our Newsletter with your Email ID to keep you updated on latest posts. We will send newsletter to your registered email address. We will not share your email address to anybody as we respect privacy.

This article is related to

MySQL,illegal mix of collations,mysql