關於資料庫的字符集問題一直都是一個比較噁心的問題，如果不瞭解其實質可能一直
都搞不清楚這個問題的根源，只能出了問題去度娘，這裡我打算相容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提取出其資料。
可以在百度雲

下載到
[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的字元索引方式。

關於ORACLE和MYSQL中文字元亂碼的根源剖析

相關文章