关于ORACLE和MYSQL中文字符乱码的根源剖析-阿里云开发者社区

开发者社区> 重庆八怪> 正文

关于ORACLE和MYSQL中文字符乱码的根源剖析

简介: 关于数据库的字符集问题一直都是一个比较恶心的问题,如果不了解其实质可能一直 都搞不清楚这个问题的根源,只能出了问题去度娘,这里我打算兼容ORACLE和MYSQL对字符集 的处理来描述乱码出现的情形和如何防止乱码问题出现的可能,本文只用UTF-8和GBK为 例...
+关注继续查看

关于数据库的字符集问题一直都是一个比较恶心的问题,如果不了解其实质可能一直
都搞不清楚这个问题的根源,只能出了问题去度娘,这里我打算兼容ORACLE和MYSQL对字符集
的处理来描述乱码出现的情形和如何防止乱码问题出现的可能,本文只用UTF-8和GBK为
例子进行描述,请大家先记住'去'这个字的UTF8和GBK编码,因为整个文章将用'去'字为
例子进行讲述
GBK     UTF8   中文
C8A5    E58EBB  去
同时整篇文章数据库DATABASE端的字符集始终为UTF8
一般来讲我们所说的乱码一般来自于非英文字符,如中文,因为英文是ASCII中进行了定义的
所有的编码方式一致

首先我们从2个报错的例子来进行描述
ORACLE:
NLS_LANG设置NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
终端设置为UTF8
SQL> create table testchar(name varchar2(20));
Table created.
SQL> insert into testchar values('去');
ERROR:
ORA-01756: quoted string not properly terminated

MYSQL:
character_set_client       gbk
character_set_connection   gbk 
character_set_results      gbk
终端设置为UTF8
mysql> create table testchar(name varchar(20));
Query OK, 0 rows affected (0.25 sec)
mysql> insert into testchar values('去');
ERROR 1406 (22001): Data too long for column 'name' at row 1
终端设置为UTF8

咋一看这两个报错不知所云啊,明明我给的varchar是20,ORACLE直接
报错单引号没有正确结束,MYSQL更狠直接报超长。
其实这就是字符集引起的错误。我会慢慢讲述

首先解释有关的字符集:
1、DATABASE字符集   数据库字符集就是你期望的使用存储数据的字符集,但是这里说只是期望的,比如我的数据库是 
                    UTF8,是不是只能存储UTF8的字符呢?当然答案是否定的,他可以存储任何字符集的字符,其实
                    字符存储在文件中就是一堆二进制0和1,要看你的终端怎么解释他们了。比如如果文件中存储是
                    二进制E58EBB,那么你用UF8的终端字符集去解释他就是'去'字,用GBK去解释就是乱码。
                    ORALCE中可以查看nls_database_parameters 中的NLS_CHARACTERSET查看数据库的字符集
                    MYSQL中可以查看character_set_database参数查看数据库的字符集
2、CLIENT转换字符集 转换字符集用于让数据库服务端知道客户端过来的数据是什么字符集,如果过来的不是数据库的字符集
                    则进行转换,当然转换必须是兼容的字符集,如果CLIENT使用的是GBK而DATABASE字符集是UTF8那么进行
                    SQL解析的时候会进行转为相应的字符集GKB->UT8,也就是说CLIENT字符集代表是你告诉数据库你使用的什么字符集
                    然后数据库来决定是否需要转换。
                    ORACLE中使用NLS_LANG来设置 如NLS_LANG=AMERICAN_AMERICA.AL32UTF8
                    MYSQL中使用set names进行设置他实际改变了三个参数character_set_client,character_set_connection
                    和character_set_results
3、终端字符集       这里终端的字符集是你录入数据和显示数据的字符集,比如我用的securtCRT伪终端就是在
                    会话选项-->终端-->外观-->字符编码,当然如果不使用伪终端那么LINUX中使用的locale进行
                    查看终端的字符编码,export LC_ALL=en_US.gbk 可以更改所有的,具体可以自己看一下。
                    当然windows也有
                    
