How to solve - Illegal mix of collations in mysql?

Question

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

Thanks!

Answer 1

MySQL really dislikes mixing collations unless it can coerce them to the same one (which clearly is not feasible in your case). Can't you just force the same collation to be used via a COLLATE clause? (or the simpler BINARY shortcut if applicable...).

Answer 2

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 are some example uses of the COLLATE clause:

SELECT * COLLATE latin1_general_ci FROM table ORDER BY key;

SELECT * FROM table ORDER BY key COLLATE latin1_general_ci;

Another option is to use the BINARY operator which is simply a shorthand version of COLLATE. Your solution might look something like this:

SELECT * FROM table WHERE BINARY a = BINARY b;

Or,

SELECT * FROM table ORDER BY BINARY a;