Answer My Searches

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;