mysql 字符集亂碼研究

2024-07-24 12:32:23

字體：大中小

來源：轉載

供稿：網友

環境描述：青云的mysql實例的ip為：192.168.0.254，和青云的跳板主機，我們在跳板主機上安裝了mysql服務，并通過下面方式連接mysql：
[root@i-iivphroy ~]# mysql -uroot -p********* -h292.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********* -h292.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********* -h292.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

（編輯：武林網）

上一篇：MySQL復制之防崩潰從細節

下一篇：MySQL搜尋元數據信息