MySQL之字元集-校對規則

標籤：電腦   arc   服務端   round   步驟   更新   做了   編碼轉換   業界   

一、字元集(Character set)

　　是多個字元(英文字元，漢字字元，或者其他國家語言字元)的集合，字元集種類較多，每個字元集包含的字元個數不同。

特點：

　　①字元編碼方式是用一個或多個位元組表示字元集中的一個字元

　　②每種字元集都有自己特有的編碼方式，因此同一個字元，在不同字元集的編碼方式下，會產生不同的二進位

常見字元集：

　　ASCII字元集：基於羅馬字母表的一套字元集，它採用1個位元組的低7位表示字元，高位始終為0。

　　LATIN1字元集：相對於ASCII字元集做了擴充，仍然使用一個位元組表示字元，但啟用了高位，擴充了字元集的表示範圍。

　　GBK字元集：支援中文，字元有一位元組編碼和兩位元組編碼方式。

　　UTF8字元集：Unicode字元集的一種，是電腦科學領域裡的一項業界標準，支援了所有國家的文字字元，utf8採用1-4個位元組表示字元。

 

1、MySQL與字元集：

　　只要涉及到文字的地方，就會存在字元集和編碼方式。MySQL系統變數值：

 

 

2、正確使用字元集

　　資料庫服務端的字元集具體要看儲存什麼字元

以上這些參數如何起作用：

1.庫、表、列字元集的由來

　　①建庫時，若未明確指定字元集，則採用character_set_server指定的字元集。

　　②建表時，若未明確指定字元集，則採用當前庫所採用的字元集。

　　③新增時，修改表欄位時，若未明確指定字元集，則採用當前表所採用的字元集。

2.更新、查詢涉及到得字元集變數

　　更新流程字元集轉換過程：character_set_client-->character_set_connection-->表字元集。

　　查詢流程字元集轉換過程：表字元集-->character_set_result

3.character_set_database

　　當前預設資料庫的字元集，比如執行use xxx後，當前資料庫變為xxx，若xxx的字元集為utf8，那麼此變數值就變為utf8(供系統設定，無需人工設定)。

 

3、MySQL用戶端與字元集

1.對於輸入來說：

　　用戶端使用的字元集必須通過character_set_client、character_set_connection體現出來：

　　　　①在用戶端對資料進行編碼（Linux：utf8、windows：gbk）

　　　　②MySQL接到SQL語句後(比如insert)，發現有字元，詢問用戶端通過什麼方式對字元編碼：用戶端通過character_set_client參數告知MySQL用戶端的編碼方式(所以此參數需要正確反映用戶端對應的編碼)

　　　　③當MySQL發現用戶端的client所傳輸的字元集與自己的connection不一樣時，會將client的字元集轉換為connection的字元集

　　　　④MySQL將轉換後的編碼儲存到MySQL表的列上，在儲存的時候再判斷編碼是否與內部儲存字元集（按照優先順序判斷字元集類型）上的編碼一致，如果不一致需要再次轉換

2.對於查詢來說：

　　用戶端使用的字元集必須通過character_set_results來體現，伺服器詢問用戶端字元集，通過character_set_results將結果轉換為與用戶端相同的字元集傳遞給用戶端。(character_set_results預設等於character_set_client)

 

4、MySQL字元編碼轉換原理：

　　問：若character_set_client為UTF8，而character_set_database為GBK，則會出現需要進行編碼轉換的情況，字元集轉換的原理是什嗎？

　　答：假設gbk字元集的字串“你好”，需要轉為utf8字元集儲存，實際就是對於“你好”字串中的每個漢字去utf8編碼錶裡面查詢對應的二進位，然後儲存。

 

圖解字元集轉換過程：

　　①MySQL Server收到請求時將請求資料從character_set_client轉換為character_set_connection;

　　②進行內部操作前將請求資料從character_set_connection轉換為內部操作字元集

確定步驟：

--使用每個資料欄位的CHARACTER SET設定值；

--若上述值不存在，則使用對應資料表的DEFAULT CHARACTER SET設定值；

--若上述值不存在，則使用對應資料庫的DEFAULT CHARACTER SET設定值；

--若上述值不存在，則使用character_set_server設定值；

　　③將操作結果從內部操作字元集轉換為character_set_results。

 

5、字串常見處理操作

1.查看字元集編碼設定

　　mysql> show variables like ‘%character%‘;

 

2.設定字元集編碼

　　mysql> set names ‘utf8‘;

相當於同時：

　　set character_set_client = utf8;

　　set character_set_results = utf8;

　　set character_set_connection = utf8;

 

3.修改資料庫字元集

　　mysql> alter database database_name character set xxx;

只修改庫的字元集，影響後續建立的表的預設定義；對於已建立的表的字元集不受影響。（一般在資料庫實現字元集即可，表和列都預設採用資料庫的字元集）

 

4.修改表的字元集

　　mysql> alter table table_name character set xxx；

只修改表的字元集，影響後續該表新增列的預設定義，已有列的字元集不受影響。

　　mysql> alter table table_name convert to character set xxx;

同時修改表字元集和已有列字元集，並將已有資料進行字元集編碼轉換。

 

5.修改列字元集

格式：

ALTER TABLE table_name MODIFY

column_name {CHAR | VARCHAR | TEXT} (column_length)

    [CHARACTER SET charset_name]

    [COLLATE collation_name]

mysql> alter table table_name modify col_name varchar(col_length) character set xxx;

 

6、字元集的正確實踐：

MySQL軟體工具本身是沒有字元集的，主要是因為工具所在的OS的字元集（Windows：gbk、Linux：utf8），所以字元集的正確實踐非常重要：

　　1.對於insert來說，character_set_client、character_set_connection相同，而且正確反映用戶端使用的字元集

　　2.對於select來說，character_set_results正確反映用戶端字元集

　　3.資料庫字元集取決於我們要儲存的字元類型

　　4.字元集轉換最多發生一次，這就要求character_set_client、character_set_connection相同

　　5.所有的字元集轉換都發生在資料庫端

綜述：

　　1、建立資料庫的時候注意字元集（gbk、utf8）；

　　2、串連資料庫以後，無論是執行dml還是select，只要涉及到varchar、char列，就需要設定正確的字元集參數。

 

 

二、字元集與校對規則collation校對

　　查看資料庫支援的所有字元集(charset)：是一套符號和對應的編號

mysql> show character set;

校對規則(collation)：

　　是在字元集內用於字元比較和排序的一套規則，比如有的規則區分大小寫，有的則無視。

mysql> create table t1(id int,name varchar(20));    #t1建表沒有指定校對規則

 

mysql> show collation;  #查看資料庫支援的所有校對規則

mysql> show variables like ‘collation_%‘;  #查看當前字元集和校對規則設定

校對規則特徵：

　　①兩個不同的字元集不能有相同的校對規則；

　　②每個字元集有一個預設校對規則；

　　③存在校對規則命名規範：以其相關的字元集名開始，中間包括一個語言名，並且以_ci（大小寫不敏感）、_cs（大小寫敏感）或_bin（二元）結束。

注意：

　　系統使用utf8字元集，若使用utf8_bin校對規則執行SQL查詢時區分大小寫，使用utf8_general_ci不區分大小寫(預設的utf8字元集對應的校對規則是utf8_general_ci)。

樣本：

mysql> create table t2(id int,name varchar(20)) character set=gbk collate=gbk_bin;    #t2建表指定校對規則（區分大小寫）

MySQL之字元集-校對規則