MySQL数据库字符集引发存储过程【Illegal mix of collations】问题总结

MySQL数据库字符集

1.1.1  MySQL字符集情况介绍

I. 字符集

字符值包含字母、数字和特殊符号。在字符值可以存储之前，字母、数字和字符必须转换为数值代码。所以必须建立一个转换表，其中包含了每个相关字符的数值代码。这样的转换表就称为字符集，有时也称为代码字符集（code character set）和字符编码（characterencoding）。

而在MySQL中，字符集的概念和编码方案的概念被看作是同义词。一个字符集(character set)是一个转换表和一个编码方案的组合。校对（collation）的概念是为了解决排序的顺序或字符的分组问题。因为字符的排序和分组需要字符之间的比较，校对就定义了这些比较的大小关系。

II. 查看数据库支持字符集

 

III. 查看字符集utf8可用的校对

 

 

IV. 字符集和校对的系统变量

系统变量	说明
CHARACTER_SET_CLIENT	从客户机发送给服务器的语句的字符集
CHARACTER_SET_CONNECTION	客户机和服务器连接的字符集
CHARACTER_SET_DATABASE	当前数据库的默认字符集。每次使用USE语句来“跳转”到另一个数据库时，这个变量就会改变。如果没有当前数据库，其值为CHARACTER_SET_SERVER
CHARACTER_SET_RESULTS	从服务器发送到客户机的SELECT语句的最终结果的字符集，包括列的值，列的元数据——列名，错误信息
CHARACTER_SET_SERVER	服务器的默认字符集
CHARACTER_SET_SYSTEM	系统字符集。用于数据库中对象（如表和列）的名字，也用于存储在目录表中函数的名字。其值总是等于utf8
CHARACTER_SET_DIR	注册的所有字符的文件都在这个目录中
COLLATION_CONNECTION	当前连接的校对
COLLATION_DATABASE	当前日期的默认校对。每次使用USE语句来“跳转”到另一个数据库时，这个变量就会改变。
COLLATION_SERVER	服务器默认校对

 

 

 

V. 数据库对象的字符集的指定有如下继承关系

Server -> Database -> Table ->Column

也就是说，如果后者没有显示指定字符集，那么将采用前者的字符集。

 

1.1.2  存储过程执行错误及引发原因

I. 存储过程执行错误现象

II. 错误引发原因分析与解决方案

Illegal mix of collations(gbk_chinese_ci,IMPLICIT) and (gb2312_chinese_ci,IMPLICIT) for operation ‘=’ ：意思是说字符编码不一样，不能进行比较，也就是说数据库内部的编码都不一样，有的数据是gbk_chinese_ci,有的数据是gb2312_chinese_ci，因此解决此问题的核心就是将数据库所有的编码进行统一。

 

问题引发原因：

 

就是存储过程中传入的参数字符编码与数据库表的字符编码不一致，一个是GBK一个是GB2312，由于字符编码不一样，无法比较（条件匹配查询）。

经过查找定位后发现：创建数据库时指定字符集为gb2312，没有指定比较字符集，而数据库中的表对应字符集为GBK。

 

解决办法：

在创建数据库时，指定数据库字符集并指定比较字符集，参考语句如下 ：

CREATE DATABASE cbs_db DEFAULT CHARACTERSET gbk COLLATE gbk_chinese_ci;

 

character set: 即字符集。

collation,:即比对方法。

collaction是针对字符集来存在的，通常排序有3种不同的方式:ci/ cs/ bin

方式	对应英文	中文含义
ci	capital ignore	不区分大小写
cs	captial sensitive	大写敏感
bin		二进制排序