[转]MySQL乱码问题深层分析

MySQL乱码问题深层分析 
一、概述

　　公司新购了一批PC，准备把几个性能较优的PC升级为数据库服务器，替换老旧的机器。公司有套POS终端软件，后台数据存储是 MySQL 3.23 版。我准备硬件升级的同时升级数据库软件。但是升级过程中遇到闻名的 MySQL 的乱码问题。经过查找资料，加上自己的摸索和经验，终于完美地解决这个问题。

　　MySQL 的乱码问题(不仅仅包括中文乱码，也包括其它语言的乱码，以下称之为乱码问题)只存在于4.1及其以上版本。4.1之前的 MySQL 不支持多语言，所以它会将你给它的数据“原封不动”地保存，再“原封不动”地读出来。从字节的角度来看，数据在这一过程中不会产生任何变化，因此不会有乱码。

　　4.1及以后的版本开始支持多语言，这个所谓的多语言，就是在输入输出时 MySQL 会替你做编码转换。而这个转换规则就是由客户端编码和服务器端编码来决定的。

　　编码转换的规则就是，在输入数据时将编码由“客户端编码”转换为“服务器端编码”，输出时将数据由“服务器端编码”转换为“客户端编码”。

　　二、乱码产生原因

　　MySQL 字符编码是版本4.1引入的，支持多国语言，而且一些特性已经超过了其它大多数数据库管理系统。正因为这一特性才导致 MySQL 的乱码问题。

　　字符集是一套符号和编码。校对规则是在字符集内用于比较字符的一套规则。让我们使用一个假想字符集的例子来区别清楚。

　　假设我们有一个字母表使用了四个字母：‘A’、‘B’、‘a’、‘b’。我们为每个字母赋予一个数值：‘A’=0，‘B’= 1，‘a’= 2，‘b’= 3。字母‘A’是一个符号，数字0是‘A’的编码，这四个字母和它们的编码组合在一起是一个字符集。

　　假设我们希望比较两个字符串的值(在if……else语句中我们经常做值的比较)：‘A’和‘B’。比较的最简单的方法是查找编码：‘A’为0，‘B’为1。因为0 小于1，我们可以说‘A’小于‘B’。我们做的仅仅是在我们的字符集上应用了一个校对规则。校对规则是一套规则(在这种情况下仅仅是一套规则)：“对编码进行比较。”我们称这种全部可能的规则中的最简单的校对规则为一个binary(二元)校对规则。

　　但是，如果我们希望小写字母和大写字母是等价的，应该怎样?那么，我们将至少有两个规则：(1)把小写字母‘a’和‘b’视为与‘A’和‘B’等价;(2)然后比较编码。我们称这是一个大小写不敏感的校对规则。比二元校对规则复杂一些。

　　在实际生活中，大多数字符集有许多字符：不仅仅是‘A’和‘B’，而是整个字母表，有时候有许多种字母表，或者一个东方的(比如中文、日文、韩文、藏文、泰文等等)使用上千个字符的书写系统，还有许多特殊符号和标点符号。并且在实际生活中，大多数校对规则有许多个规则：不仅仅是大小写不敏感，还包括重音符不敏感(“重音符” 是附属于一个字母的符号，象德语的‘?’符号)和多字节映射(例如，作为规则‘?’=‘OE’就是两个德语校对规则的一种)。

　　(以上摘自MySQL 5.1 手册。更多内容可参见：http://dev.mysql.com/doc/refman/5.1/zh/charset.html)

　　MySQL 4.1.x开始支持以下这些事情

　　l 使用多种字符集(Character Set)来存储字符

　　l 使用多种校对规则(Collation)来比较字符串

　　l 在同一台服务器、同一个数据库或甚至在同一个表中使用不同字符集或校对规则来混合字符串

　　l 允许定义任何级别的字符集和校对规则

　　MySQL 4.1及以上版本的字符集支持(Character Set Support)有两个方面：字符集(Character Set)和校对规则(Collation)。 字符集和校对规则有4个级别的默认设置：服务器(server)，数据库(database)，数据表(table)和连接(connection)。

　　MySQL 中是根据下面几个变量确定服务器端和客户端用的什么字符集：

　　character_set_client　　　　 客户端字符集

　　character_set_connection　　 客户端与服务器端连接采用的字符集

　　character_set_results　　　　　SELECT查询返回数据的字符集

　　character_set_database　　　　数据库采用的字符集

　　MySQL的字符集处理是这样的：

　　1、发送请求。

　　1)客户端发送请求到服务器端。

