MySQL - Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'UNION'

Question

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)

Answer 1

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

Answer 2

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.