mysql编码乱码问题的理解

最近遇到mysql著名的编码问题，经过一番追究和官方文档帮助，终于解决，并总结出些经验:）

一．原理

影响mysql乱码的问题的根本原因包含字符集和校验规则

mysql编码和校验规则分为四个级别:服务器级、数据库级、表级和连接级

这四个级别的编码和校验规则都有两个值：默认值和用户设置值

服务器级：默认值在my.cnf中确定，这是由编译安装时配置定下来的

         设置值是在运行mysql时通过 --default-character-set修改

数据库：默认值继承自服务器级 设置值在create database中指定

表级：默认值继承自数据库级，设置中在create table中指定

连接级:默认值由配置文件[mysql]节点配置值决定，设置在set names（set character_set_connection  x）中指定.

mysql的查询或者更新或者插入过程如下：

1.客户段用character_set_client的字符集发送sql

2.服务器接收到sql语句后用character_set_connection的编码转换sql语句，并执行

3.将结果以character_set_results的编码返回给客户端

因此如果用set names xxxz将这三个编码统一到xxxx,基本上可以避免大部分的乱码问题.

（注意：用 show variables like 'chara%';可以看到如下变量：

mysql> show variables like 'chara%';
+--------------------------+-----------------------------------+
| Variable_name            | Value                             |
+--------------------------+-----------------------------------+
| character_set_client     | latin1                            |
| character_set_connection | latin1                            |
| character_set_database   | utf8                              |
| character_set_results    | latin1                            |
| character_set_server     | latin1                            |
| character_set_system     | utf8                              |
| character_sets_dir       | /data/mysql/share/mysql/charsets/ |
+--------------------------+-----------------------------------+

 

如果执行set names utf8;后，将会改变character_set_client,character_set_connection,character_set_results三个变量的值：

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'chara%';
+--------------------------+-----------------------------------+
| Variable_name            | Value                             |
+--------------------------+-----------------------------------+
| character_set_client     | utf8                              |
| character_set_connection | utf8                              |
| character_set_database   | utf8                              |
| character_set_results    | utf8                              |
| character_set_server     | latin1                            |
| character_set_system     | utf8                              |
| character_sets_dir       | /data/mysql/share/mysql/charsets/ |
+--------------------------+-----------------------------------+

 

）

二．遇到的问题

A Database Error Occurred

Error Number: 1267

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'

select * from HostBase where ServiceArr like '%QQ幻想%'

这是一个查询报错，由mysql的api报出.查询前set names utf8了。

为啥会出现这个问题呢？从报错来看，可以知道默认的校验规则latin1_swedish_ci和设置的规则utf8_general_ci发生冲突。从sql语句来说，问题肯定出现在中文的"幻想"的校验规则上，因为英文字母无论是latin1和utf8都是一样。这个数据库表的编码是latin1,而ServiceArr没指定编码，因此继承表的编码latin1，因此校验规则必然是latin1_swedish_ci，对于sql语句中的'幻想'，mysql有如下的规定，如果字符串没指定编码，那么编码继承自character_set_connectio，也就是utf8，由校验规则的优先级原理，ServiceArr like '%QQ幻想%'用的是ServiceArr的校验规则latin1_swedish_ci.一个字符集上的校验规则是不能用在其他字符集上的，因此发生以上错误。

三．解决办法

知道原理和问题产生的原因，解决起来就比较容易了

只需将sql转换为latin1，然后在set names latin1即可:)
四,总结

mysql的编码比较复杂，但对使用而言，只要保证如下两点可以解决大多数问题

1.我们传给mysql服务器的编码和从mysql服务器中取出来的编码是一致的

也就是set namse xxxx;

2.应用程序编码最好也和第一步中的xxxxx一致。

 (该问题的关键点就是执行set names latin1,让那三个变量和表的charset一样就不会乱码了)