Illegal mix of collations for operation 'concat'

Question

I'm trying to execute this concat query in mysql

SELECT CONCAT(if(fName,fName,''),Name) 
From Student

Error:

#1271 - Illegal mix of collations for operation 'concat'

Answer 1

This is due to collections difference, you can solve by converting the two strings or columns to one collection say UTF8

CONCAT(CAST(fName AS CHAR CHARACTER SET utf8),CAST('' AS CHAR CHARACTER SET utf8))

This will solve :)

you can check more about casting in MySQL here MySQL Casting

Answer 2

The charsets and/or collations you use in your connection do not match the charset/collation in your table.

There are 4 solutions:

1- Change the charset in your connection:

//find out the charset used in your table.
SHOW TABLES LIKE 'student'
//set the server charset to match
SET NAMES 'charset_name' [COLLATE 'collation_name']

2- Change the charset used in your table to match the server charset:

//find out the charset used in the server
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
//Change the charset used in the table
ALTER TABLE student ......

3- Change the default charset settings and restart MySQL

Edit My.ini and replace the character_set_* options, so they match your tables.

4- Change the charset settings for your connection

You client can override the charset and collation settings.
If it does not option 1 or 3 should fix your issue, but if the connection overrides these settings, you need to check the connection-string and edit the charset/collation settings to match your database.

Some advice:

Find a charset. I recommend UTF8 and a collation: I recommend utf8_general_ci. And use those consistantly everywhere.