MySQL數(shù)據(jù)庫基礎(chǔ)（二）——MySQL字符集與亂碼解析

MySQL數(shù)據(jù)庫基礎(chǔ)（二）——MySQL字符集與亂碼解析

一、字符集與編碼

1、字符集簡介

字符(Character)是各種文字和符號的總稱，包括各國家文字、標點符號、圖形符號、數(shù)字等。
字符集(Character set)是多個字符的集合，字符集種類較多，每個字符集包含的字符個數(shù)不同，常見字符集名稱：ASCII字符集、GB2312字符集、BIG5字符集、 GB18030字符集、Unicode字符集等。計算機要準確的處理各種字符集文字，需要進行字符編碼，以便計算機能夠識別和存儲各種文字。
字符編碼（Character encoding）是把字符集中的某個字符編碼為指定字符集中字符，以便文本在計算機中存儲和通過通信網(wǎng)絡(luò)的傳遞。常見的例子包括將拉丁字母表編碼成ASCII，ASCII將字母、數(shù)字和其它符號編號，并用7比特的二進制來表示。
字符序(collation)是指同一個字符集內(nèi)字符之間的比較規(guī)則。只有確定字符序后，才能在一個字符集上定義什么是等價的字符，以及字符之間的大小關(guān)系。一個字符可以包含多種字符序。MySQL字符序命名規(guī)則是：以字符序?qū)?yīng)的字符集名稱開頭，以國家名居中(或以general居中)，以ci、cs、或bin結(jié)尾。以ci結(jié)尾的字符序表示大小寫不敏感，以cs結(jié)尾的字符序表示大小寫敏感，以bin結(jié)尾的字符序表示按二進制編碼值比較。

2、ASCII編碼

ASCII既是編碼字符集，又是字符編碼，ASCII直接將字符在編碼字符集中的序號作為字符在計算機中存儲從數(shù)值。
例如：在ASCII中A字符在表中排第65位，序號是65，而編碼后A的數(shù)值是0100 0001，即十進制的65的二進制轉(zhuǎn)換結(jié)果。

3、Latin1字符集

Latin1字符集在ASCII字符集基礎(chǔ)上進行了擴展，仍然使用一個字節(jié)表示字符，但啟用了高位，擴展了字符集的表示范圍。

4、UTF-8編碼

UTF-8（8-bit Unicode Transformation Format）是一種針對Unicode的可變長度字符編碼，又稱萬國碼。由Ken Thompson于1992年創(chuàng)建?，F(xiàn)在已經(jīng)標準化為RFC 3629。UTF-8用1到6個字節(jié)編碼Unicode字符。
UTF-8是一種變長字節(jié)編碼方式。對于某一個字符的UTF-8編碼，如果只有一個字節(jié)則其最高二進制位為0；如果是多字節(jié)，其第一個字節(jié)從最高位開始，連續(xù)的二進制位值為1的個數(shù)決定了其編碼的位數(shù)，其余各字節(jié)均以10開頭。UTF-8最多可用到6個字節(jié)。 如表：
1字節(jié) 0xxxxxxx
2字節(jié) 110xxxxx 10xxxxxx
3字節(jié) 1110xxxx 10xxxxxx 10xxxxxx
4字節(jié) 11110xxx 10xxxxxx 10xxxxxx 10xxxxxx
5字節(jié) 111110xx 10xxxxxx 10xxxxxx 10xxxxxx 10xxxxxx
6字節(jié) 1111110x 10xxxxxx 10xxxxxx 10xxxxxx 10xxxxxx 10xxxxxx
因此UTF-8中可以用來表示字符編碼的實際位數(shù)最多有31位，即上表中x所表示的位。除去控制位（每字節(jié)開頭的10等），x表示的位與UNICODE編碼是一一對應(yīng)的，位高低順序也相同。
實際將UNICODE轉(zhuǎn)換為UTF-8編碼時應(yīng)先去除高位0，然后根據(jù)所剩編碼的位數(shù)決定所需最小的UTF-8編碼位數(shù)。 因此基本ASCII字符集中的字符（UNICODE兼容ASCII）只需要一個字節(jié)的UTF-8編碼（7個二進制位）便可以表示。

5、字符集兼容性