　　2)服务器端会把请求的数据从客户端字符集(character_set_client)转成服务器连接字符集(character_set_connection)。

　　3)然後服务器会检测存储区域(table，column)的字符集，然后把数据从连接字符集(character_set_connection)转为存储区域(table，column)的字符集，然後再存储或者查询。

　　2、返回请求。

　　1)服务器将存储区域(table，column)的字符集转换成服务器连接字符集(character_set_connection)。

　　2)将服务器连接字符集(character_set_connection)转换成结果字符集(character_set_results)，再发送到客户端。

　　例如，我建立一个字符集为 gbk 的数据库(服务器端)。(MySQL 4.1 开始，在建立数据库时要指定它的字符集和校对规则，不指定就用默认的字符集和校对规则。)

　　连接数据库的程序(客户端)使用 gb2312 字符集(如 windows 命令行下使用 MySQL ，或者 PHP 连接MySQL )，那么在执行 insert 命令时，insert 的字符串将做一个 gb2312 到 gbk 的转换。而 select 时，数据库中保存的数据会先经过 gbk 到 gb2312 的转换之后再给你(结果集)。

　　好，那么为什么升级3.23(或4.0)到4.1时会乱码?举个例子说明。

　　例如3.23的数据库中保存的是gbk编码的数据。升级之前我将这些数据导出保存到文件里，这个文件的编码当然也是gbk的(因为3.23不支持多语言，不会对数据进行转换，也就是前面说的“原封不动地保存，原封不动地读出”)。

　　然后我在4.1中建立一个数据库，字符集为A;客户端字符集为B。将刚才的gbk数据导入。

　　1)A=gbk，B=gbk

　　导入数据时数据不会被转换;读出时需要set names gbk(set name命令下面将讲解)。

　　2)A=latin1，B=gbk

　　导入数据会进行gbk->latin1的转换，可能会丢失数据，产生乱码。

　　3)A=gbk，B=latin1

　　导入数据会进行latin1->gbk转换，可能会产生乱码。

　　4)A=latin1，B=latin1

　　导入数据时不会进行转换;读出时不需要set names gbk 。

　　大家可以看到，上面1)、4)才是正确的做法，即让A和B使用同样的字符集才不会乱码。

　　三、解决方案

　　了解了 MySQL 4.1.x 以上版本字符集处理的过程，我们就知道了怎么从原理上解决这个问题。

　　思路：让服务器端和客户端的字符集保持一致。

　　服务器端的编码是由字符集(Character Set)和校对规则(Collation)决定的。

　　上面提到，MySQL 中是根据下面几个变量确定服务器端和客户端用的什么字符集：

　　character_set_client　　　　 客户端字符集

　　character_set_connection　　 客户端与服务器端连接采用的字符集

　　character_set_results　　　　　SELECT查询返回数据的字符集

　　character_set_database　　　　数据库采用的字符集

　　也就是说，只要保证这几个变量采用一致的字符集，就不会出现乱码问题了。

　　查看系统的字符集用下面的命令：

　　1 mysql> SHOW VARIABLES LIKE 'character_set_%';

　　2 +--------------------------+-----------------------------------------+

　　3 | Variable_name | Value |

　　4 +--------------------------+-----------------------------------------+

　　5 | character_set_client | utf8 |

　　6 | character_set_connection | utf8 |

　　7 | character_set_database | utf8 |

　　8 | character_set_filesystem | binary |

　　9 | character_set_results | utf8 |

　　10 | character_set_server | utf8 |

　　11 | character_set_system | utf8 |

　　12 | character_sets_dir | E:\usr\MySQL Server 5.0\share\charsets\ |

　　13 +--------------------------+-----------------------------------------+

