MySQL – Fixing Illegal mix of collations Message

I wanted to do a union in order to insert a row at the beginning of the results for a query like so

select 'ALL' as id, 'All' as name
union
select owner as id, owner as name
from
account
order by name;

However it gave me this error:

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

So I tried using the collate clause to make everything the same:

select 'ALL' COLLATE latin1_swedish_ci as id,'All' COLLATE latin1_swedish_ci as name
union
select owner as id, owner as name
from
account
order by name;

But then I got this error:

COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'utf8'

So it turns out to fix this you have to set the character set and the collation in your select statement like so:

select _latin1 'ALL' COLLATE latin1_swedish_ci as id,_latin1 'All' COLLATE latin1_swedish_ci as name
union
select owner as id, owner as name
from
account
order by name;

2 Responses to “MySQL – Fixing Illegal mix of collations Message”

  1. Jody says:

    I didn’t realize that worked. I thought you had to select blah from blahblah
    union
    select blah2 from blahblah

  2. Sorry Jody, that may only be for MySQL, not SQL Server. But give it a try, let us know.