[转]MySQL5字符集支持及编码研究

对于MySQL5字符集和编码的研究起源于在近期开发过程中的两个异常

1）java.sql.SQLException: Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

2）java.sql.SQLException: Incorrect string value: '\xEF\xBF\xBD\xEF\xBF\xBD...' for column 'SNAPURL' at row 1

通过查看项目使用的数据库，发现发生问题的数据表的编码是utf8，而该表中的SNAPURL的编码却被设置成gbk，这应该就是造成上述问题的原因。将表中编码为gbk的列修改为utf8后，异常消失。问题初步解决。

1、MySQL5的字符集支持

MySQL5.1中的字符集支持包括在MyISAM、MEMORY和InnoDB存储引擎中。MySQL5支持多种字符集来存储字符串，对每种字符集也有相应的校对规则(Collation)来进行比较。MySQL5还支持从服务器、数据库到数据库表、列、连接等多种级别的字符集和校对规则。如上述异常发生时为数据库表和表中列指定不同的字符编码。

2、MySQL5字符集及校对规则

MySQL5字符集和校对规则有4个级别的 默认设置：服务器级、数据库级、表级和连接级。

1）服务器字符集和校对

MySQL服务器有一个服务器字符集和一个服务器校对规则，它们均不能设置为空。

MySQL按照如下方法确定服务器字符集和服务器校对规则：

・当服务器启动时根据有效的选项设置

・根据运行时的设定值

可以通过在构建安装程序时将--with-charset和--with-collation作为 configure的参数来设定服务器字符集和校对。

如  shell> ./configure --with-charset=latin1 --with-collation=latin1_german1_ci

当前的服务器字符集和校对规则通过character_set_server和collation_server系统变量的值表示。在运行时能够改变这些变量的值。

2）数据库字符集和校对

每一个数据库有一个数据库字符集和一个数据库校对规则，它不能够为空。MySQL这样选择数据库字符集和数据库校对规则：

・如果在数据库创建SQL中指定了CHARACTER SET X和COLLATE Y，那么采用字符集 X和校对规则 Y。如  CREATE DATABASE db_name DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

・如果指定了CHARACTER SET X而没有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。

・否则，采用服务器字符集和服务器校对规则。即MYSQL系统变量character_set_server和collation_server的值

默认数据库的字符集和校对规则通过character_set_database和 collation_database系统变量的值表示。无论何时默认数据库更改了，服务器都设置这两个变量的值。如果没有 默认数据库，这两个变量与相应的服务器级别的变量（character_set_server和collation_server）具有相同的值。如果在CREATE TABLE语句中没有指定表字符集和校对规则，则使用数据库字符集和校对规则作为默认值。

3）表字符集和校对

每一个表有一个表字符集和一个校对规则，它不能为空。MySQL按照下面的方式选择表字符集和 校对规则：

・如果在创建表的SQL语句中指定了CHARACTER SET X和COLLATE Y，那么采用CHARACTER SET X和COLLATE Y。如 CREATE TABLE A DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

・如果指定了CHARACTER SET X而没有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。

・否则，采用数据库字符集和数据库校对规则。

如果在列定义中没有指定列字符集和校对规则，则默认使用表字符集和校对规则。表字符集和校对规则是MySQL的扩展;在标准SQL中没有。

4）列字符集和校对

每一个“字符”列（即，CHAR、VARCHAR或TEXT类型的列）有一个列字符集和一个列 校对规则，它不能为空。MySQL按照下面的方式选择列字符集和校对规则：

・如果指定了CHARACTER SET X和COLLATE Y，那么采用CHARACTER SET X和COLLATE Y。如

CREATE TABLE Table1

(

          column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci

);

・如果指定了CHARACTER SET X而没有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。

・否则，采用表字符集和服务器校对规则。

5）连接字符集和校对

在客户端和服务器的连接处理中也涉及了字符集和校对规则变量。每一个客户端有一个连接相关的字符集和校对规则变量。

客户端发送SQL语句，例如查询，通过连接发送到服务器。服务器通过连接发送响应给客户端，例如结果集。对于客户端连接，这样会导致一些关于连接的字符集和 校对规则的问题，这些问题均能够通过系统变量来解决：

