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

标签: 默认分类 | 发表时间:2012-02-10 00:08 | 作者:Hins_pan
出处:http://hi.baidu.com/hins%5Fpan

对于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:再一次看到这篇文章,依然觉得很赞,转过来学习~~


类别: 默认分类  查看评论

相关 [mysql5 字符集 编码] 推荐:

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

- - 小彰
对于MySQL5字符集和编码的研究起源于在近期开发过程中的两个异常. 通过查看项目使用的数据库,发现发生问题的数据表的编码是utf8,而该表中的SNAPURL的编码却被设置成gbk,这应该就是造成上述问题的原因. 将表中编码为gbk的列修改为utf8后,异常消失. 1、MySQL5的字符集支持. MySQL5.1中的字符集支持包括在MyISAM、MEMORY和InnoDB存储引擎中.

中文字符集编码Unicode ,gb2312 , cp936 ,GBK,GB18030

- - 博客园_学院派的驴
转自: http://hi.baidu.com/okptqdwpfrbosuq/item/0fc063f8b65f0516d6ff8c03. 中文字符集编码Unicode ,gb2312 , cp936 ,GBK,GB18030. 转自: http://www.blog.edu.cn/user3/flyingcs/archives/2006/1418577.shtml 概要:UTF-8的一个特别的好处是它与ISO- 8859-1完全兼容,可以表示世界上所有的字符,汉字通常用3个字节来表示.

(原创)Linux下MySQL 5.5的修改字符集编码为UTF8(彻底解决中文乱码问题)

- - 服务器运维与网站架构|Linux运维|X研究
PS:昨天一同事遇到mysql 5.5中文乱码问题,找我解决. 解决了,有个细节问题网上没人说,我就总结一下. 一、登录MySQL查看用SHOW VARIABLES LIKE ‘character%’;下字符集,显示如下:. character_set_database和character_set_server的默认字符集还是latin1.

Mysql 乱码问题--如何查看和修改Mysql 的字符集

- - ITeye博客
MySQL会出现中文乱码的原因不外乎下列几点:.    1.server本身设定问题,例如还停留在latin1.    2.table的语系设定问题(包含character与collation).    3.客户端程式(例如 php)的连线语系设定问题.    utf8可以兼容世界上所有字符!!!!.

编码

- - 人月神话的BLOG
前面谈需求,架构和设计都比较多,今天谈一下编码方面的内容,做一个好的程序员不容易,很多时候不是体现在需求和架构能力的缺少上面,更多的是体现在最基础的编码和实现能力的不足上面. 编码是一个技术活,需要大量的脑力活动,但是很多人确可以把编码做为一个体力活,我在这里想继续强调的是如果编码是一个完全的重复体力劳动的话,那么所有工作就一定是可以自动化掉的,在这个时候你原来所有的工作没有任何的价值体现而被完全替代.

字符编码

- - 博客 - 伯乐在线
伯乐在线注:本文来自文章作者 @acmerfight 的投稿( 原文链接). 如果其他朋友也想投稿,请发邮件至 webmaster@jobbole.com,或直接给 @伯乐在线官方微博 私信投递. 你是否认为“ASCII码 = 一个字符就是8比特”. 你是否认为一个字节就是一个字符,一个字符就是8比特.

将URL编码?

- - JavaScript - Web前端 - ITeye博客
    URL一般只能由字母、数字、$ - _. * ' ( ) 等一些字符构成. 那么当URL中需要用到汉字时怎么办,譬如有这样的URL: "www.test.com/search?name=张三",此时,只有通过将URL进行编码的方式进行传递了.     Javascript编/解码方法:.     如果对上面的URL(www.test.com/search?name=张三)进行编码的话.

编码风格不是编码规范

- - 外刊IT评论
我并不认为程序员是一个情绪特别丰富的群体. 但有一些事情却能很容易刺激程序员的神经,那就是代码格式和布局. 如果看到一个函数的括弧在同一行上没有闭合,我的眼睛会喷血. 如果看到有人没有 恰好的在两个函数间留一空行,我的小腿会抽筋. 但重点在这里——除非是在家里开发自己的业余爱好软件,我的这些个人喜好其实是无关紧要的.

理清URL编码

- winners - Thinking for Fun
关于URL编码,RFC1738做了如下的规定:. “Only alphanumerics [0-9a-zA-Z], the special characters “$-_.+!*’(),” [not including the quotes - ed], and reserved characters used for their reserved purposes may be used unencoded within a URL.”.