查看任意字符的指定編碼方式的編碼：
select hex(convert('string' using code));
查看編碼值在字符集中的字符：
select convert(0xABCDXXX using charsetname);
ASCII編碼實例：
select hex(convert('hello' using ascii));
字符串“hello”的ASCII編碼：0x68656C6C6F
Latin1編碼實例：
select hex(convert('hello' using latin1));
字符串“hello”的Latin1編碼：0x68656C6C6F
UTF-8編碼實例：
select hex(convert('hello' using utf8));
字符串“hello”的UTF-8編碼：0x68656C6C6F
GBK編碼實例：
select hex(convert('hello' using gbk));
字符串“hello”的GBK編碼：0x68656C6C6F
GB2312編碼實例：
select hex(convert('hello' using gb2312));
字符串“hello”的GB2312編碼：0x68656C6C6F
BIG5編碼實例：
select hex(convert('hello' using big5));
字符串“hello”的BIG5編碼：0x68656C6C6F
從以上實例可以看出，Latin1字符集兼容ASCII字符集；UTF-8、GBK、GB2312、BIG5字符集都兼容Latin1字符集。

中文“很屌”的UTF-8編碼實例：
select hex(convert('很屌' using utf8));
“很屌”的UTF-8編碼：0xE5BE88E5B18C
select CONVERT(0xE5BE88E5B18C USING utf8);
將“很屌”字符的UTF-8編碼值0xE5BE88E5B18C轉(zhuǎn)換為UTF-8中的字符
中文“很屌”的GBK編碼實例：
select hex(convert('很屌' using gbk));
“很屌”的GBK編碼：0xBADC8CC5
select CONVERT(0xBADC8CC5 USING gbk);
將“很屌”字符的GBK編碼值0xBADC8CC5 轉(zhuǎn)換為GBK中的字符
中文“很屌”的GB2312編碼實例：
select hex(convert('很屌' using gb2312));
“很屌”的GB2312編碼：0xBADC3F
select CONVERT(0xBADC3F USING gbk);
將“很屌”字符的GB2312編碼值0xBADC3F轉(zhuǎn)換為GBK中的字符，結(jié)果為“很？”，字符“屌”在GB2312字符集中不存在。
中文“很屌”的BIG5編碼實例：
select hex(convert('很屌' using big5));
“很屌”的BIG5編碼：0xABDCCE78
中文“很屌”的Latin1編碼實例：
select hex(convert('很屌' using latin1));
“很屌”的Latin1編碼：0x3F3F
中文“很屌”的ASCII編碼實例：
select hex(convert('很屌' using ascii));
“很屌”的ASCII編碼：0x3F3F
從以上實例可以看出，對于中文字符來說，UTF-8、GBK、GB2312、BIG5四種編碼之間是互不兼容的，直接相互轉(zhuǎn)換會導致亂碼；當UTF-8、GBK、GB2312、BIG5四種編碼轉(zhuǎn)換為ASCII編碼和Latin1編碼格式時，每個中文字符會被轉(zhuǎn)換為0x3F，即中文字符’？’。
GB2312支持簡體中文，BIG5支持繁體中文，GBK支持簡體中文及繁體中文，UTF-8支持幾乎所有字符。
GBK是國家標準GB2312基礎(chǔ)上擴容后兼容GB2312的標準。GB2312是GBK的子集，GBK是GB18030的子集。

二、MySQL字符集

1、MySQL環(huán)境變量

Session會話變量：
使用show variables like '%char%';可以查看Session會話的字符集變量：

set character_set_server=utf8;
set character_set_database=utf8;
使用SET可以設(shè)置不同字符集。但是使用SET設(shè)置的字符集都是Session會話級別的，如果新打開一個會話，新會話使用的是默認的字符集。
Global全局變量：
使用show global variables like '%char%';可以查看Global的字符集變量：

set global character_set_database=utf8;
set global character_set_server=utf8;
使用SET GLOBAL可以設(shè)置多個會話的字符集。
使用show charset;查看MySQL支持的字符集和對應(yīng)字符集的字符序。
MySQL服務(wù)重啟后，Global的值會被重置為默認值。永久修改Global的值的方法如下：
修改mysql配置文件/etc/my.cnf。
[mysqld]
character-set-server=utf8
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

2、MySQL字符集

MySQL服務(wù)器可以支持多種字符集，提供了不同級別的設(shè)置，包括server級、database級、table級、column級。
MySQL數(shù)據(jù)庫的環(huán)境變量查看使用SQL語句show variables like '%char%';
character_set_client：客戶端使用的字符集，當客戶端向服務(wù)器發(fā)送請求時，請求以客戶端字符集進行編碼。
character_set_connection ：客戶端/數(shù)據(jù)庫建立的通信連接使用的字符集，MySQL服務(wù)器接收客戶端的查詢請求后，將其轉(zhuǎn)換為character_set_connection變量指定的字符集。
character_set_database：數(shù)據(jù)庫服務(wù)器中某個數(shù)據(jù)庫的字符集，如果沒有默認數(shù)據(jù)庫字符集，使用 character_set_server指定的字符集。
character_set_results：數(shù)據(jù)庫給客戶端返回時的字符集，MySQL數(shù)據(jù)庫把結(jié)果集和錯誤信息轉(zhuǎn)換為character_set_results指定的字符集，并發(fā)送給客戶端。
character_set_server：數(shù)據(jù)庫服務(wù)器的字符集，內(nèi)部操作字符集。
character_set_system：系統(tǒng)元數(shù)據(jù)（字段名等）使用的字符集
當客戶端連接服務(wù)器的時候，客戶端會將自己想要的字符集名稱發(fā)給MySQL服務(wù)端，然后服務(wù)端就會使用字符集去設(shè)置character_set_connection、character_set_client、character_set_results。
創(chuàng)建數(shù)據(jù)庫時如果不指定數(shù)據(jù)庫的字符集，默認會使用character_set_server字符集。
創(chuàng)建表時如果不指定表的字符集，默認使用當前數(shù)據(jù)庫字符集。
創(chuàng)建列時如果不指定字符集，默認使用當前表的字符集。

