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)

From stackoverflow

• 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

  Greg : That might work, but my query has around 80 columns selected, some of them made up of yet more columns. It would take a while to edit the whole query. There's no just "ignore collation errors" setting? :-)

  kristof : Unfortunately I do not know that, but I would doubt so. How would you choose the collation of the union of two incompatible columns then (assuming none of them is of default collation)?

  Greg : It seems to me that MySQL should coerce the results of one of the union queries to match the other, at least if the user sets a setting to do so. It follows a similar philosophy with select concat(1,'a'); giving '1a'. .. but I'm not a database language designer.

  kristof : True, that would probably make sense. If you do not get quick answer to your question, perhaps you may consider changing the collation in the tables. Perhaps there is a way to script/automate it. Ask here perhaps the proper fix will be a faster one as well:)

• 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.