Troubleshooting “Illegal mix of collations” error in mysql

by

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;

More Related Contents:

  1. What’s the difference between utf8_general_ci and utf8_unicode_ci?
  2. UTF-8: General? Bin? Unicode?
  3. How to change the CHARACTER SET (and COLLATION) throughout a database?
  4. How to change collation of database, table, column?
  5. What effects does using a binary collation have?
  6. Are column and table name case sensitive in MySQL?
  7. Illegal mix of collations error in MySql
  8. What is the best MySQL collation for German language
  9. #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
  10. MySQL treats ÅÄÖ as AAO?
  11. How to change the default charset of a MySQL table?
  12. MySQL Illegal mix of collations
  13. Out of memory Microsoft VBScript runtime error 800a0007
  14. Left Outer Join doesn’t return all rows from my left table?
  15. Are table names in MySQL case sensitive?
  16. 1114 (HY000): The table is full
  17. When should I use a composite index?
  18. How can I restore the MySQL root user’s full privileges?
  19. setting global sql_mode in mysql
  20. Compare dates in MySQL
  21. MySQL root password change
  22. MySQL join query using like?
  23. MySql Query: Select top 3 rows from table for each category
  24. MySQL: How to select records for this week?
  25. MySQL Select Query – Get only first 10 characters of a value
  26. Create an index on a huge MySQL production table without table locking
  27. mysql select id and name from other table and join query
  28. Laravel MySql DB Connection with SSH
  29. Get column sum and use to calculate percent of total (mySQL)
  30. is there a length limit to group_concat or another reason why it would not work on a text field

Leave a Comment