1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

今天有个SQL执行不了，通过错误输出查到了问题，大致SQL如下：


SELECT * FROM `table` WHERE `field1`=`filed2`


这个SQL看起来是一点错误都没有，但是缺爆出了下面这个错误：


Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='


意思就是编码不一致不能用“=” 运算符。那怎么办呢。。。还用说就是转换编码了，还好MySQL也有相关的函数。


CONVERT(table-colum USING utf8) COLLATE utf8_unicode_ci


比如是field1编码是utf8_unicode_ci，filed2编码是utf8_general_ci那么就会出现，那么SQL语句应该修改成这样子。


SELECT * FROM `table` WHERE `field1`=CONVERT(`filed2` USING utf8) COLLATE utf8_unicode_ci


这样编码就统一了，不过这个只是临时解决。最终还是根解决最好，直接修改字段的本身编码。

The default collation for stored procedure parameters is utf8_general_ci and you can't mix collations, so you have four options:

Option 1: add COLLATE to your input variable:
SET @rUsername = ‘aname’ COLLATE utf8_unicode_ci; -- COLLATE added
CALL updateProductUsers(@rUsername, @rProductID, @rPerm);

Option 2: add COLLATE to the WHERE clause:
CREATE PROCEDURE updateProductUsers(
    IN rUsername VARCHAR(24),
    IN rProductID INT UNSIGNED,
    IN rPerm VARCHAR(16))
BEGIN
    UPDATE productUsers
        INNER JOIN users
        ON productUsers.userID = users.userID
        SET productUsers.permission = rPerm
        WHERE users.username = rUsername COLLATE utf8_unicode_ci -- COLLATE added
        AND productUsers.productID = rProductID;
END

Option 3: add it to the IN parameter definition:
CREATE PROCEDURE updateProductUsers(
    IN rUsername VARCHAR(24) COLLATE utf8_unicode_ci, -- COLLATE added
    IN rProductID INT UNSIGNED,
    IN rPerm VARCHAR(16))
BEGIN
    UPDATE productUsers
        INNER JOIN users
        ON productUsers.userID = users.userID
        SET productUsers.permission = rPerm
        WHERE users.username = rUsername
        AND productUsers.productID = rProductID;
END

Option 4: alter the field itself:
ALTER TABLE users CHARACTER SET utf8 COLLATE utf8_general_ci;

Unless you need to sort data in Unicode order, I would suggest altering all your tables to use utf8_general_ci collation, as it requires no code changes, and will speed sorts up slightly.

UPDATE: utf8mb4/utf8mb4_unicode_ci is now the preferred character set/collation method. utf8_general_ci is advised against, as the performance improvement is negligible. See https://stackoverflow.com/a/766996/1432614

I spent half a day searching for answers to an identical "Illegal mix of collations" error with conflicts between utf8_unicode_ci and utf8_general_ci.

I found that some columns in my database were not specifically collated utf8_unicode_ci. It seems mysql implicitly collated these columns utf8_general_ci.

Specifically, running a 'SHOW CREATE TABLE table1' query outputted something like the following:
| table1 | CREATE TABLE `table1` (
`id` int(11) NOT NULL,
`col1` varchar(4) CHARACTER SET utf8 NOT NULL,
`col2` int(11) NOT NULL,
PRIMARY KEY (`col1`,`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

Note the line 'col1' varchar(4) CHARACTER SET utf8 NOT NULL does not have a collation specified. I then ran the following query:

ALTER TABLE table1 CHANGE col1 col1 VARCHAR(4) CHARACTER SET utf8 
COLLATE utf8_unicode_ci NOT NULL;

This solved my "Illegal mix of collations" error. Hope this might help someone else out there.