引言
定位：查詢資料庫得到的結果集亂碼，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在官網也提到：
Every client has session-specific connection-related character set and collation system variables. These session system variable values are initialized at connect time, but can be changed within the session.
每個客戶端或者終端在連線上server之後，都有自己特定的系統變數，伺服器預設採用客戶端的本地化引數初始化系統變數的值，並且在本次會話中可以動態改變其值。Linux下的shell和win下的dos在連線到server之後，本地化引數可能存在差異（編碼格式），上面三個變數在不同終端下值的不同就是最好的驗證。可以看下面截圖。
【注】*當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的格式下，插入一條資料
如果插入資料包錯，改下sql_mode模式：
set sql_mode="";
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可以解決亂碼呢？
The character encoding between client and server is automatically detected upon connection (provided that the Connector/J connection properties
characterEncodingandconnectionCollationare not set). You specify the encoding on the server using the system variablecharacter_set_server(for more information, see Server Character Set and Collation). The driver automatically uses the encoding specified by the server.To override the automatically detected encoding on the client side, use the
characterEncodingproperty in the connection URL to the server. Use Java-style names when specifying character encodings. The following table lists MySQL character set names and their corresponding Java-style names:
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有一部分編解碼在本地完成。
思考，變化三個變數的值和庫表欄位編碼，組合實驗，嘗試推斷結果。
本文參考