What Should I Do If the "illegal mix of collation" Error Is Reported for a View or Query?

This error often occurs when comparing character-type columns like VARCHAR or comparing such columns with string literals (strings directly input in SQL). The error occurs due to inconsistent collations of the strings on each side of the comparison operator.

Example:

SELECT ... WHERE (Column_Name = convert(date_format(curdate(),'%Y%m%d') using utf8mb4) ...FROM Table_Name;

SELECT ... WHERE (Column_Name='AAAAA') ...FROM Table_Name;

You can use the built-in COLLATION() function of MySQL to check whether the expressions on the left and right of the comparison operator are the same.

• Use the COLLATION() function to check the collation of a character-type column in a table.

  SELECT COLLATION(column_name) FROM Table_Name LIMIT 1;

• Use the COLLATION() function to check the collation of a constant or expression.

  SELECT COLLATION('AAA');

  SELECT COLLATION(convert(date_format(curdate(),'%Y%m%d') using utf8mb4));

If the collations of the strings on the left and right of the comparison operator are inconsistent, use the CONVERT function to explicitly specify the collation of either side. If you directly convert columns in a table, indexes may become invalid. So, you are advised to modify the constant.

If

SELECT COLLATION(column_name) FROM Table_Name LIMIT 1;

utf8mb4_general_ci is displayed.

SELECT COLLATION('AAAAA');

utf8mb4_0900_ci is displayed.

Change the constant string to convert('AAAAA'using utf8mb4 )collate utf8mb4_general_ci.

The SQL statement is changed to:

SELECT ... WHERE (column_name = convert('AAAAA' using utf8mb4 )collate utf8mb4_general_ci) ...FROM Table_Name;