MySQL error: Illegal mix of collations for operation 'UNION'

Question

I am Boxonix!

I am currently making a comment section for my website, and I'm tiny a bit in trouble! This "Illegal mix of collations for operation 'UNION'" pops out when i run this query:

SELECT *
FROM comments
JOIN users ON comments.user_id = users.id
ORDER BY users.id DESC LIMIT $LIMIT1
UNION
SELECT *
FROM comments
JOIN videos ON comments.video_id = videos.id

I am already kind a bit confused, I'm not using MySQL that often!
Please help!

Answer 1

You can force collation in SELECT part of query. For example if column videos.comment is utf8_general_ci and comments.comment is utf8_unicode_ci you can force it to be same:

SELECT 
   comment COLLATE utf8_general_ci
FROM comments
UNION
SELECT 
   comment
FROM videos

But this has limited use in case that collations can't be translated exactly as you want.

Answer 2

To avoid "Illegal mix of collations for operation 'UNION'" MySQL error you must verify that same-index columns have the same type of collation or encoding.

Answer 3

The problem is that the two sub-queries will have different column sets.

The first sub-query is going to give you all columns from comments and all columns from users. The second sub-query is going to give you all columns from comments and all columns from videos. Unless the two tables users and videos have the exact same column definitions, then the UNION is not going to work.

You need to decide what columns you need from each query and then customise each SELECT ... so that they match.