MySQL: Illegal mix of collations Error
You might encounter the Illegal mix of collations error if your table-columns is using an incorrect collation.
By. Jacob
Edited: 2020-11-28 08:06
I recently encountered an error, possibly after updating, saying there was a "illegal mix of collations" in the MySQL tables I was working on. The error looked a bit like this:
... Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation ...
The error can happen when MySQL tries to compare two strings of different collations; the collation used for the database connection itself might also have something to do with this error. You can set the database character set and collation for the current connection using this query:
set names utf8mb4 collate utf8mb4_0900_ai_ci;
This query can also be used from PHP scripts.
Note. utf8mb4_0900_ai_ci is newer than utf8mb4_general_ci. If you instead need to do accent- and case sensitive comparison, use utf8mb4_0900_as_cs instead.
Changing the collation on tables
Make sure the default collation of the database is correct:
alter database database_name default collate utf8mb4_0900_ai_ci;
You should also make sure that the MySQL table-columns are using the correct collation. If you need to change the collation of a table and its columns, you can use the below query:
alter table table_name convert to character set utf8mb4 collate utf8mb4_0900_ai_ci;
Also keep in mind that MySQL will resolve some discrepancies by itself, but it is still a good idea to explicitly define the collation on your own.
Character sets and collations
Keep in mind, a character set is a set of symbols and encodings; utf8mb4 allows for characters up to 4 bytes in length, which is more than utf8. A collation has to do with how characters are compared when performing queries. You should carefully select a collation that suits the needs of your application, utf8mb4_0900_ai_ci is accent-insensitive and case-insensitive, so if you need to do precise comparisons you should probably use utf8mb4_0900_as_cs instead.
Accents has to do with characters such as: éúüó; for example, If using an accent-insensitive collation, "e" and "é" will be treated the same. And likewise with "e" and "E" when using a case insensitive collation.
Applications can benefit from using unicode for internationalization purposes; utf8mb4 provides broad support for different characters, emojis, and symbols, and is recommended over plain old utf8.
How to know the collation of tables and columns
Keep in mind that the collation used on the table may be different than the collation used on the individual columns.
The following query will return the collations for all of the columns found in a given database, useful for smaller databases:
select table_name, column_name, collation_name, character_set_name from information_schema.columns where table_schema = 'database_name';
To return the column-collations from a specific table:
select table_name, column_name, collation_name, character_set_name from information_schema.columns where table_name = 'table_name';
If you need the collation of the tables themselves:
select table_schema, table_name, table_collation from information_schema.tables where table_schema = 'database_name';
Alternatively, you can both view and modify character set and collation in phpMyAdmin.
Tell us what you think: