Troubleshooting "Illegal mix of collations" error in mysql
collation, mysql
Solution
This is generally caused by comparing two strings of incompatible collation or by attempting to select data of different collation into a combined column.
The clause `COLLATE` allows you to specify the collation used in the query.
For example, the following `WHERE` clause will always give the error you posted:
WHERE 'A' COLLATE latin1_general_ci = 'A' COLLATE latin1_general_cs
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:
BINARY str is the shorthand for CAST(str AS BINARY).
Your solution might look something like this:
SELECT * FROM table WHERE BINARY a = BINARY b;
or,
SELECT * FROM table ORDER BY BINARY a;
Please keep in mind that, as pointed out by Jacob Stamm in the comments, "casting columns to compare them will cause any indexing on that column to be ignored".
For much greater detail about this collation business, I highly recommend eggyal's excellent answer to this same question.
Problem
Am getting the below error when trying to do a select through a stored procedure in MySQL. Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '=' Any idea on what might be going wrong here? The collation of the table is `latin1_general_ci` and that of the column in the where clause is `latin1_general_cs`.