Menu
How do I fix that error once and for all? I just want to be able to do unions in MySQL.
(I'm looking for a shortcut, like an option to make MySQL ignore that issue or take it's best guess, not looking to change collations on 100s of tables ... at least not today)
321
So what is an "illegal mix of collations"? An "illegal mix of collations" occurs when an expression compares two strings of different collations but of equal coercibility and the coercibility rules cannot help to resolve the conflict.
A collation is a set of rules that defines how to compare and sort character strings. Each collation in MySQL belongs to a single character set. Every character set has at least one collation, and most have two or more collations. A collation orders characters based on weights.
A character set in MySQL is a set of characters, encodings, and symbols that are legal in a string. This article explains how we can get all character sets in MySQL, how we can configure proper character sets for client connections, and how we can convert strings between multiple character sets.
Not sure about mySQL but in MSSQL you can change the collation in the query so for example if you have 2 tables with different collation and you want to join them or as in you situation crate UNION you can do
select column1 from tableWithProperCollation
union all
select column1 COLLATE SQL_Latin1_General_CP1_CI_AS from tableWithDifferentCollation
Of course SQL_Latin1_General_CP1_CI_AS is just an example of collation you want to "convert" to
195
Thanks Kristof. In this case it was being caused by selecting a literal in the first select, and not from any different table collations.
Ironically I got it working by following this old blog post I made for that issue.
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
