mysql: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for opera

昨天把mysql里所有table的varchar字段的字符集,批量换成了utf8mb4/utf8mb4_unicode_ci ,以便能保存一些emoji火星文 , 结果有一个sql语句执行时,报错如下:

Illegalmixofcollations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT)foroperation '=

观察了一下,这个sql使用了一个自定义的function,这个函数的入口参数为varchar,类似如下:

CREATE FUNCTION `f_xxx`(
  p_ref_type     VARCHAR(50) ,
  ...
)

问题就在这里,把p_ref_type的参数类型改成:

CREATE FUNCTION `f_xxx`(
  p_ref_type     VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  ...
)

就ok了。

当然,如果不想改function,还有一种一劳永逸的解决办法:(需要停机)

出错信息的意思是 utf8-unicode-cli与utf-general-cli 不能混用,可以用

show create function f_xxx\G 查看下结构,重点观察下最后几行:

...

  END
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

这里可以看出,collation_connnection 与 Database Collation所用的字符集不一致,要想办法弄成一致,可以再用:

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

查看mysql的相关系统变量:

| Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | utf8mb4_general_ci | | collation_database | utf8mb4_general_ci | | collation_server | utf8mb4_general_ci |

大概输出是这样的,注意最后3行,确认mysql实例的编码后,可以调整database的编码:

ALTER DATABASE `数据库名` character set utf8mb4 COLLATE utf8mb4_general_ci ;

考虑到,有些表或字段在创建时,可能也指定了其它编码,可以用:

./mysqldump -u用户名 -p -d --add-drop-table 数据库名 > 导出的文件名

注:为防止操作期间有人操作数据库,最好将所有应用暂时停机,保证操作期间db没人向其写入数据 。

导出表结构的sql,然后用文本编码工具,批量把指定字符集的字符串给批量清空,即:清除所有指定字符集的DDL语句,全部默认用database本身的字符集

接下来用

./mysqldump -u用户名 -p -t 数据库名 > 导出的文件名 

导出所有数据

最后,干掉所有table,然后mysql命令行下

source 修改好以后的数据库表结构.sql

source 数据.sql

将所有表重建,然后导入数据,进行恢复,最终目标是将整个db,包括所有表及字段的字符集大换血了

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏james大数据架构

微软官方提供的用于监控MS SQL Server运行状况的工具及SQL语句

Microsoft SQL Server 2005 提供了一些工具来监控数据库。方法之一是动态管理视图。动态管理视图 (DMV) 和动态管理函数 (DMF) 返...

32370
来自专栏PPV课数据科学社区

SQL Server优化50法

查询速度慢的原因很多,常见如下几种: 1、没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷) 2、I/O吞吐量小,形成了瓶颈效...

45870
来自专栏JMCui

SQL优化二(SQL性能调优)

一·、前言:这篇博文内容非原创,是我们公司的架构师给我们做技术培训的时候讲的内容,我稍微整理了下,借花献佛。这篇博文只是做一个大概的科普介绍,毕竟SQL优化的知...

43160
来自专栏Java架构师历程

Mysql面试题目

5>.InnoDB不支持全文索引,而MyISAM支持。(X) (2) 问各种不同mysql版本的2者的改进 (3)2者的索引的实现方式

26230
来自专栏程序小工

MySQL数据库常见名词对比

MySQL 由于性能高、成本低、可靠性好,已经成为最流行的开源数据库,因此被广泛地应用在 Internet 上的中小型网站中。随着 MySQL 的不断成熟,它也...

16330
来自专栏Java3y

Oracle总结【视图、索引、事务、用户权限、批量操作】

前言 在Oracle总结的第一篇中,我们已经总结了一些常用的SQL相关的知识点了…那么本篇主要总结关于Oralce视图、序列、事务的一些内容… 在数据库中,我们...

42540
来自专栏PHP在线

MySQL存储引擎总结

前言 在数据库中存的就是一张张有着千丝万缕关系的表,所以表设计的好坏,将直接影响着整个数据库。而在设计表的时候,我们都会关注一个问题,使用什么存储引擎。等一下,...

42760
来自专栏xcywt

学习SQLite之路(一)

  工作快一年了,接触的东西不是很多,学到的东西也不多。无意中看到公司的代码有一点关于sqlite3的(不是我这一层负责的代码),于是乎就学学试试。  参考: ...

21270
来自专栏程序员历小冰

MySQL探秘(二):SQL语句执行过程详解

 昔日庖丁解牛,未见全牛,所赖者是其对牛内部骨架结构的了解,对于MySQL亦是如此,只有更加全面地了解SQL语句执行的每个过程,才能更好的进行SQL的设计和优化...

32330
来自专栏FreeBuf

安全科普:SQLi Labs 指南 Part 1

译者:SQL Libs一直也没看到有人写过比较完整的指南,只有作者在自己的博客上帖了一些tip和一些视频,偶然看到一篇文章在写这个,便拿过来翻一下,以作参考,原...

20990

扫码关注云+社区

领取腾讯云代金券