　　14 8 rows in set (0.00 sec)

　　15

　　可以看到，我的这几个变量都是一致的。但如果不一致呢?网上许多教程告诉你“你set names下就解决了”。

　　那么set names是什么呢? set names实际上就是同时设置了 character_set_client ，character_set_connection和 character_set_results 这三个系统变量。

　　例如在mysql命令行上输入 set names 'gbk' 命令等同于：

　　SET character_set_client = gbk;

　　SET character_set_connection = gbk;

　　SET character_set_results = gbk;

　　很多情况下，这样设置了之后就能把乱码问题解决了。但是还是不能完全避免出现乱码的可能，为什么呢?

　　因为character_set_client ，character_set_connection 这两个变量仅用于保证与 character_set_database 编码的一致，而 character_set_results 则用于保证 SELECT 返回的结果与程序的编码一致。

　　例如，你的数据库(character_set_database)用的是 utf8 的字符集，那么你就要保证 character_set_client ，character_set_connection 也是utf8的字符集。

　　而你的程序也许采用的并不是utf8 ，比如你的程序用的是gbk ，那么你若把 character_set_results 也设置为 utf8 的话就会出现乱码问题。此时你应该把 character_set_results 设置为gbk。这样就能保证数据库返回的结果与你的程序的编码一致。

　　到此应该就可以解决绝大多数我们遇到的乱码问题了，另外还必须强调的是，有时候乱码的出现有可能是以上几种原因混合造成的。

　　总而言之，我们应当尽量的保证数据库中的数据是正确的，就是客户端到服务器端或者服务器端到客户端转换的过程中不要产生乱码，那么问题处理起来就相对简单了。

　　四、总结

　　为便于大家记忆，总结为以下四点：

　　1、要保证发送的数据与数据库的字符集一致，即 character_set_client，character_set_connection 与character_set_database 一致。

　　2、要保证数据库中存储的数据与数据库编码一致，即数据的编码与character_set_database一致。

　　3、要保证 SELECT 的返回与程序的编码一致，即 character_set_results 与程序(PHP、Java等)编码一致。

　　4、要保证程序编码与浏览器编码一致，即程序编码与 一致。

　　附：

　　1、MySQL服务器能够支持多种字符集。可以使用SHOW CHARACTER SET语句列出可用的字符集：

　　mysql> show character set;

　　+----------+-----------------------------+---------------------+--------+

　　| Charset | Description | Default collation | Maxlen |

　　+----------+-----------------------------+---------------------+--------+

　　| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |

　　| dec8 | DEC West European | dec8_swedish_ci | 1 |

　　| cp850 | DOS West European | cp850_general_ci | 1 |

　　| hp8 | HP West European | hp8_english_ci | 1 |

　　| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |

　　| latin1 | cp1252 West European | latin1_swedish_ci | 1 |

　　| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |

　　| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |

　　| ascii | US ASCII | ascii_general_ci | 1 |

　　| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |

　　| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |

　　| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |

　　| tis620 | TIS620 Thai | tis620_thai_ci | 1 |

　　| euckr | EUC-KR Korean | euckr_korean_ci | 2 |

　　| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |

　　| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |

　　| greek | ISO 8859-7 Greek | greek_general_ci | 1 |

　　| cp1250 | Windows Central European | cp1250_general_ci | 1 |

　　| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |

　　| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |

　　| armscii8 | armSCII-8 armenian | armscii8_general_ci | 1 |

　　| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

　　| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |

　　| cp866 | DOS Russian | cp866_general_ci | 1 |

　　| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |

　　| macce | Mac Central European | macce_general_ci | 1 |

　　| macroman | Mac West European | macroman_general_ci | 1 |

　　| cp852 | DOS Central European | cp852_general_ci | 1 |

　　| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |

　　| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |

　　| cp1256 | Windows Arabic | cp1256_general_ci | 1 |

　　| cp1257 | Windows Baltic | cp1257_general_ci | 1 |

　　| binary | Binary pseudo charset | binary | 1 |

　　| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |

　　| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |

　　| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |

　　+----------+-----------------------------+---------------------+--------+

　　36 rows in set (0.00 sec)