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

一、字符集與編碼

1、字符集簡(jiǎn)介

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

創(chuàng)新互聯(lián)公司專注于連平網(wǎng)站建設(shè)服務(wù)及定制，我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗(yàn)。 熱誠(chéng)為您提供連平營(yíng)銷型網(wǎng)站建設(shè)，連平網(wǎng)站制作、連平網(wǎng)頁(yè)設(shè)計(jì)、連平網(wǎng)站官網(wǎng)定制、小程序設(shè)計(jì)服務(wù)，打造連平網(wǎng)絡(luò)公司原創(chuàng)品牌,更為您提供連平網(wǎng)站排名全網(wǎng)營(yíng)銷落地服務(wù)。

2、ASCII編碼

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

3、Latin1字符集

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

4、UTF-8編碼

UTF-8（8-bit Unicode Transformation Format）是一種針對(duì)Unicode的可變長(zhǎng)度字符編碼，又稱萬(wàn)國(guó)碼。由Ken Thompson于1992年創(chuàng)建。現(xiàn)在已經(jīng)標(biāo)準(zhǔn)化為RFC 3629。UTF-8用1到6個(gè)字節(jié)編碼Unicode字符。
UTF-8是一種變長(zhǎng)字節(jié)編碼方式。對(duì)于某一個(gè)字符的UTF-8編碼，如果只有一個(gè)字節(jié)則其最高二進(jìn)制位為0；如果是多字節(jié)，其第一個(gè)字節(jié)從最高位開(kāi)始，連續(xù)的二進(jìn)制位值為1的個(gè)數(shù)決定了其編碼的位數(shù)，其余各字節(jié)均以10開(kāi)頭。UTF-8最多可用到6個(gè)字節(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中可以用來(lái)表示字符編碼的實(shí)際位數(shù)最多有31位，即上表中x所表示的位。除去控制位（每字節(jié)開(kāi)頭的10等），x表示的位與UNICODE編碼是一一對(duì)應(yīng)的，位高低順序也相同。
實(shí)際將UNICODE轉(zhuǎn)換為UTF-8編碼時(shí)應(yīng)先去除高位0，然后根據(jù)所剩編碼的位數(shù)決定所需最小的UTF-8編碼位數(shù)。 因此基本ASCII字符集中的字符（UNICODE兼容ASCII）只需要一個(gè)字節(jié)的UTF-8編碼（7個(gè)二進(jìn)制位）便可以表示。

5、字符集兼容性

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

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

二、MySQL字符集

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

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

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

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

2、MySQL字符集

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

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

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

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

A、MySQL服務(wù)端收到請(qǐng)求時(shí)將請(qǐng)求數(shù)據(jù)從character_set_client字符集轉(zhuǎn)換為character_set_connection字符集。
B、進(jìn)行內(nèi)部操作前將請(qǐng)求數(shù)據(jù)從character_set_connection字符集轉(zhuǎn)換為內(nèi)部操作字符集。確定步驟：
--使用每個(gè)數(shù)據(jù)字段的CHARACTER SET設(shè)定值；
--若上述值不存在，則使用對(duì)應(yīng)數(shù)據(jù)表的DEFAULT CHARACTER SET設(shè)定值；
--若上述值不存在，則使用對(duì)應(yīng)數(shù)據(jù)庫(kù)的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、存入和取出時(shí)對(duì)應(yīng)環(huán)節(jié)的編碼不一致。
B、如果兩個(gè)字符集之間無(wú)法進(jìn)行無(wú)損編碼轉(zhuǎn)換，一定會(huì)出現(xiàn)亂碼。

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

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

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