views:

5503

answers:

2

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)

+2  A: 

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

kristof
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? :-)
Greg
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)?
kristof
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.
Greg
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:)
kristof
+1  A: 

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.

Greg