3、MySQL字符集的設(shè)置

A、MySQL服務(wù)器級字符集
修改MySQL服務(wù)器配置文件/etc/my.cnf文件。
[mysqld]
character_set_server=utf8
?重啟MySQL數(shù)據(jù)庫服務(wù)生效。
B、MySQL數(shù)據(jù)庫級字符集：
創(chuàng)建數(shù)據(jù)庫時指定：
CREATE DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
修改已有的數(shù)據(jù)庫的字符集：
ALTER DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
ALTER修改只對修改后在數(shù)據(jù)庫上的操作有效。
C、MySQL表級字符集：
創(chuàng)建表時指定：
CREATE TABLE tbl_name (column_list)
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]]
修改表的字符集：
ALTER TABLE tbl_name
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]
D、MySQL字段級字符集：
修改已有字段的字符集：
ALTER TABLE table_name MODIFY
column_name {CHAR | VARCHAR | TEXT} (column_length)
??? [CHARACTER SET charset_name]
??? [COLLATE collation_name]
MySQL客戶端設(shè)置：set names utf8;等價于：
set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_results=utf8;
E、客戶端字符集
修改MySQL服務(wù)器配置文件/etc/my.cnf文件。
[client]
default-character-set=utf8
等價于set names utf8;
會影響會話中的變量character_set_client，character_set_connection 和character_set_results的值。
??修改后無需重啟MySQL數(shù)據(jù)庫服務(wù)即可生效。

4、MySQL字符集的轉(zhuǎn)換過程

A、MySQL服務(wù)端收到請求時將請求數(shù)據(jù)從character_set_client字符集轉(zhuǎn)換為character_set_connection字符集。
B、進行內(nèi)部操作前將請求數(shù)據(jù)從character_set_connection字符集轉(zhuǎn)換為內(nèi)部操作字符集。確定步驟：
--使用每個數(shù)據(jù)字段的CHARACTER SET設(shè)定值；
--若上述值不存在，則使用對應(yīng)數(shù)據(jù)表的DEFAULT CHARACTER SET設(shè)定值；
--若上述值不存在，則使用對應(yīng)數(shù)據(jù)庫的DEFAULT CHARACTER SET設(shè)定值；
--若上述值不存在，則使用character_set_server字符集設(shè)定值；
C、將操作結(jié)果從內(nèi)部操作字符集轉(zhuǎn)換為character_set_results字符集。
D、將character_set_results字符集的執(zhí)行結(jié)果轉(zhuǎn)換為character_set_client字符集，發(fā)送到客戶端，客戶端使用設(shè)置的字符集展示結(jié)果。

三、MySQL產(chǎn)生亂碼的產(chǎn)生

1、MySQL亂碼產(chǎn)生的原因

亂碼產(chǎn)生的原因如下：
A、存入和取出時對應(yīng)環(huán)節(jié)的編碼不一致。
B、如果兩個字符集之間無法進行無損編碼轉(zhuǎn)換，一定會出現(xiàn)亂碼。

2、編碼無損轉(zhuǎn)換

如果一個使用編碼A表示的字符X，轉(zhuǎn)化為編碼B的表示形式，而編碼B的字符集中并沒有X字符，則編碼轉(zhuǎn)換是有損的，否則編碼轉(zhuǎn)換就是無損的。
由于每個字符集所支持的字符數(shù)量是有限的，并且各個字符集涵蓋的字符之間存在差異。將UTF-8字符轉(zhuǎn)換為GBK字符時，MySQL內(nèi)部如果無法在GBK字符集找到一個UTF8字符集中的字符時，就會轉(zhuǎn)換成一個錯誤標記（0x3F，問號）。
編碼無損轉(zhuǎn)換的條件：
A、被轉(zhuǎn)換的字符是否同時在兩個字符集中。
B、目標字符集是否能夠?qū)Σ恢С肿址?，保留其原有表達形式。

參考博文：
http://cenalulu.github.io/