MySQL Union illegal mix of collations

Question

I have the following in my PHP query:

SELECT tags.tag, theValues.* 
FROM ch09.tbl_tags tags 
RIGHT JOIN ((SELECT 'dog' as 'Vals')
UNION (SELECT 'cat' as 'Vals')) theValues on tags.tag = theValues.Vals

After the "RIGHT JOIN" everything within the brackets are created on the fly from user input.

It worked fine in MySQL 4, but I just had a new computer and installed 5.5, imported the SQL dump as UTF-8. But I get:

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

There's probably a better way to construct my query, all I need is everything the user inputs in one column as "Vals" and if there's a matching tag in tbl_tags.tag then it should be there in a column called "tag" else the tag column is blank.

I have tried using Collate: http://www.answermysearches.com/mysql-fixing-illegal-mix-of-collations-message/352/, but I simply cannot get it to work.

So I need to either construct a better query or use the collate function somehow, but how?

Thanks in advance.

Craig

Loon

Answer 1

Please see the following question: MySQL Encoding Question

I think the problem actually deals with the encoding of the Column and SQL text. To get around this, try using the CONVERT(aaa USING bbb) function as follows in your WHERE clause:

SELECT tags.tag, theValues.* 
  FROM ch09.tbl_tags tags 
 RIGHT JOIN ((SELECT 'dog' as 'Vals')
       UNION (SELECT 'cat' as 'Vals')) theValues
   ON CONVERT(tags.tag USING utf8) = theValues.Vals

Alternatively, change your table's column encoding to be UTF8.

Hope this helps,

john...

Maun

Note that ON tags.tag = theValues.Vals COLLATE utf8_general_ci should work as well. – Blob

Works like a charm. Thanks a lot John ! – Insupportable