MySQL 字元集和校驗規則工作原理

字元編碼相關參數

數據流中的轉碼過程

校驗規則

Tips:字元集和校驗規則總是相伴的

一 從簡單的建庫語句開始

CREATE DATABASE [IF NOT EXISTS] <db_name>
[[DEFAULT] CHARACTER SET <db_charset>] 
[[DEFAULT] COLLATE <db_collation>];

db_name ： 資料庫名 必填

db_charset：資料庫的字元集 預設為伺服器字元集

db_collation：資料庫的校驗規則 預設為伺服器校對規則

二 字元集和字元編碼是什麼？

字元編碼：將特定的字元與二進位碼建立一一映射的集合就是字元集。每種字元集對應該種字元集的編碼方式。

常見的字元集有 僅支持英文和特殊字元的ASCII、支持中英文的GBK、支持世界所有字元的Unicode等等<UTF-8是Unicode字元集的子集，他們不是兩種編碼方式>。

以ASCII字元集為例

它基於羅馬字母表的編碼方式，他不能表示中文僅僅包含了 全部的英文大小寫和為數不多的特殊符號，每個字元一個位元組低7位為編碼位最高位保留，有些地方最高位做了擴充。增添了一些表格符號、運算符等。總而言之1個位元組 8bit 表示一個字元，因為一一對應，所有一共有 2^{7個字元。擴展字元集擁有2}8。

三 查看MySQL編碼方式

show variables like 'character%';

1. MySQL 一旦啟動不需要再來關心 character_set_filesystem、character_set_system、character_sets_dir三個變數，因為他們並不會造成亂碼的問題。系統文件存儲方式不需要關心，字元集存放位置於性能和MySQL業務無關、資料庫使用的編碼格式是元數據的存儲格式。理解MySQL編碼轉化原理不難理解。

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

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

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

四 編碼方式和校驗規則在使用中的作用

1 連接的概念

一個連接：指的是連接伺服器時所作的事情。 ——《MySQL手冊》

例如：客戶端發送SQL語句，例如查詢，通過連接發送到伺服器。伺服器通過連接發送響應給客戶端，例如結果集。

2 一個提交的分解

1. 客戶端發起 查詢

2. 伺服器使用character_set_client變數作為客戶端發送的查詢中使用的字元集。

3. 伺服器拿到 查詢 後用將character_set_client 編碼方式轉為 character_set_connection對應的校驗規則為collation_connection， (如果查詢是文字字元串，也就是他們有某種字元格式的引介詞 例如_utf8,如果是列值，校驗規則將不依靠collation_connection)

4. 伺服器執行查詢的結果 將會 按照 character_set_results 編碼方式返回查詢結果到客戶端。包括結果數據，例如列值和結果元數據（如列名）。

5. 關於：字元串對3的註解：**[_charset_name] 'String' [COLLATE collation_name] **

   1. [_charset_name]字元編碼 也就是引介詞，表示接下來的字元串的編碼方式。
   2. [COLLATE collation_name]表示該字元串的校驗匹配方式
   3. 來自官方文檔對引介詞的解釋：_charset_name表達式正式稱做一個引介詞。它告訴解析程式，“後面將要出現的字元串使用字元集X。”因為以前人們對此感到困惑，我們強調引介詞不導致任何轉換; 它僅是一個符號，不改變字元串的值。引介詞在標準十六進位字母和數字十六進位符號（x'literal'和 0xnnnn）中是合法的，以及？（當在一個編程語言介面中使用預處理的語句時進行參數替換）。

3 更新和查詢轉碼過程

數據更新轉碼過程：character_set_client-->character_set_connection-->表字元集。

數據查詢轉碼過程：表字元集-->character_set_result

4 字元串的引介詞和校驗規則的確定

引介詞：_charset_name表達式正式稱做一個引介詞。它告訴解析程式，“後面將要出現的字元串使用字元集X。”因為以前人們對此感到困惑，我們強調引介詞不導致任何轉換; 它僅是一個符號，不改變字元串的值。引介詞在標準十六進位字母和數字十六進位符號（x'literal'和 0xnnnn）中是合法的，以及？（當在一個編程語言介面中使用預處理的語句時進行參數替換）。

• 如果指定了CHARACTER SET X和COLLATE Y，那麼使用CHARACTER SET X和COLLATE Y。

• 如果指定了CHARACTER SET X而沒有指定COLLATE Y，那麼使用CHARACTER SET X和CHARACTER SET X的預設校對規則。

• 否則，使用通過character_set_connection 和 collation_connection系統變數給出的字元集和 校對規則。

Tips：COLLATE子句，能夠為一個查詢覆蓋任何預設校對規則。MySQL手冊

5 關於校驗規則

1. 每個字元集都有預設的校驗規則
2. 兩個不同的字元集不能有一致的校驗規則
3. 存在校對規則命名約定：以其相關的字元集名開始，中間包括一個語言名，並且以_ci（大小寫不敏感）、_cs（大小寫敏感）或_bin（二元）結束。

常見操作

查看現在支持的所有編碼方式

show character set;

查看支持的所有檢驗規則

show collation;

查看字元編碼設置

show variables like 'character%';

查看當前字元集和校對規則設置

show variables like 'collation_%';

設置編碼字元集

set names 'utf8';

修改資料庫字元集

alter database database_name character set xxx;

修改表的字元集

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

alter table table_name character set xxx；

2. 同時修改表字元集和已有列字元集，並將已有數據進行字元集編碼轉換。

alter table table_name convert to character set xxx;

修改列 字元集

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