ssh遠端連線linux下的MySQL資料庫字元集和防止亂碼

標籤：blog   mysql   table   多執行個體   ima   order   國際   中文   業務   

實驗環境：

本地windows 8.1

遠端連線工具 SecureCRT 7.3

Linux發行版本 CentOS 6.7 x86_64位Linux系統，核心的版本為2.6.32-573

mysql版本 mysql-5.5.32

1.1 MySQL資料庫字元集介紹

字元集就是一套文字元號及編碼、比較規則的集合

MySQL資料庫字元集包括字元集（CHARACTER）和校對規則（COLLATION）兩個概念。其中，字元集是用來定義MySQL資料字串的儲存方式，而校對規則是定義比較字串的方式。

1.1.1 在互連網環境中，使用MySQL時常用的字元集有

常用字元集	一個漢字長度	說明
GBK	2位元組	不是國際標準，對中文環境支援的很好
UTF-8	3位元組	中英文混合的環境，建議使用此字元集，用的比較多
latin1	1位元組	MySQL的預設字元集
utf8mb4	4位元組	UTF-8 Unicode，用於移動互連網

1.1.2 MySQL如何選擇合適的字元集

1)        如果處理各種各樣的文字，發布到不同語言國家地區，應選擇Unicode字元集，對mysql來說就是UTF-8（每個漢字3位元組），更適合於多英文少中文

2)        如果只需要支援中文，並且資料量很大，效能要求也很高，可選GBK（定長，每個漢字占雙位元組，英文也占雙位元組），更適合於大量運算、比較排序，定長字元集，效能較高

3)        處理移動互連網業務，可能需要使用utf8mb4字元集

老師建議：沒有特別需求，請選擇UTF-8

1.1.3 查看當前MySQL系統支援的字元集

mysql> show character set;

1.2 查看系統當前使用的字元集

mysql> show variables like ‘character_set%‘;

 

想要不亂碼，就要讓上述字元集統一

1.3 更改用戶端的字元集1.3.1 進入資料庫後更改用戶端字元集

set names gbk;

將3個用戶端的字元集（1、2、5）改為gbk

相當於輸入下面3條命令：

set character_set_client = gbk;

set character_set_results = gbk;

set character_set_connection = gbk;

1.3.2 登入資料庫時更改用戶端字元集，效果同上

[[email protected] ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock --default-character=gbk;

mysql> show variables like ‘character_set%‘;

 

1.3.3 修改設定檔my.cnf

永久生效，上面

[client]

default-character-set=gbk

注意：多執行個體的情況下，此步驟修改字元集，要修改/etc/my.cnf

1.4 更改服務端字元集1.4.1 修改設定檔my.cnf

[mysqld]

default-character-set=utf8  ##適合5.1及以前版本

character-set-server=uft8  ##適合5.5

重啟服務後永久生效

這個修改影響的第3和第6個字元集，相當於修改

character_set_database

character_set_server

1.4.2 在編譯的時候指定服務端字元集

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \

1.4.3 命令列修改庫的字元集

不建議在有資料的庫上修改，下面的命令對之前已經存在的資料無效，影響之後更新的資料

mysql> alter database oldboy character set latin1 collate = latin1_swedish_ci;

mysql> show create database oldboy\G

1.4.4 命令列修改表的字元集

不建議在有資料的表上修改，下面的命令對之前已經存在的資料無效，影響之後更新的資料

mysql> alter table oldboy.test character set latin1;

mysql> show create table oldboy.test\G

1.5 防止亂碼，統一其它地方1.5.1 建表建庫的時候注意統一字元集

建庫

create database oldboy_utf8 default character set utf8 collate utf8_general_ci

建表

create table `student` (

`id`  int(4)  not null  auto_increment,

`name`  char(20)  not null,

primary key (`id`),

)  ENGINE=InnoDB  AUTO_INCREMENT=10  DEFAULT  CHARSET=utf8

1.5.2 程式

程式碼的編碼要和庫表統一，UTF-8無簽名

1.5.3 linux服務端

cat /etc/sysconfig/i18n

$LANG的字元集要和資料庫統一

1.5.4 串連工具CRT的字元集

更改SecureCRT工具的預設字元集為utf-8

1.5.5 在資料庫中執行sql語句的方法

1)        操作習慣

盡量不在MySQL命令列直接插入資料（SSH用戶端影響），將SQL語句放到檔案裡

2)        sql檔案的格式

統一使用“uft8沒有簽名”

3)        匯入檔案方式

可在MySQL命令列中用source執行sql檔案

命令方式匯入資料mysql –uroot –poldboy123 oldboy <test.sql

4)        匯入sql語句時設定用戶端字元集

sql檔案裡加入set names utf8;

或者mysql –uroot –poldboy123 oldboy --default-character-set=uft8 <test.sql

1.6 更改已有資料的資料庫字元集

思想：匯出原庫的表結構和資料，刪除原庫建立新庫，將表結構和資料匯入新庫

1.6.1 匯出表結構

mysqldump –uroot –poldboy123 --default-character-set=latin1 –d dbname >alltable.sql

--default-character-set=uft8表示以utf8字元集進行串連，-d只導表結構

1.6.2 編輯表結構語句

表結構alltable.sql將所有latin1字串改成utf8（可以用sed做個替換）

1.6.3 匯出資料

確保資料庫不再更新，匯出所有資料（不帶表結構）

mysqldump –uroot –poldboy123 --quick --no-create-info --extended-insert --default-character-set =latin1 dbname >alldata.sql

參數說明：

--quick  用於轉儲大的表，強制mysqldump從伺服器一次一行的檢索資料而不是檢索所有行，並輸出前CACHE到記憶體中

--no-create-info  不建立CREATE TABLE語句

--extended-insert  使用包括幾個VALUES列表的多行INSERT文法，這樣檔案更小，IO也小，匯入資料時會非常快

--default-character-set =latin1  按照原有字元集匯出資料，這樣匯出的檔案中，所有中文都是可見的，不會儲存成亂碼

1.6.4 修改my.cnf配置

調整用戶端及服務端字元集，重啟生效

1.6.5 通過utf8建庫

刪除原庫，然後create database dbname default charset utf8;

1.6.6 匯入表結構

匯入的是更改過的字元集的表結構

mysql –uroot –poldboy123 dbname <alltable.sql

1.6.7 匯入資料

mysql –uroot –poldboy123 dbname <alldata.sql

PS：選擇目錄字元集時，要注意最好大於等於源字元集（字型檔更大），否則可能會丟失不被支援的資料

ssh遠端連線linux下的MySQL資料庫字元集和防止亂碼