mysql字符集之ems,navicat,PEAR::MDB2相关

关于mysql字符乱码早就有标准的解决办法，数据库使用UTF8编码，页面也使用UTF8编码，然后服务器端先作一次 'SET NAMES UTF8' 的查询就万事大吉。这次做一个项目对相关问题又有了深入一点的了解。

系统为 Centos4.7，PHP 4.3.9， MYSQL 4.1

 

一、关于mysql数据库编码设定有两种常见办法

1. 建立数据时采用默认的Latin1编码，然后配置全部采用默认。这样写库是什么编码取出来就是什么编码，就是说如果页面是GB2312编码那么写入就是经过Latin1编码“pack”过的GB编码，取出来之后也是GB2312编码。这种类似RAW写库的方式不会产生乱码，不过缺点也不少。比如在导入导出时可能会乱码，缺乏兼容性如果数据库升级很可能乱码，貌似在搜索中文时会有兼容问题。
2. 数据库，前后台全部采用UTF-8编码。虽然三字节编码相对浪费空间及流量但是从此大大减小不兼容性。早点时候会出现乱码问题，现在大家都知道一条sql命令就可以解决。

先转个网上搜索的

引用

此时，要是我们通过采用UTF-8的PHP程序从数据库里读取数据，很有可能是一串“?????”或者是其他乱码。网上查了半天，解决办法倒是简单，在连接数据库之后，读取数据之前，先执行一项查询“SET NAMES UTF8”，即在PHP里
mysql_query("SET NAMES UTF8");

即可显示正常（只要数据库里信息的字符正常）。为什么会这样？这句查询“SET NAMES UTF8”到底是什么作用？
到 MySQL命令行输入“SET NAMES UTF8;”，然后执行“show variebles like“character_set_%”;”，发现原来为latin1的那些变量“character_set_client”、 “character_set_connection”、“character_set_results”的值全部变为utf8了，原来是这3个变量在捣蛋。查阅手册，上面那句等于：

SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;


看看这3个变量的作用：
信息输入路径：client→connection→server；
信息输出路径：server→connection→results。
换句话说，每个路径要经过3次改变字符集编码。以出现乱码的输出为例，server里utf8的数据，传入connection转为latin1，传入 results转为latin1，utf-8页面又把results转过来。如果两种字符集不兼容，比如latin1和utf8，转化过程就为不可逆的，破坏性的。所以就转不回来了。
但这里要声明一点，“SET NAMES UTF8”作用只是临时的，MySQL重启后就恢复默认了。
接下来就说到MySQL在服务器上的配置问题了。岂不是我们每次对数据库读写都得加上“SET NAMESUTF8”，以保证数据传输的编码一致？能不能通过配置MySQL来达到那三个变量默认就为我们要想的字符集 ？手册上没说，我在网上也没找到答案。所以，从服务器配置的角度而言，是没办法省略掉那行代码的。

 

先回答上面引用中末尾黑体字的问题： 在服务器端配置只能对控制台命令行登录起效果，而对PHP（应该也包括JAVA/ASP）连接无效 。

至于搜索到的修改mysql配置方法也存在问题

引用

修改默认字符集
(1) 最简单的修改方法，就是修改mysql的my.ini(linux系统为my.cnf)文件中的字符集键值，
如
在[client]下面加上
default-character-set = utf8
在[mysqld]下面加上
character_set_server = utf8
default-character-set = utf8
character-set-server = utf8
collation-server = latin1_swedish_ci
init_connect = 'SET collation_connection = utf8_general_ci'
init_connect = 'SET NAMES utf8'
修改完后，重启mysql的服务，service mysql restart

 

我只要在my.cnf中[mysqld]小节里面添加上面“character_set_server ”的设置就会导致重启服务失败，经过测试可行的配置如下：

[mysql]
default-character-set=utf8
[client]
default-character-set=utf8

 如果同时出现[mysql][client]对于default-character-set的定义，以最后出现的定义为准。
并且此字符集定义只影响控制台登录，sql管理软件（如ems/navicat）不受影响需要自己设定
这个可以这样验证：在[mysql]中随便输入一个错误定义，然后service mysqld restart时并不会报错，而是
在 #mysql -p 时报错，比如：

mysql: unknown variable 'init_connect=SET collation_connection = utf8_bin'

