Troubleshooting "Illegal mix of collations" error in mysql
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.
Answer
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;