那么了解了3种字符集我们来简单描述一下:
如果我设置终端为GBK,CLIENT转换字符集为GBK,DATABASE字符集为UTF8,那么我们输入一个'去'字,并且SELECT出来的流程为

终端以GBK字符集编码录入数据为C8A5--->
数据来到了CLIENT比如SQL命令窗口编码为C8A5--->
回车后对SQL进行解析CLIENT的字符集为GBK和DATABASE UTF8不同进行转换为E58EBB--->
数据进入DATAFILE 存储格式为E58EBB--->
终端SQL命令窗口发起SELECT查看要求插叙数据'去'--->
DATABASE查询数据文件读取E58EBB返回给用户SESSION--->
用户SESSION发现CLIENT字符集为GBK进行转为C8A5--->
终端显示数据解析C8A5为GBK格式显示为'去'

整个过程大概就是这样,如果一个环节出现问题可能出现乱码,比如转换后为C8A5但是终端为UTF8格式
那么必然乱码

回到刚才的问题
mysql> insert into testchar values('去');
ERROR 1406 (22001): Data too long for column 'name' at row 1

SQL> insert into testchar values('去');
ERROR:
ORA-01756: quoted string not properly terminated

由于我的CLIENT字符集设置为GBK,而终端字符集设置为UTF8,数据库字符集为UTF8

那么我们录入的数据编码为E58EBB,然后进行解析由于CLIENT设置为GBK,DATABASE为UTF8,认为要
进行转换,那么叫E58EBB当做GBK进行转为UTF8。当然就出现了问题因为E58EBB压根不是GBK的编码
转出来肯定是乱码,那么我们如何纠正呢?
1、设置CLIENT字符集设置为UTF8
或者
2、设置终端字符集为GBK
都可以

接下来我们来验证我们的说法。
一、在ORACLE和MYSQL中设置终端字符集为GBK,设置CLIENT字符集为UTF8,DATABASE字符集为UT8
   那么这种情况下我们插入'去'字,那么终端是C8A5,而CLIENT字符集和DTABASE字符集相同
   不会进行转换,这样就吧GBK的字符编码数据存入了UTF8的字符集的库,接下来演示
1、ORACLE
SQL> insert into testchar values('去');
1 row created.
SQL> select dump(name,16) from testchar;

DUMP(NAME,16)
--------------------------------------------------------------------------------
Typ=1 Len=2: c8,a5

我们看到我们的UTF8字符集的库存储进了C8A5为GBK字符集数据。
当然查看也没有问题因为我们的终端字符集为GBK,它会自动进行转换
SQL> select * from testchar;
NAME
--------------------

如果我们设置终端字符集为UTF8那么就出现了乱码
SQL> r
  1* select * from testchar

NAME
--------------------
?
2、MYSQL

mysql> show variables like '%char%';
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| character_set_client     | utf8                        |
| character_set_connection | utf8                        |
| character_set_database   | utf8                        |
| character_set_filesystem | binary                      |
| character_set_results    | utf8                        |
| character_set_server     | utf8                        |
| character_set_system     | utf8                        |
| character_sets_dir       | /mysql/test/share/charsets/ |
+--------------------------+-----------------------------+
mysql> insert into testchar values('去');
Query OK, 1 row affected (0.00 sec)
直接查看数据文件的使用工具bcview(是我自己用C语言编写的),来查看实际的数据文件
testchar.ibd提取出其数据。
可以在百度云
http://pan.baidu.com/s/1num76RJ
下载到
[root@hadoop1 test]# bcview testchar.ibd 16 146 2;
******************************************************************
This Tool Is Uesed For Find The Data In Binary format(Hexadecimal)
Usage:./bcview file blocksize offset cnt-bytes!                   
file: Is Your File Will To Find Data!                             
blocksize: Is N kb Block.Eg: 8 Is 8 Kb Blocksize(Oracle)!         
                         Eg: 16 Is 16 Kb Blocksize(Innodb)!       