注意如果是[client]中定义错误则服务将无法启动。

 

如此配置可以对控制台登录（#mysql -p）到mysql命令行有效，所以运行 show variables like 'character%'; 这条命令可以得到如下信息：

 

mysql> show variables like  'collation_%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |	-> 由set name utf8决定
| collation_database   | latin1_swedish_ci |	-> 数据库自身属性
| collation_server     | latin1_swedish_ci |	-> mysql系统默认定义
+----------------------+-------------------+

 

对于collation_database  字段若没有使用use命令选择数据库那么其值取自下一项（collation_server ）。

 

 

二、上面的设置只针对控制台登录，如果使用GUI软件登录那么依据软件自身对字符集的设定

1. EMS MySQL Manager 2005 v3版本不支持UTF-8编码，支持GB/GBK，2007 v4版本支持UTF-8。所以如果是采用Lation1建库RAW写库的那么v3版本使用默认数据库字符集属性就能正常显示中文，v4新版得采用windows charset，中文版系统默认为GBK，若DB为GB2312也能显示出中文 。如果采用UTF-8编码建库只有在v4新版本下中文才不乱码。
   
   
    
   
   
2. navicat的设定比较怪。整个服务器采用统一编码设定，如果其上的数据库采用不同的编码格式那么相互之间就会乱码。在图navicat1中设定为“使用mysql字符集”就相当于执行了一次“SET NAMES XXX”，这儿XXX似乎默认等于UTF-8，如果执行一次“show variables like 'character%';” 查询得到图navicat2。
   
   
   
    
   
    
   
   注意“character_set_database”值为latin1，说明这是latin1的库。因为其保存的是GB内容所以查看是乱码。于是如图navicat3修改设置，于是中文显示正常
   
   
   
   
    
    

 

 

三、项目中使用了PEAR::MDB2作为数据库连接方式，数据库为UTF-8编码。测试中发现前台页面（UTF-8）中文有些是乱码的，于是使用ems v4查看，同一个表中有不乱码的有正常的。使用navicat查看，分别采用图navicat1的设置以及图navicat4的设置，乱码情况正好相反——换个设置不乱码的正常而正常会乱码。


 

 

 

 

 

 

思考半天后来发现是MDB2连接时忘了使用“SET NAMES UTF8”命令查询。因为数据库中存在原始数据所以和新插入数据的编码实则是不一样——前者是以UTF-8编码保存在UTF-8的数据库表中；后者是以被Latin1打包过的UTF-8数据保存在UTF-8数据库中。在ems v4中无论客户端是设定默认/utf8它都使用如下设定

SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;

 而navicat8中如果指定编码格式则以上三项都是采用 Latin1编码。。。其实就和ems v3采用的同样策略。

 

知道了问题自然就知道了怎么解决。不过看了PEAR手册发现可以在定义dsn连接时就可以直接指定连接编码方式：

$dsn = array(
  'phptype'=>DB_DATABASE, 
  'username'=>DB_USER, 
  'password'=>DB_PW,
  'hostspec'=>DB_HOST, 
  'database'=>DB_NAME, 
  'charset'=>'utf8');

 

有了最后那个值就相当于执行了一次 "SET NAMES UTF8" 命令。

 

总而言之：

1. 尽量使用UTF-8作为统一编码，当前后台数据库之间数据流通时就无需特别转码也无需考虑乱码，AJAX也无需考虑对中文打包转码问题。
2. 后台在初始化数据库连接之后需要做一次查询，内容为“SET NAMES UTF8”，以保证mysql使用UTF-8作为传输路径三个部分的统一编码而不是默认的Latin1。否则你建立UTF-8的数据库但保存的内容却是Latin1编码格式（mysql自动在UTF-8外面包括了一层Latin1） 。

 

 

 

 

 

 

 

评论

2 楼 waiting 2012-06-15  

a6230589 写道

我也是修改了mysql的配置文件后发现编码设置对控制台登录有用，但是对php却没用，谢谢兄弟了。

只想说mysql有够垃圾问题太多，现在能不用就不用。

1 楼 a6230589 2012-06-15  

我也是修改了mysql的配置文件后发现编码设置对控制台登录有用，但是对php却没用，谢谢兄弟了。