Illegal mix of collations for operation 'concat'

开发者_运维百科

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'

---

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

---

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.

---

A concatenation can only work if the collation of all used values matches OR you use a collation that all collations are a subset of (from a logical standpoint).

If you want to concatenate text, each text should be the same collation. Take a look at the collation the database uses, then take a look at the collation that your connection uses:

show variables like '%coll%'

The collation_connection should match the collation of the table you try to concatenate. If it doesn't, the error message will arise.

You can then change the connection collation to match the one of the table.

---

Look like you have a miss use on the if statement there because it will resulting an undefined data type so the concat operation will fail as it different in data type. Try change the query by use ifnull instead.

Try this query instead:

SELECT concat(ifnull(fName,''),Name) From Student

see the demo here http://www.sqlize.com/kfy85j8f1e

for another reference read also http://forums.mysql.com/read.php?10,225982,225982#msg-225982

---

It can also be an error with your client library being too old for the mysql server.
We had a similar problem with LIKE and the character "ő" and using PHP MySQL library version 5.1.52 but MySQL server version 5.5.22.
The problem has gone away upon upgrading the client library.