This article has participated in the good article call order activity, click to see: back end, big front end double track submission, 20,000 yuan prize pool for you to challenge!

Question

When executing a select statement through a stored procedure in MySQL, we encountered the following error:

Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation ‘=’

What’s going wrong here?

The collation rule for tables is latin1_General_CI, and the collation rule for columns in where statements is latin1_general_cs.

Best Answer

This is usually caused by comparing two strings with different collations or by trying to query data with different collations into a combined column.

The COLLATE command allows you to specify collation rules in a query.

For example: the following WHERE command will always throw the error you encounter.

WHERE 'A' COLLATE latin1_general_ci = 'A' COLLATE latin1_general_cs
Copy the code

The solution is to specify the same collation for both columns of data in your query. Here is an example of using COLLATE:

SELECT * FROM table ORDER BY key COLLATE latin1_general_ci;
Copy the code

Another option is to use the BINARY command:

BINARY str is the shorthand for CAST(str AS BINARY).

In your question, it is used as follows:

SELECT * FROM table WHERE BINARY a = BINARY b;
Copy the code

Or:

SELECT * FROM table ORDER BY BINARY a;
Copy the code

The original link

Stackoverflow.com/questions/3…