offset:Is Every Block Offset Your Want Start!                                     
cnt-bytes:Is After Offset,How Bytes Your Want Gets!                               
Edtor QQ:22389860!                                                
Used gcc version 4.1.2 20080704 (Red Hat 4.1.2-46)                
******************************************************************
----Current file size is :0.093750 Mb
----Current use set blockszie is 16 Kb
current block:00000000--Offset:00146--cnt bytes:02--data is:0002
current block:00000001--Offset:00146--cnt bytes:02--data is:0000
current block:00000002--Offset:00146--cnt bytes:02--data is:ffff
current block:00000003--Offset:00146--cnt bytes:02--data is:c8a5
current block:00000004--Offset:00146--cnt bytes:02--data is:0000
current block:00000005--Offset:00146--cnt bytes:02--data is:0000

这里块00000003就是这个表的第一个数据块关于如何使用BCVIEW和查看
数据参考我的博客
http://blog.itpub.net/7728585/viewspace-2071787/
可以看到数据为c8a5为GBK的字符集编码。
如果这个时候我们修改终端字符集为UTF8,也会出现乱码
mysql> select * from testchar;
+------+
| name |
+------+
| ?    |
+------+
1 row in set (0.00 sec)
这样一看ORACLE MYSQL都是一样的没有问题。乱码的出现在于GBK编码的字符集不能在
终端进行UTF8编码的解析。

二、那么如何将正确的UTF8 '去'字的编码E58EBB存入到数据库呢?其实前面已经给出了答案

将CLIENT字符集设置为GBK同时终端字符集设置为GBK
或者CLIENT字符集设置为UTF8同时终端设置为UTF8   

