引言
定位:查询数据库得到的结果集乱码
,client端向数据库插入数据乱码
。
网上有不少帖子,手把手地教给我们如何去改这一问题。方案大多数如下:
方案中最多介绍的就是更改配置文件,win下
my.ini
、Linux下my.cnf
# Win下 my.ini 有的默认被注释掉,只需要去掉注释就可以
#在[client]下追加:
default-character-set=utf8
#在[mysqld]下追加:
character-set-server=utf8
#在[mysql]下追加:
default-character-set=utf8
# Linux下,这里就有所不同,每个人当初安装MySQL的方式,添加的my.cnf
#是否是用的官网模板还是网上复制的内容填充的,但是方式要添加的内容和win
#大同小异,如果当初指定了相应的默认字符集就无需指定字符集。
#【注】无论是my.ini还是my.cnf里面的mysql相关的配置项一定要在所属的组下面,
比如default-character-set就只能放在[mysql]/[client],不能放在[mysqld]下,
不然会导致mysql服务启动失败,可能如下:
#[Error]start Starting MySQL .. The server quit without updating PID file
# 所以说mysql服务起不来了,可能是配置文件出现了问题其实最关键的一项是
[mysqld] character-set-server=utf8
,其它两项,对于my.cnf
只需要追加[mysql] character-set-server=utf8
就可以改变character_set_client
、character_set_connection
、character_set_results
这三项的值,这三项代表是什么意思,别急,后面会有介绍。如果是利用JDBC连接mysql时出现的乱码,大致的步骤会有:
更改上面所讲的配置文件、更改数据库编码、更改表编码、添加在连接数据库的url地址上添加后缀
?useUnicode=true&characterEncoding=xxx
等等,有的同学一顿猛改,挨个尝试,最终还是乱码还是没有解决,然而有的同学运气就比较好,改完数据库编码乱码问题就解决了。但是回过头来想想是哪个地方改好的,为什么这样改就OK,就有点摸不着头脑了,下次遇到同样的…所以有时候抓住事物的根源就能举一反三。
character_set_client/connection/results变量
首先附一张图,帮助下面更好地理解。
1. 三个变量的含义及作用
- character_set_client:客户端发送的sql语句编码字符集。它的作用就是告诉mysql服务器,本地客户端采用了什么编码环境。
insert into t values('吴');
,服务器知道这条sql是以character_set_client指定的字符集所编码的,'吴'将被转化为指定的编码格式,比如它的utf8编码为E590B4
,最终以0101…发送到server。 - character_set_connection:服务器将接收到的character_set_client指定编码的sql语句翻译成character_set_connection指定编码的sql。也有不少帖子谈到该变量是否有用,为什么要设置这个中间变量,直接把sql执行入库,为什么还要转化,在这里不争论这一个。
- character_set_results:指示了服务器将查询结果返回给客户端的字符集。服务器检索库表之后,需根据该变量的值将查询结果的字符集转化为character_set_results所指定的字符集。
关于这三个变量,MySQL在官网也提到:
【注】*当character_set_client、character_set_connection
值相同时,也不会发生编解码。
2. 乱码课代表 "?"、火星文"å ½…"
打开任意客户终端,连接mysql
,之后执行show variables like 'character_set%'
,就可以查看这三个变量的值,乱码问题最大程度上是由这三个变量和数据库字符集的配置,乱码的根源就是编解码操作。下面我展示了四个client终端在执行完命令之后的截图。【我这里的MySQL服务器是安装在ALi
的ECS
服务器上,这里三个终端所连接的都是同一个mysql
服务器】
Linux:
win->cmd
命令窗口远程连接Linux服务器上的MySQL
:
Navicate终端下连接:
IDEA下利用JDBC
连接数据库后查询到的结果,注意,为了试验,url
后面不要跟具体的数据库名,只要mysql
的地址和端口就可以,因为variables有global
和session
两个范围,这里采用默认查询session级别。
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.0.1:3306", "***", "***");
PreparedStatement ps = connection.prepareStatement(" show variables like 'character_set%';");
ps.execute();
ResultSet resultSet = ps.executeQuery();
while (resultSet.next()) {
//| Variable_name | Value
System.out.println(resultSet.getString("Variable_name"));
System.out.println(resultSet.getString("Value"));
}
/*
输出
character_set_client
latin1
character_set_connection
latin1
character_set_database
latin1
character_set_filesystem
binary
character_set_results
character_set_server
latin1
character_set_system
utf8
character_sets_dir
/usr/local/mysql/share/charsets/
*/
}
请仔细观察这三个变量:
character_set_client
、character_set_connection
、character_set_results
这三个变量在不同的终端可能有不一样的值,比如dos下和Linux下。但是每一个终端下这三个变量的值默认情况下是相同的,dos下是三个变量值全为gbk
,Linux下全为utf8
。
1、乱码复现,从最小的latin1字符集说起
latin1编码(字符集),所占空间大小1字节,编码范围0~255,使用了ASCII码的高位1,在ASCII基础上扩展了128个字符,扩展的128个字符,大致看了一下都是火星文,看不懂。尽管一个utf8的字符映射到latin1上会出现乱码,但这不能否定latin1就不能存放utf8的数据,或者说用latin1存放utf8的字符查询后肯定会乱码,这不是绝对的,下面会有示例。
# 为了看出效果,将三个变量值统一
mysql> set names "latin1";
mysql> show variables like 'character_set%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
# 创建数据库,默认字符集为latin1,继承了server的字符集
mysql> create database db;
# 查看数据库结构
mysql> show create database db;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| db | CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------+
# 选中新建的库
mysql> use db;
mysql> create table t(t varchar(30));
#表的字符集默认继承自db,db又继承server
mysql> show create table t;
+-------+------------+
| Table | Create Table
+-------+--------------+
| t | CREATE TABLE `t` (
`t` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1|
+-------+----------+
# 查看表字段字符集
mysql> show full columns from t\G;
*************************** 1. row ***************************
Field: t
Type: varchar(30)
Collation: latin1_swedish_ci
Null: YES
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
# 插入一条英文和中文信息
mysql> insert into t values('wu'),('吴');
mysql> select *,hex(t),length(t) from t;
+------+--------+-----------+
| t | hex(t) | length(t) |
+------+--------+-----------+
| wu | 7775 | 2 |
| 吴 | E590B4 | 3 |
+------+--------+-----------+
数据库表字段字符集为latin1,character_client/connection/results这三个变量的值也为latin1,一个没有中文字符的latin1字符集竟然能存储中文并且还能把数据完好无损的展示出来,其实数据底层编码没有受损,如果数据编码过程中受损,我们看到的就不是这样的了,况且上面hex(t)字符编码也显示了数据并没有受损。我们看下流程:
[1] [2] [3] [4]
shell--->吴-utf8->"E590B4"--->client(latin1)--->conn(latin1)--->DB
# insert执行过程:
# 客户端发出插入字符的命令,server检查character_set_client,知道客户端使用latin1作为编码集,
其实我们欺骗了server服务器,因为在没有执行set names "latin1"之前,character_set_client是utf8
即是shell本地环境下的字符编码。server检查character_set_conn,和set_client相同,[1]->[2]过程
传递的"E590B4"不会发生编码解码转化等问题,[3]->[4]属于数据库内部操作了,检查conn是否和数
据库编码集相同,这里是相同的,按照latin1格式,以字节流写入硬盘.
# 分析:【输入】
# shell通过set names "latin1"欺骗了server,让server以为我们本地的编码是latin1格式,本来
字符"吴"在shell中是以utf8格式的单字符3字节中文存在,在server中是认为是以latin1编码的
字符(转化为latin1是这个样子"å ´") ,3字符3字节的形式存在,因为在[2]->[3]->[4]中流转,
没有发生编码转换,也没有字符损失,只不过server中认为的"E590B4"和shell中认为的"E590B4"
语义不同,latin1最终还是能存储下这一串字符编码.
[5] [6] [7]
DB-->results(latin1)-->"E590B4"-->utf8-->shell-->“吴”
# 分析:【输出】
# [5]->[6]数据库内部没有发生有损化编码,成功的将“E590B4”带到shell之前,因为shell编
码是utf8,不要忽略shell格式的编码,正是因为它,才将格式复原。因为results当初设置
的是latin1,最终是以latin1格式返回,这时shell就认为这是一个utf8编码的字符,它有被欺
骗了,就会按照utf8格式来划分字符,“E590B4”又被当做3字节一字符进行了utf8编码,
最终复原.
在终端中执行set names 'utf8';
;将编码格式改为shell默认的本地化参数,然后执行查询,乱码出现
mysql> set names "utf8";
# 乱码出现
mysql> select *,hex(t),length(t) from t;
+--------+--------+-----------+
| t | hex(t) | length(t) |
+--------+--------+-----------+
| wu | 7775 | 2 |
| å´ | E590B4 | 3 |
+--------+--------+-----------+
出现乱码,肯定是编解码出现了错误,出现编解码错误的过程发生在了[5]->[6],这时的[5]->[6]不在是上面的了
[5] [6] [7]
DB(latin1)-->results(utf8)-->"E590B4"-->utf8-->shell-->“吴”
# DB内部到character_set_results编解码的过程发生了错误,也不能说是错误吧,因为[5]->[6]
就是正常的latin1->utf8格式的转化,utf8的字符集包含latin1,latin1的字符都能在utf8中找到
相应的字符,“E590B4"latin1编码表示"å ´"(三字符),(utf8>latin1)无损转化,转为utf8也是"å ´"(三字符),
这里语义就发生了变化,不在是utf8当初认为的单字符3字节了,显示在shell中就是火星文了
接下来又会看到另一个熟悉的朋友(?)。
在上述操作的基础上,client、conn、results都为utf8,数据库表字段为latin1的格式下,插入一条数据
mysql> insert into t values('吴');
Query OK, 1 row affected, 1 warning (0.01 sec)
# mysql这时候给出提示如下,说明字符在编码的时候遇到了冲突
mysql> show warnings;
+---------+------+----------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xE5\x90\xB4' for column 't' at row 1 |
+---------+------+----------------------------------------------------------------+
# 熟悉的老盆友“?”出现
mysql> select *,hex(t),length(t) from t;
+--------+--------+-----------+
| t | hex(t) | length(t) |
+--------+--------+-----------+
| wu | 7775 | 2 |
| å´ | E590B4 | 3 |
| ? | 3F | 1 |
+--------+--------+-----------+
3 rows in set (0.00 sec)
插入数据结果为"?"乱码,warnings警告也有一个提示,上述过程的编解码错误出现在向DB中转换存数据的过程中
[1] [2] [3] [4]
shell--->吴-utf8->"E590B4"--->client(utf8)--->conn(utf8)--->DB(latin1)
# [3]->[4]server内部字符集编解码,将数据整合到库中
# utf8->laint1 [我的猜想,如有错误,请不吝指出]
# 大的方面想,在latin1中找不到一个字符为"吴"且值为"E590B4"的字符,并且utf8是
单字符3字节,这3字节单字符的范围是latin1所不能解析的,因为latin1只能处理单字
符1字节,所以会有 Incorrect string value: '\xE5\x90\xB4' for column 't' at row 1 提示。
latin1不知道插入的是什么字符,最终会以问号代替所插入的字符。无论改变set names "latin1"还
是其他字符集查询,最终结果不变,"?"是挥之不去的了,因为他是以最小
字符集latin1格式保存在了库中,他就是一个"?"。
接着,改变character_set_client=latin1;
,插入数据看效果。
mysql> set character_set_client=latin1;
mysql> show variables like 'character_set%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | latin1 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)
mysql> insert into t values('吴');
Query OK, 1 row affected (0.01 sec)
# 出现了和当初在latin1的环境下,插入数据之后查询显示无乱码,
后来set names "utf8";之后查询的乱码一样,对应表中第二条记录
mysql> select *,hex(t),length(t) from t;
+--------+--------+-----------+
| t | hex(t) | length(t) |
+--------+--------+-----------+
| wu | 7775 | 2 |
| å´ | E590B4 | 3 |[1]
| ? | 3F | 1 |
| å´ | E590B4 | 3 |[2]
+--------+--------+-----------+
这次出现乱码的错误转化逻辑发生在[2]->[3]处,其实之前说的编解码错误,并不能以偏概全,像出现Incorrect说明真正的出现了编解码错误,其他的插入数据并没有warnings,就不能这样说了,其实它编解码本来无误,只不过语义变化了,本来是单字符3字节,却被按照单字符单字节解析,底层数据没有遭到破坏,只是再次重组底层编码的时候就不一样了。
[1] [2] [3] [4]
shell--->吴-utf8->"E590B4"--->client(latin1)--->conn(utf8)--->DB(latin1)
# latin1->utf8 [2] -> [3]
# set character_set_results=latin1欺骗服务器说自己的本地编码为latin1,
E590B4就会按照latin1编码解析,latin1->utf8是无损转化的,单字节单字符
就一定会在utf8中找到一个相应的字符,这是mysql服务器内部帮我们做的,
mysql服务器所做,mysql服务器所做,[3]->[4]过程,mysql再次对字符进
行编解码,因为这时候的utf8实际就是latin1的值 "å ´",这时候就能够找到
在latin1中对应的字符,查询输出的也就是看到的"å ´"
但是只要执行一句set character_set_results=latin1;
[1]、[2]就会显示原中文字符。字符又现行了。
这个过程就是对应的[5]、[6]、[7],注意语义,在[7]之前“E590B4”代表的是"å ´",并不是“吴”,在[7]后,shell自动将"E590B4"重组的,这不是mysql所做。将远程连接工具 SecureCRT会话选项的字符集改为"ISO-8859-1(拉丁语)"之后,就会看到本来的"å ´",如下图。
[5] [6] [7]
DB(latin1)-->results(utf8)-->"E590B4"-->utf8-->shell-->“吴”
next,set names 'utf8'
回到查询乱码态,接下来我们让conn为latin1,插入数据看结果
mysql> set character_set_connection=latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values('吴');
Query OK, 1 row affected, 1 warning (0.01 sec)
# 同样插入之后出现了警告,这时候编码肯定受损
mysql> select *,hex(t),length(t) from t;
+--------+--------+-----------+
| t | hex(t) | length(t) |
+--------+--------+-----------+
| wu | 7775 | 2 |
| å´ | E590B4 | 3 |
| ? | 3F | 1 |[3]
| å´ | E590B4 | 3 |
| ? | 3F | 1 |[4]
+--------+--------+-----------+
这次插入出现的错误在过程[2]->[3],看到这,应该也能分析出来个大概了吧,举一反三了吧
[1] [2] [3] [4]
shell--->吴-utf8->"E590B4"--->client(utf8)--->conn(latin1)--->DB(latin1)
# [2]->[3]mysql服务器内部转化,没有在latin1中找到[2]utf8格式的相应
字符,给出warning,sql_mode为严格模式下是给予错误提示.
3. JDBC的特殊处
为什么jdbc连接mysql的url后面加useUnicode=true&characterEncoding=utf8
可以解决乱码呢?
client在与server连接时,MySQL会自动检测客户与服务器之间的编码集,如果客户端没有指定characterEncoding
属性,那么client与服务器之间的连接就会使用character_set_server
的值来初始化之前client_conn_results三个变量的值。如果指定了,则用characterEncoding
初始化。
在shell不改动默认编码(utf-8)下,数据库表字段字符集为latin1,character_client/connection/results这三个变量的值也为latin1时,查询到的结果是没有乱码的,最终会被shell正常解析。JDBC又有所不同了,插入查询都是乱码,结果为什么没有被idea所矫正?之前shell就能够自动重组字符,idea难道是个智障?
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://***/db1", "***", "***");
PreparedStatement ps = connection.prepareStatement(" select * from t");
ResultSet resultSet = ps.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getString("t"));
}
}
/* 输出
wu
�
?
�
?
*/
其实这与idea无关,字符编解码的事情是由本地的JDBC来做的,JDBC在本地完成解析后,idea才显示的
# shell
DB(latin1)-->results(latin1)--"E590B4"-->shell按照utf8格式解析
# jdbc
DB(latin1)-->results(latin1)--"E590B4"-->jdbc按照latin1格式解析完毕[1]--->idea显
示字符串,jdbc是在本地客户端解析的,所以说idea就不会像shell那样,强制重组,
jdbc在本地帮我们做好了,它只会将结果以latin1->utf8字符映射的方式显示。
# 在插入的时候也会提前将字符串以指定字符集编码化
尝试向数据库中插入一条记录,查询插入的结果是一个问号“?”,说明了在本地,
jdbc在将字符串转到latin1的时候就发生了编码错误,latin1中找不到utf8指定的字符,[1]->[2]编码损失。
[1] [2]
idea(utf8)->jdbc(latin1)->client(latin1)->conn(latin1)->DB
# 总:jdbc编解码的过程有一部分是在本地完成的
总结
1、一般为了避免乱码,应该将数据库中的字符集设置为utf8,因为utf8是unicode的中编码方式,unicode字符集包含世界上所有的字符,通过utf8可以实现gbk<->utf8字符的映射,当然不是所有的都能映射成功,只有那些我们连见都没见的古文或者其他不常见的字可能映射失败。
2、mysql的乱码情况主要有,底层数据编码没有受损,显示乱码,这种一般是在编解码的过程中语义发生了变化所导致的;另一种是编解码已经受损,在shell插入数据的时候受损后会有一个waring警告。常见的就是"?",这种情况,无论设置何种字符集,都会显示"?"。区分编解码语义的变化,根据mysql内部进行编解码还是外部进行编解码来判断,内部编解码主要是找相应的字符映射,外部则是重组,另外,JDBC有一部分编解码在本地完成。
本文参考