・当查询离开客户端后，在查询中使用哪种字符集？

服务器使用character_set_client变量作为客户端发送的查询中使用的字符集。

・服务器接收到查询后应该转换为哪种字符集？

转换时，服务器使用character_set_connection和collation_connection系统变量。它将客户端发送的查询从character_set_client系统变量转换到character_set_connection（除非字符串文字具有象_latin1或_utf8的引介词）。collation_connection对比较文字字符串是重要的。对于列值的字符串比较，它不重要，因为列具有更高的 校对规则优先级。

・服务器发送结果集或返回错误信息到客户端之前应该转换为哪种字符集？

character_set_results变量指示服务器返回查询结果到客户端使用的字符集。包括结果数据，例如列值和结果元数据（如列名）。

用户可以修改这几个系统变量的值。在MYSQL中，SET NAMES 'charset_name'能够影响连接字符集

SET NAMES ' x' 语句与下列三个语句等价：

mysql>   SET character_set_client =    x;

mysql>   SET character_set_results =    x;

mysql>   SET character_set_connection =    x;

7）字符串文字字符集和校对

每一字符串字符文字有一个字符集和一个校对规则，它不能为空。一个字符串文字可能有一个可选的字符集引用介词和COLLATE子句。如 SELECT _latin1' string ' COLLATE latin1_danish_ci;

MySQL这样确定一个文字字符集和校对规则：

・如果指定了CHARACTER SET X和COLLATE Y，那么使用CHARACTER SET X和COLLATE Y。

・如果指定了CHARACTER SET X而没有指定COLLATE Y，那么使用CHARACTER SET X和CHARACTER SET X的默认校对规则。

・否则，使用通过character_set_connection和collation_connection系统变量给出的字符集和校对规则。

3、异常原因分析

通过上述对MySQL5字符集和校对规则的了解，就能够很清楚的分析出前言中的异常发生的原因了：

异常发生时的MySQL字符集和校对方式情况：

character_set_connection=utf8，校对方式为utf8默认校对方式

character_set_server=utf8，校对方式为utf8默认校对方式

character_set_database=utf8，校对方式为utf8默认校对方式

1）对于异常java.sql.SQLException: Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='；

分析：其发生的SQL语句形如：SELECT COUNT(*) FROM PAGE      WHERE SNAPURL ='http://snap.xxx.com/中文'；由于没有显示的指定字符串的字符集，因此MySQL采用character_set_connection和collation_connection系统变量指定的值来处理，即utf8编码字符串，以utf8的默认校对规则来处理'http://snap.xxx.com/中文'；同时，由于列SNAPURL是gbk编码的，因此MySQL对SNAPURL列采用的是gbk的字符集和gbk默认校对规则处理。因此在该SQL语句中'='两端的校对规则不一致，也就产生了先前的异常。

2）对于异常java.sql.SQLException: Incorrect string value: '\xEF\xBF\xBD\xEF\xBF\xBD...' for column 'SNAPURL' at row 1

分析：其发生的SQL语句形如：insert into PAGE(CONTENT) values('中文xxxx')；由对异常一的分析可知，异常二的发生也主要是因为MySQL对执行的SQL语句采用utf8编码，但是表PAGE的列CONTENT是gbk编码的，因此要将utf8的字符串插入到gbk编码的列中，由于utf8与gbk不兼容，因此发生前述异常。

4、其他

MySQL5中与字符集相关的系统变量除了前面提到的character_set_server、character_set_database、character_set_connection以外，还有character_set_client、character_set_results、character_set_system。其中character_set_client表示客户端发送的字符串使用的字符集（客户端发送的字符串在系统中会转码为charcter_set_connection表示的字符集并得到执行）。character_set_results表示MySQL发回给客户端的数据使用的字符集。character_set_system表示MySQL系统元数据使用的字符集。

5、尾声

本文是我根据MySQL参考手册相关内容和自己的理解写的，有不当之处希望得到各位的指正和交流。

PS：再一次看到这篇文章，依然觉得很赞，转过来学习~~