我们使用复杂一点的将CLIENT字符集设置为GBK同时终端字符集设置为GBK
测试
1、ORACLE
设置终端字符集为GBK同时
[oradba@hadoop1 ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
SQL> insert into testchar values('去');
1 row created.

SQL> commit;
Commit complete.

SQL> select dump(name,16) from testchar;
DUMP(NAME,16)
--------------------------------------------------------------------------------
Typ=1 Len=3: e5,8e,bb

可以看到e5,8e,bb为我们正确的UTF8字符集表面的'去'字

2、MYSQL
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| character_set_client     | gbk                         |
| character_set_connection | gbk                         |
| character_set_database   | utf8                        |
| character_set_filesystem | binary                      |
| character_set_results    | gbk                         |
| character_set_server     | utf8                        |
| character_set_system     | utf8                        |
| character_sets_dir       | /mysql/test/share/charsets/ |
+--------------------------+-----------------------------+
8 rows in set (0.01 sec)

[root@hadoop1 test]# bcview testchar.ibd 16 146 3;
current block:00000003--Offset:00146--cnt bytes:03--data is:e58ebb
可以看到e58ebb为UTF8的字符集编码了。
SQL> select * from testchar;

NAME
----------------------------------------
?

这个时候乱码是因为回显的时候 utf8 服务端字符集编码 e58ebb--> GBK CLIENT字符集转换c8a5-->utf8 终端解析乱码

那么也许大家要问如何最大限度的避免乱码的出现
方案为
1、设置终端字符集(或者确定.sql文件)为UTF8、CLIENT字符集为UTF8、database字符集为UTF8。
   这样不存在转换完全取决于你终端的输入的字符的编码,关于有时候我们要SOURCE SQL文件,
   那么如果我们设置终端字符集为UTF8然后more一下这个文件如果看到的中文没有问题,如果没有
   问题那么就能你能正常导入数据。
2、按照需求设置比如你文件或者终端是GBK编码,那么你必须设置CLIENT字符集为GBK,也就是说转换
   字符集修改为你终端输入或者文件本身的编码。这样才能正常的进行转化不会出现乱码。

同时在重点强调一下数据库database的字符集为UTF8并不表示只能存储UTF8的字符编码,这个已经在前面的
例子验证过。
   
最后测试一下WINDOWS 我们修改环境变量为
american_america.AL32UTF8
sqlplusw /nolog
终端字符集为
C:\Users\Administrator>chcp
活动代码页: 936
936是GBK编码

然后查看本来的'去'字
SQL> connect sys/gelc123@192.168.190.30:1521/test as sysdba
Connected.
SQL> select * from testchar;

NAME
--------------------
鍘?

发现出现问题乱码,为什么呢?因为WINDOWS终端本生的字符集编码为GBK,
而你去告诉ORACLE 不需要进行转换,那么正确的UTF8编码e5,8e,bb被原封不动
的传输给终端,终端用GBK进行解析当然GBK解析e5,8e,bb肯定乱码。
我们只需要修改转换字符集为
american_america.ZHS16GBK

再次查看
SQL> connect sys/gelc123@192.168.190.30:1521/test as sysdba
Connected.
SQL> select * from testchar;

NAME
----------------------------------------


没有问题。

关于linux的locale参考如下:
Installing the locales package

In Debian, locales is a seperate package from glibc, so you'll have to install it in order to generate and use the Japanese locale in Liunx. During installation, debconf will ask you to choose which locales to generate. For starters, pick ja_JP.EUC-JP and en_US. You may also want to choose en_US.UTF-8 and ja_JP.UTF-8 for unicode support.

After installation, you can choose and generate locales manually by editing /etc/locale.gen. The contents of mine are as follows: 
# This file lists locales that you wish to have built. You can find a list
# of valid supported locales at /usr/share/doc/locales/SUPPORTED.gz. Other
# combinations are possible, but may not be well tested. If you change
# this file, you need to rerun locale-gen.

en_US ISO-8859-1
en_US.UTF-8 UTF-8
ja_JP.EUC-JP EUC-JP
ja_JP.UTF-8 UTF-8
After editing the file, run locale-gen as root. 
Setting up the environment

There are several special environment variables that determine how various locale-specific tasks should be handled.
LANG - Specifies the default locale for all unset locale variables
LANGUAGE - Most programs use this for the language of its interface
LINGUAS - (Obsolete?) The WindowMaker window manager used to use this instead of LANGUAGE.
LC_ALL - Overrides the locale for all LC_ variables and LANG
LC_CTYPE - Character handling functions
LC_COLLATE - Affects glob bracket ranges and sort order
LC_MESSAGES - Language that messages should be written in. Most programs use the value of LANGUAGE instead.
LC_NUMERIC - Determines the thousands seperator and how to write floating point numbers
LC_TIME - How to format dates and times
To run X with an English interface but the ability to display, input, and copy-paste Japanese text, set the environment variables as follows prior to running X or in your .Xsession file. These examples assume your are running a Bourne-like shell, such as sh, bash, or zsh: 
export LANGUAGE=en
export LINGUAS=en
export LC_CTYPE=ja_JP # May break certain things like window maker; untested
export LC_TIME=C # Format time as English
export LC_NUMERIC=C # Format numbers as English
export LC_MESSAGES=C # Output messages in English
export LC_COLLATE=ja_JP # Do sorting and collating of characters as Japanese
export LANG=ja_JP # Use Japanese for all others
If you are using a c-shell like csh or tcsh, use the following instead: 
setenv LANGUAGE en
setenv LINGUAS en
setenv LC_CTYPE ja_JP 
setenv LC_TIME C 
setenv LC_NUMERIC C 
setenv LC_MESSAGES C 
setenv LC_COLLATE ja_JP
setenv LANG ja_JP

至少我们可以得出结论LC_*覆盖LC_ALL覆盖LANG ,
所有我们需要设置
export LC_ALL=zh_CN.GBK
export LANG=zh_CN.GBK
但是在测试中这些设置并不影响vi的编辑和输出,他影响的是LINUX自己的程序比如date
[root@ora12ctest ~]# date
2016年 08月 16日 星期二 02:27:36 CST
这个必须要设置客户端字符集为gbk才行否则乱码,如下:
[root@ora12ctest ~]# date
201686:28:27 CST

所以我们得出的结论不受locale影响。也就是客户端使用什么字符集vi录入数据还是什么字符集,显示也是依据客户端字符集
比如设置LC_*为GBK,客户端使用UTF8那么vi录入数据还是UTF8,如果more这个文件只要客户端使用UTF8字符集就能正常
显示
如:客户端设置为UTF8
[root@ora12ctest ~]# locale
LANG=zh_CN.GBK
LC_CTYPE="zh_CN.GBK"
LC_NUMERIC="zh_CN.GBK"
LC_TIME="zh_CN.GBK"
LC_COLLATE="zh_CN.GBK"
LC_MONETARY="zh_CN.GBK"
LC_MESSAGES="zh_CN.GBK"
LC_PAPER="zh_CN.GBK"
LC_NAME="zh_CN.GBK"
LC_ADDRESS="zh_CN.GBK"
LC_TELEPHONE="zh_CN.GBK"
LC_MEASUREMENT="zh_CN.GBK"
LC_IDENTIFICATION="zh_CN.GBK"
LC_ALL=zh_CN.GBK

vi test3.txt文件录入
高鹏
[root@ora12ctest ~]# file test3.txt 
test3.txt: UTF-8 Unicode text
[root@ora12ctest ~]# more test3.txt 
高鹏
录入和显示均没有问题。

下面转自网络:
locale把按照所涉及到的文化传统的各个方面分成12个大类,这12个大类分别是: 

1、语言符号及其分类(LC_CTYPE) 
2、数字(LC_NUMERIC) 
3、比较和排序习惯(LC_COLLATE) 
4、时间显示格式(LC_TIME) 
5、货币单位(LC_MONETARY) 
6、信息主要是提示信息,错误信息,状态信息,标题,标签,按钮和菜单等(LC_MESSAGES) 
7、姓名书写方式(LC_NAME) 
8、地址书写方式(LC_ADDRESS) 
9、电话号码书写方式(LC_TELEPHONE) 
10、度量衡表达方式 (LC_MEASUREMENT) 
11、默认纸张尺寸大小(LC_PAPER) 
12、对locale自身包含信息的概述(LC_IDENTIFICATION)。


所以说,locale就是某一个地域内的人们的语言习惯和文化传统和生活习惯。一个地区的locale就是根据这几大类的习惯定义的,这些locale定 义文件放在/usr/share/i18n/locales目录下面,例如en_US, zh_CN and de_DE@euro都是locale的定义文件,这些文件都是用文本格式书写的,你可以用写字板打开,看看里边的内容,当然出了有限的注释以外,大部分 东西可能你都看不懂,因为是用的Unicode的字符索引方式。


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
中文乱码处理
由于编码方式的不同,各种编码处理的语言不通,将导致中文乱码问题: 一、几种常见的编码方式: 1、ISO-8859-1:属于单字节编码,最多表示的字符范围是0-255,应用于英文系列。 2、GB2312/GBK:属于汉字的国标码,专门表示汉字,是双字节编码,还兼容ISO-8859-1编码,其中GBK同时表示繁体字和简体字,而GB2312只能表示简体字。
797 0
浅析pinyin4j源码 简单利用pinyin4j对中文字符进行自然排序(转)
pinyin4j项目  官网地址 http://pinyin4j.sourceforge.net/   我们先把资源下载下来,连同源码和jar包一起放入工程。如下图:   接下来在demo包下,我们写一个测试类,简单使用pinyin4j对中文字符进行自然排序 新建一个ConvertTest.
1066 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
4623 0
Windows下CMD中文乱码问题解决方法,设置代码页65001后仍然乱码
原文地址: http://blog.csdn.net/u011250882/article/details/48136883 在中文Windows系统中,如果一个文本文件是UTF-8编码的,那么在CMD.exe命令行窗口(所谓的DOS窗口)中不能正确显示文件中的内容。在默认情况下,命令行窗口中使用的代码页是中文或者美国的,即编码是中文字符集或者西文字符集。  如果想正确显示UTF-8
6637 0
mysql中文乱码
mysql是我们项目中非常常用的数据型数据库。但是因为我们需要在数据库保存中文字符,所以经常遇到数据库乱码情况。下面就来介绍一下如何彻底解决数据库中文乱码情况。 1、中文乱码 1.1、中文乱码 create table user(name varchar(11)); # 创建user表 i...
2231 0
+关注
重庆八怪
10年ORACLE/MYSQL DBA,有一定C/C++基础
635
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《SaaS模式云原生数据仓库应用场景实践》
立即下载
文娱运维技术
立即下载
《看见新力量:二》电子书
立即下载