前言
加入创业公司就是不一样,什么事情都得自己搞,还好在中小公司待多了,各方面都会一些。今天同事创建了一个新库,导入原有表记录之后,运行项目报错,利用中午休息的时间解决了,顺便说一下相关还会引起这个问题的解决办法。
关于字符集这块的问题,以MySQL为例,它的字符集有两个概念,一个是Character Sets,一个是Collations,前者是字符的编码,后者是指对前者进行比较操作的规则字符集。
重点:这2个参数可以在数据库实例、单个数据库、表(函数)、列等四个级别指定。
字符集可能涉及到的问题:
1.代码操作数据库,中文参数查询不到结果;
2.代码调用SQL运行报错;
3.mix of collations(utf8_general_ic,IMPLICIT) and(utf8_unicode_ci,IMPLICIT)错误;
…欢迎补充
总结
- 喜欢把总结写在前面给着急的你看。
- 发生DAO层操作数据库报错,首先判断是否是环境引起的,例如迁移数据库,重建新建等等,确定问题到底在代码上还是数据库上;
- 在SQL工具上单独执行该SQL,是否会报错,报错则更改SQL,不报错则检查其他配置,确认问题是涉及配置的还是什么,有针对性的查,并对数据库做多方查询,有全局设置、数据库设置、表设置等。
防患于未然,特别是在更换环境的时候,创建数据库也好,项目也好,我们应该遵循一个原则,保证配置一致性。
尽可能不要在创建环境时使用默认配置,尽可能手工指定其保证一致性,因为你不知道你在哪一环可能没设置,这是你环境导致项目出错的绝大多数原因的根。
- 首选在编译安装MySQL的时候指定两个参数使用utf8编码。
- 次选在配置文件my.cnf或my.ini设定两个参数,同时设置init_connect参数。
- 第三在配置文件my.cnf或my.ini设定两个参数,同时客户端的连接指定set names命令。
- 在配置文件my.cnf里的client和server处加入default-character-set参数方便管理。
正文
这个是我报错的问题:
mix of collations(utf8_general_ic,IMPLICIT) and(utf8_unicode_ci,IMPLICIT)...
意思就是我的某个地方的排序编码规则不一致产生报错,它提示我不能混用。
下面先认识一下这个collations到底是什么:
一个字符集有一个或多种collation,并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin(二元)结束。
在做比较时,应该确保两个表的字符排序相同。一般建表的时候不指定,可以走默认的,全是默认的就没什么问题了
Tips:
utf8_general_ci和utf8_unicode_ci的区别,前者校对速度快,但准确度稍差;后者准确度高,但校对速度稍慢
那么本文定义就清楚了,是围绕字符集(排序规则)进行查找错误原因。
后面将详述所有可能的查找和解决办法(虽然我并没有遇到)。
查询手段
当前MySQL的字符集
show variables like 'character_set_%';
当前MySQL服务器字符集校验设置
show variables like 'collation_%';
显示当前MySQL服务器某个对象(数据库、表、函数)设置
show create database 数据库名; -- 显示建库的设置
show create table 表名; -- 显示建表的设置
show create function 函数名; -- 显示函数创建及设置
show create procedure 过程名; -- 显示存储过程创建及设置
.....
详细解决
先确认自己哪块代码运行报错,这可以确定问题是在哪个数据库实例、哪个数据库、哪个表、哪个函数上。
下面全部操作均以Character=utf8,Collations=utf8_general_ci
为标准,会直接上图提供完整操作,最后会附上sql。
1.通过MySQL数据库配置解决
MySQL有默认的字符集,这个是安装的时候确定的,在编译MySQL的时候可以通过DEFAULT_CHARSET= utf8
和DEFAULT_COLLATION=utf8_general_ci
来指定默认的字符集为utf8,这也是最一劳永逸的办法,这样指定后,客户端连接到数据库的编码方式也默认是utf8了,应用程序不需要任何处理。
大多数问题都是出现在该值的配置上。
先查MySQL配置,找症结
查询数据库实例字符集配置,如下图,有的同学会发现其中有字符集不匹配,latin1为MySQL默认字符集,更改就好。
继续查看数据库排序字符集设置,如下图
测试,失败!(这不是楼主的问题,是为了给大家看流程特地改错的)
遇到java调用mysql,参数为中文查不到数据的问题,请参考本小点内容解决,由于有一些设置只能改客户端,并不能更改服务端(Linux等)数据库配置,也就是说服务器数据库的数据编码格式还是有问题,照样查不到匹配的数据,请移步到后面查看解决服务端MySQL字符集配置问题。
2.通过改表、函数、列字符集解决
改到这里,有的同学测试还是报错,别急还有呢!请关注你报错的SQL,是访问了哪个表、函数…,直接加入命令对其进行修改,下面直接上命令了
测试,失败!(这不是楼主的问题,是为了给大家看流程特地改错的)
3.通过修改临时表字符集解决
着重说明下面这个,楼主解决的问题就是换新库后代码调用MySQL存储过程报错,看下图乍看之下都是对的,字符集都没问题啊
然后去存储过程里看涉及到的表,字符集也没问题,再看详细的每个字段,参数都是对的。那么问题在哪,最后回头一看,存储过程里创建了一张临时表,尝试给其加上字符集
测试,通过!至此,楼主的问题解决。
解决服务端MySQL字符集配置问题
好了,自己犯下的错,跪着也要挽回(别人创建的数据库不设默认字符集,这个锅还是你接下了)。
对于使用者来说,一般推荐使用utf8编码来存储数据。而要解决乱码问题,不单单是MySQL数据的存储问题,还和用户的程序文件的编码方式、用户程序和MySQL数据库的连接方式都有关系。
先来设置MySQL的字符集,直接修改文件:
Windows用户请找到MySQL安装目录下的my.ini;
Linux或Unix用户请找到small.cnf、my-medium.cnf、my-huge.cnf、my-innodb-heavy-4G.cnf其中之一到/etc下,命名为my.cnf,命令应该是cp …(不熟Linux)
打开my文件,找到[client]和[mysqld],在这2个下面添加
default-character-set=utf8
若你的MySQL是5.5版本的,在[mysqld]下添加
character-set-server=utf8
至此,服务端MySQL的配置就解决了,以后创建数据库等就不一定需要指定字符集,因为全局已经默认配置了,记得改前停数据库,改后启动。
服务端改完了,接下来保证客户端的连接请求编码也一致,就不会出现问题了,如下的配置通常都是在客户端进行的
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
那么这时候,是否有在服务端解决问题的办法,可行的思路是在init_connect里设置。
这个命令在每个普通用户连接上来的时候都会触发执行,可以在[mysqld]增加以下一行设置连接字符集
init_connect = 'SET NAMES utf8';
至此,完整解决从客户端设置 --> 客户端连接服务端 --> 服务端配置。如此完整设置后, 必定不会再产生乱码问题。
关于页面与后台乱码问题,请移步查看博主其他说明乱码的文章。
后附Linux下修改MySQL字符集配置的说明。
附:SQL操作
查看数据库字符集
show variables like 'collation_%';
show variables like 'character_set_%';
修改数据库字符集
set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_database=utf8;
set character_set_results=utf8;
set character_set_server=utf8;
set character_set_system=utf8;
set collation_connection=utf8;
set collation_database=utf8;
set collation_server=utf8;
查询、修改、创建数据库默认字符集(表的操作语法一致,database–>table)
show create database 数据库名;
alter database 数据库名 default character set = utf8;
create database 数据库名 default character set utf8 collate utf8_general_ci;
批量更改表的字符集
SELECT a.TABLE_TYPE,CONCAT('alter TABLE ',A.TABLE_NAME,' default character set = utf8;')
FROM INFORMATION_SCHEMA.TABLES A
WHERE A.TABLE_SCHEMA='MINI'
AND a.TABLE_TYPE='BASE TABLE';