In MySQL, you might run into an error similar to this:

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' 

This is generally caused by comparing two strings of incompatible collations or by attempting to select data of different collations into a combined column. In this case, they are utf8_unicode_ci and utf8_general_ci.

So the job now is to make the two columns collation match.

Here are some fixes:

1. Change the collation of one column (or string) to match the other collation.

Find the columns with inappropriate collation:


So the chances are, you can find the column with a different collation or it hasn’t been specified at all.

You can change the collation on each column:

ALTER TABLE table_name CHANGE col_name data_type CHARACTER SET charset_name COLLATE collation_name;

If you want to make a collation change table-wide:

ALTER TABLE table_name CONVERT TO CHARACTER SET charset_name COLLATE collation_name;

2. Add a COLLATE clause to specify the collation used in your query.

SELECT * FROM table _name ORDER BY col_name COLLATE collation_name;

Need a good MySQL GUI? TablePlus provides a native client that allows you to access and manage MySQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS

TablePlus in Dark mode