mysql 字符集亂碼探究

dbasdk發表於2017-03-13
MySql
環境描述：青雲的mysql例項的ip為：192.168.0.254，和青雲的跳板主機，我們在跳板主機上安裝了mysql服務，並透過下面方式連線mysql：
[root@i-iivphroy ~]# mysql -uroot -p********* -h192.168.0.254
問題描述：近期網站整體遷雲，需要先遷移一部分資料到雲，採用mysqldump的方法，可是在雲上source完成之後，卻發現中文亂碼。
一：檢視源端mysql的相關資訊。
1：檢視源端mysql的表的字符集，為utf8.
mysql> show create table v_publish_info;
。
。
。
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
2：檢視mysql關於字符集的引數，
MariaDB [log]> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
MariaDB [log]> show variables like 'character_set_%';
+--------------------------+------------------------+
| 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/share/charsets/ |
二：檢視目標雲端的相關資訊
1，檢視目標端mysql的表的字符集，為utf8.
mysql> show create table v_publish_info;
。
。
。
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
2，檢視mysql關於字符集的引數，發現是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 | latin1 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> show variables like 'collation_%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_general_ci |
| collation_server | latin1_general_ci |
+----------------------+-------------------+
3 rows in set (0.01 sec)
原來是青雲的mysql預設的字符集相關引數是latin1，但是我們的表是utf8,這導致亂碼，驗證：
在雲端修改引數,都改成utf8,也就是改成和源端一樣，
mysql> set character_set_client=utf8 ;
mysql> set character_set_connection =utf8 ;
。
。
。
mysql> set collation_server =utf8_general_ci ;
再次檢視資料，中文不再亂碼：
mysql> select title from v_publish_info limit 2;
+-----------------------------------------------------------------------------------------------------------------+
| title |
+-----------------------------------------------------------------------------------------------------------------+
| 即墨省級經濟開發區藍色新區管理委員會關於體育中心銅鋁複合散熱器邀請報價的函 |
| 2015年招投標領域十大關鍵詞 |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
既然確定了就是這些引數導致的問題，那麼接下來開始解決問題，前面修改的引數，僅僅是在當前會話生效的，新開session無效，於是透過控制檯修改mysql的引數，
character_set_server=utf8
然後重啟mysql例項，然後再次檢視mysql的資料，發現依舊亂碼，
mysql> select title from v_publish_info limit 2;
+---------------------------------------+
| title |
+---------------------------------------+
| ????????????????????????????????????? |
| 2015??????????? |
+---------------------------------------+
2 rows in set (0.00 sec)
再次檢視相關引數：發現還有 latin1
mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> show variables like 'collation_%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
我們已經把mysql服務端的相關引數修改了，剩下的是clint端的引數，我們是透過下面方式連線資料庫的，也就是說mysql並沒有在這臺伺服器上，而是在192.168.0.254上面的。
[root@i-iivphroy ~]# mysql -uroot -p********* -h192.168.0.254
突然想到那這臺跳板機就相當於是客戶端了，這裡面也有my.cnf的配置檔案，嘗試去修改這裡，如下紅色部分，是設定客戶端的引數的：
[root@i-iivphroy ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#[mysqld]
#default-character-set=utf8
#init_connect = 'SET NAMES utf8'
[client]
default-character-set=utf8
然後從新登入資料庫：
[root@i-iivphroy ~]# mysql -uroot -p********* -h192.168.0.254
再次檢視相關引數，徹底和源端一樣了：
mysql> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.01 sec)
mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| 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 | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
再次檢視資料，不在亂碼：
mysql> select title from v_publish_info limit 2;
+-----------------------------------------------------------------------------------------------------------------+
| title |
+-----------------------------------------------------------------------------------------------------------------+
| 即墨省級經濟開發區藍色新區管理委員會關於體育中心銅鋁複合散熱器邀請報價的函 |
| 2015年招投標領域十大關鍵詞 |
+-----------------------------------------------------------------------------------------------------------------+
下面講解下這幾個引數
系統變數：
– character_set_server：預設的內部操作字符集
– character_set_client：客戶端來源資料使用的字符集
– character_set_connection：連線層字符集
– character_set_results：查詢結果字符集
– character_set_database：當前選中資料庫的預設字符集
– character_set_system：系統後設資料(欄位名等)字符集
– 還有以collation_開頭的同上面對應的變數，用來描述字元序。
1.庫、表、列字符集的由來：
(1).建庫時，若未明確指定字符集，則採用character_set_server指定的字符集。
(2).建表時，若未明確指定字符集，則採用當前庫所採用的字符集。
(3).新增，修改表欄位時，若未明確指定字符集，則採用當前表所採用的字符集。
2.更新、查詢涉及到得字符集變數：
使用者在更新(插入，刪除，修改)，查詢資料庫時，最常使用的字符集變數主要包含：character_set_client，character_set_connection，character_set_result。
（1）更新流程字符集轉換過程：character_set_client------->character_set_connection----->表字符集。
（2）查詢流程字符集轉換過程：表字符集------->character_set_result
總結：透過這次解決問題的過程，修正了我原來的認識，原來資料庫的引數，可以透過修改客戶端（資料庫沒在這個伺服器上）的配置檔案my.cnf來改變，並且瞭解到了查詢一條資料，需要把表的字符集轉換成character_set_result的字符集，亂碼你就修改這個character_set_result引數即可。並且mysql資料庫的my.cnf最好設定上如下兩個引數：
[mysqld]
default-character-set=utf8
[client]
default-character-set=utf8

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29734436/viewspace-2135278/，如需轉載，請註明出處，否則將追究法律責任。

相關文章