How to Resolve \'Illegal Mix of Collations\' Error in MySQL Queries?

Illegal Character Sorting Resolution in MySql with Collation Error

In attempting to combine two subqueries in an SQL query, you may encounter the "Illegal mix of collations" error. This arises when the columns involved in comparisons or operations have different character set or collation settings.

To resolve this error, it's necessary to ensure that the columns used in the query have the same collation. To determine the columns affected, consult the following query:

SELECT table_schema, table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE collation_name = 'latin1_general_ci'
ORDER BY table_schema, table_name,ordinal_position;

This will display the columns with the 'latin1_general_ci' collation. To resolve the error, convert the conflicting columns to the 'latin1_swedish_ci' collation using this query:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET latin1 COLLATE 'latin1_swedish_ci';

By rectifying the collation mismatch, the query can be executed successfully:

SELECT username, (SUM(rating)/COUNT(*)) as TheAverage, Count(*) as TheCount
FROM ratings WHERE month='Aug' 
AND username IN (SELECT username FROM users WHERE gender =1)
GROUP BY username HAVING TheCount > 4
ORDER BY TheAverage DESC, TheCount DESC

The above is the detailed content of How to Resolve \'Illegal Mix of Collations\' Error in MySQL Queries?. For more information, please follow other related articles on the PHP Chinese website!