[Err] 1271 - Illegal mix of collations for operation 'concat'

背景,

MySQL 5.1.54执行在MySQL 5.5.12 调试好存储过程，call 提示如下错误,处理方式:

[Err] 1271 - Illegal mix of collations for operation 'concat'

查验,Mysql 5.5.12下字符集utf8 创建sp(sp_xxx_everyday) 如下:

character_set_client utf8

character_set_connection utf8

character_set_database utf8

character_set_filesystem binary

character_set_results utf8

character_set_server utf8

character_set_system utf8

character_sets_dir /usr/local/services/mysql/share/charsets/

CREATE  PROCEDURE `sp_xxx_everyday`()

BEGIN

DECLARE tab_num INT;

DECLARE bak_dt VARCHAR(18);

DECLARE tbname VARCHAR(18);

# DECLARE tab_dt CHAR(18);

# SET tab_dt=REPLACE(REPLACE(CONCAT(DATE_ADD(CURDATE(),INTERVAL -1 DAY),'21:00:00','to','20:00:00'),'-',''),':','');

# SET tab_dt=REPLACE(CONCAT('21:00:00','to','20:00:00'),':','');

SET bak_dt=REPLACE(DATE_ADD(CURDATE(),INTERVAL -1 DAY),'-','');

SET tbname=CONCAT('xx_',bak_dt);

SELECT COUNT(0) INTO tab_num FROM information_schema.TABLES WHERE TABLE_SCHEMA='xxDB' AND TABLE_NAME=tbname;

IF tab_num=0 THEN

SET @a=CONCAT('CREATE TABLE xx_',bak_dt,'(',

'Id int(11) NOT NULL AUTO_INCREMENT COMMENT ''流水Id'',

Role_Id int(11) NOT NULL,

Guild_Id int(11) NOT NULL,

Level int(11) NOT NULL COMMENT ''等级'',

Fight_Score int(11) NOT NULL COMMENT ''战斗力评分'',

.......

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''xxxx''');

PREPARE cmd FROM @a;

EXECUTE cmd;

SET @b=CONCAT('INSERT INTO xxx_',bak_dt,'(Role_Id,xx,xxx,xxx)

SELECT Role_Id,xx,xxx,xx

FROM guild_xxx 

WHERE FeedPetTime>=CONCAT(DATE_ADD(CURDATE(),INTERVAL -1 DAY),'' '',''21:00:00'') AND Vitality>0

ORDER BY Vitality DESC,FeedPetTime ASC');

PREPARE cmd FROM @b;

EXECUTE cmd;

DEALLOCATE PREPARE cmd;

ELSE

#   SET @a=CONCAT('ALTER TABLE guild_xxx_',bak_dt,'RENAME TO test.guild_xxx_',bak_dt);

# PREPARE cmd FROM @a;

# EXECUTE cmd;

# DEALLOCATE PREPARE cmd;

SET @a=CONCAT('CREATE TABLE xx_',bak_dt,'(',

'Id int(11) NOT NULL AUTO_INCREMENT COMMENT ''流水Id'',

Role_Id int(11) NOT NULL,

Guild_Id int(11) NOT NULL,

Level int(11) NOT NULL COMMENT ''等级'',

Fight_Score int(11) NOT NULL COMMENT ''战斗力评分'',

.......

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''xxxx''');

PREPARE cmd FROM @a;

EXECUTE cmd;

SET @b=CONCAT('INSERT INTO xxx_',bak_dt,'(Role_Id,xx,xxx,xxx)

SELECT Role_Id,xx,xxx,xx

FROM guild_xxx 

WHERE FeedPetTime>=CONCAT(DATE_ADD(CURDATE(),INTERVAL -1 DAY),'' '',''21:00:00'') AND Vitality>0

ORDER BY Vitality DESC,FeedPetTime ASC');

PREPARE cmd FROM @b;

EXECUTE cmd;

DEALLOCATE PREPARE cmd;

END IF;

END;

在生产环境中MySQL5.1.54创建ok，但是call sp_xxx_everyday();

提示错误:[Err] 1271 - Illegal mix of collations for operation 'concat'

搜索一把...得知可能是字符集问题，果然，如下，当前mysql DB字符集latin1,故导致执行问题

character_set_client utf8

character_set_connection utf8

character_set_database latin1

character_set_filesystem binary

character_set_results utf8

character_set_server latin1

character_set_system utf8

character_sets_dir /data/mysql_root/base/share/mysql/charsets/

调整方法:

将 DECLARE bak_dt VARCHAR(18);

DECLARE tbname VARCHAR(18);

后面加 character set utf8

DECLARE bak_dt VARCHAR(18) character set utf8;

DECLARE tbname VARCHAR(18) character set utf8;

其他无需变动，call sp_xxx  执行 ok。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26855487/viewspace-765054/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26855487/viewspace-765054/