Illegal mix of collations error in MySQL - utf8_unicode_ci and utf8_general_ci
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:
SHOW CREATE TABLE table_name;
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.
Not on Mac? Download TablePlus for Windows.
Need a quick edit on the go? Download for iOS
