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

mysql

Solution

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.

Problem

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!

Original source