MySQL – Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation ‘UNION’


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)

Best Solution

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