mysql5.6修改字符编码,ERR:Illegal mix of collations for operation 'concat'

mysql5.6修改字符编码,ERR:Illegal mix of collations for operation 'concat'

 

1.问题起因:搭建环境初始化mysql的时候看到mysql配置文件[client]下有设置编码为default-character-set = utf8,大意误以为是全局配置了,其实还需要在[mysqld]下添加character_set_server = utf8参数的

2.尝试在线处理:

  mysql> show variables like 'collation_%';

  mysql> show variables like 'character_set_%';

  看到编码为:latin1

  便尝试使用set命令修改:set GLOBAL Variable_name=utf8_general_ci;     set GLOBAL Variable_name=utf8; 

  结果在执行存储过程中:call xx_ya_stats_xx_daily(2018-05-18,3); 仍然报错:Illegal mix of collations for operation 'concat'

  最后再查看数据库的编码,自动恢复为latin1

3.个人结论:mysql在初始化时使用latin1字符编码,导致了里面的库表数据都是latin1字符编码,虽然改了全局的编码,但库表数据编码还是没有改到,导致失败。

4.最终解决办法:把数据库备份导出,重新初始化mysql实例,再导入数据,恢复正常。

5.步骤:

  导出:mysqldump -uroot -h'127.0.0.1' -P3306 -pxxx-E -R  db_name > db_name.sql

  修改sql:cat db_name.sql | grep -v "ALTER DATABASE \`db_name\` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;" > db_name_new.sql

  初始化同样的新实例:mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=xxxxx

  设置密码:mysql> grant all privileges on *.* to 'root'@'%' IDENTIFIED BY 'xxxx';

       mysql> flush privileges;

  修改密码:mysqladmin -h127.0.0.1 -uroot -p --port=3336 password "new_passwd"

  开启事件:SET GLOBAL event_scheduler = ON;
  查看事件是否开启:mysql> show variables like 'event_scheduler';

  创建数据库并指定字符编码:mysql> CREATE database db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

  导入数据库:mysql -uroot -h'127.0.0.1' -P3306 -pxxxx db_name < db_name_new.sql

  测试:call xx_ya_stats_xxxx_daily(2018-05-18,3);

  查看编码:

    mysql> show variables like 'collation_%';

    mysql> show variables like 'character_set_%';

posted @ 2018-05-21 12:43  chenjianwen  阅读(1225)  评论(0编辑  收藏  举报