Troubleshooting “Illegal mix of collations” error in mysql

If you're seeing the "Illegal mix of collations" error in MySQL, it's likely due to comparing two strings of incompatible collation or attempting to select data of different collation into a combined column. In this article, we'll explore some solutions to this error, including changing the database, table, and column collation, using the COLLATE clause, and the BINARY operator. Follow these steps to fix the "Illegal mix of collations" error in MySQL and get your database back up and running.

As you can see in the below image this error well known when mix collection used in the database

This is generally caused by comparing two strings of incompatible collation or by attempting to select data of different collation into a combined column.

There are ways to resolve this things,

once of them to set all data in one collection

change database collation:

ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_unicode_ci;

change table collation:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

change column collation:

ALTER TABLE `table_name`
MODIFY column_name VARCHAR(255) CHARACTER 
SET utf8 COLLATE utf8_unicode_ci;

Another solution is, to use clause COLLATE allows you to specify the collation used in the query.

Your solution is to specify a shared collation for the two columns within the query. Here is an example that uses the COLLATE clause:

SELECT * FROM table ORDER BY key COLLATE latin1_general_ci;

Another option is to use the BINARY operator:

Your solution might look something like this:

SELECT * FROM `table_name` WHERE BINARY a = BINARY b;

or,

SELECT * FROM `table_name` ORDER BY BINARY a;