這篇文章介紹的是MySQL  5.6中的字符集，基本是我以前學(xué)習(xí)MySQL 5.6手冊時整理而來。

目前創(chuàng)新互聯(lián)已為上1000家的企業(yè)提供了網(wǎng)站建設(shè)、域名、虛擬空間、成都網(wǎng)站托管、企業(yè)網(wǎng)站設(shè)計、響水網(wǎng)站維護等服務(wù)，公司將堅持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。

 

概論

基礎(chǔ)概念

字符集(character set)是編碼和字符符號的映射集合。排序規(guī)則(collation)是用于比較字符集中字符的規(guī)則集。

 

現(xiàn)在我們自定義一個簡單的字符集character set。假設(shè)我們有一個僅有四個字母的字母表：A、B、a、b。我們給每個字母一個數(shù)字：A = 0，B = 1，a = 2，b = 3。字母A是一個字符符號，數(shù)字0是A的編碼，這四個字母和它們編碼的映射就是一個字符集。

假設(shè)我們想要比較兩個字符串的值：A和B。最簡單的辦法就是查看它們的編碼：A的編碼為0，B的編碼為1。因為0小于1，我們說A小于B。我們所做的就是應(yīng)用排序規(guī)則到字符集。這些規(guī)則的集合(在該示例中只有一條規(guī)則)就是排序規(guī)則collation：比較它們的編碼。當(dāng)然，現(xiàn)實中的字符集和排序規(guī)則要復(fù)雜地多，但基本理念是上面所說的那樣。

 

repertoire是指一個字符集中字符的集合。字符串表達(dá)式都有一個repertoire屬性，這個屬性可以有兩個值：

• ASCII：表達(dá)式只能包含Unicode編碼在U+0000到U+007F范圍內(nèi)的字符。

• UNICODE：表達(dá)式可以包含Unicode編碼在U+0000到U+10FFFF范圍內(nèi)的字符。這包括那些在Basic Multilingual Plane(BMP)范圍內(nèi)(U+0000 到 U+FFFF)的字符，和在BMP范圍外(U+01000 到 U+10FFFF)的增補字符。

 

ASCII范圍是UNICODE范圍的一個子集，因此，一個ASCII repertoire的字符串可以安全地轉(zhuǎn)換成UNICODE repertoire字符串的字符集(或任何包含ASCII范圍的字符集)，而不會有任何問題。從這里我們可以得到兩個結(jié)論：

1、特定的字符集擁有特定范圍的repertoire，這就限定了使用該字符集的表/字段也就只能使用特定范圍的字符，超出范圍的字符將不會被那個字符集所支持。比如，ascii字符集的repertoire范圍就是ASCII，因此，若使用該字符集，就只能使用Unicode編碼在U+0000到U+007F范圍內(nèi)的字符。

2、字符子集的字符集可以安全地轉(zhuǎn)換成它的超集的字符集，而不會有任何問題。這點在不同字符集混用時很有用，MySQL可以完成自動轉(zhuǎn)換。但是，反過來是不行的。

 

MySQL中的的常見字符集：

• utf8字符集：是一種UTF-8編碼的Unicode字符集，每個字符占用1到3個字節(jié)。只能覆蓋BMP范圍內(nèi)的字符，其中不單單有英文字符，也包括中文字符等。

• utf8mb4字符集：是一種UTF-8編碼的Unicode字符集，每個字符占用1到4個字節(jié)。可以覆蓋BMP范圍內(nèi)的字符和增補字符。BMP范圍內(nèi)的字符編碼和utf8字符集中的編碼是完全相同的，長度也是完全一樣的，所以utf8mb4字符集可以兼容utf8字符集。

 

元數(shù)據(jù)的字符集

元數(shù)據(jù)(Metadata)是"關(guān)于數(shù)據(jù)的數(shù)據(jù)"。任何描述數(shù)據(jù)庫的東西 — 與數(shù)據(jù)庫中的內(nèi)容相對，都是元數(shù)據(jù)。因此，列名、數(shù)據(jù)庫名、用戶名、版本名和SHOW命令的大多數(shù)字符串結(jié)果都是元數(shù)據(jù)。這對于information_schema庫中表的內(nèi)容也同樣正確，因為從定義上來說，那些表包含關(guān)于數(shù)據(jù)庫對象的信息。元數(shù)據(jù)的呈現(xiàn)必須滿足這些要求：

• 所有的元數(shù)據(jù)都必須使用相同的字符集。否則，對information_schema庫中的表執(zhí)行SHOW或SELECT命令將無法正常運行，因為這些操作返回的結(jié)果中，同一列的不同行將會在不同的字符集中。

• 元數(shù)據(jù)必須包含所有語言的所有字符。否則，用戶將無法使用它們自己本地的語言來被表或列命名。

為了滿足上面的要求，MySQL將元數(shù)據(jù)存儲在Unicode字符集中，準(zhǔn)確地說是UTF-8。只要你不使用方言或非拉丁字符，這不會有任何問題。但如果你使用了，你應(yīng)該意識到元數(shù)據(jù)是UTF-8字符集的。

 

MySQL將系統(tǒng)變量character_set_system的值設(shè)置為與元數(shù)據(jù)所使用的字符集的相同：

mysql> SHOW VARIABLES LIKE 'character_set_system'; +-----------------------+----------+ | Variable_name         |  Value    | +-----------------------+----------+ | character_set_system |  utf8   | +-----------------------+---------+

元數(shù)據(jù)的存儲使用Unicode并不意味著，服務(wù)器返回DESCRIBE函數(shù)的結(jié)果的列名時默認(rèn)會使用character_set_system變量所設(shè)置的字符集。當(dāng)你使用SELECT column1 FROM t命令時，服務(wù)器返回給客戶端的column1這個列名自身的字符集，是由系統(tǒng)變量character_set_results的值決定的，默認(rèn)值是latin1：

mysql> SHOW VARIABLES LIKE 'character_set_results'; +-----------------------+---------+ | Variable_name         |  Value   | +-----------------------+---------+ | character_set_results |  latin1  | +-----------------------+---------+

如果你想要服務(wù)器返回元數(shù)據(jù)給客戶端時使用不同的字符集，就使用SET NAMES'character_set' 命令來強制服務(wù)器將當(dāng)前字符集轉(zhuǎn)換成指定的character_set，該命令會自動設(shè)置character_set_results等相關(guān)的系統(tǒng)變量，僅對當(dāng)前會話生效。如果character_set_results被設(shè)為NULL，服務(wù)器返回元數(shù)據(jù)時將不進(jìn)行轉(zhuǎn)換，而使用它原本的字符集(即變量character_set_system所指示的字符集)。

另外，客戶端程序也可以在接收到服務(wù)器返回的數(shù)據(jù)后執(zhí)行字符集轉(zhuǎn)換。在客戶端執(zhí)行字符集轉(zhuǎn)換效率更高，但并不是所有客戶端都支持這個功能。

 

從上面的說明可以看出，字符集問題不單單影響數(shù)據(jù)存儲，還會影響客戶端與MySQL服務(wù)器之間的通信。如果你想要讓客戶端程序使用與默認(rèn)字符集不相同的字符集與服務(wù)器端通信，你需要指明哪一個。比如，要使用 utf8 字符集，執(zhí)行命令：

mysql> SET NAMES 'utf8';

當(dāng)然，因為還沒有再MySQL配置文件中設(shè)置相關(guān)變量，所以這里的設(shè)置并不是持久的。僅在當(dāng)前會話中生效。

 

存儲數(shù)據(jù)時的字符集

MySQL支持在MySQL服務(wù)器(server)、數(shù)據(jù)庫(database)、表(table)和列(column)四個級別指定所使用的字符集。MySQL支持對MyISAM、MEMORY和InnoDB存儲引擎配置字符集。

 

1、 MySQL服務(wù)器有一個服務(wù)器字符集和服務(wù)器排序規(guī)則，分別由變量character_set_server和變量collation_server控制。你可以在MySQL配置文件中或MySQL啟動選項中設(shè)置這兩個選項的值，也支持使用set命令進(jìn)行動態(tài)修改，有全局值和會話值兩種。character_set_server的默認(rèn)值是latin1字符集，collation_server的默認(rèn)值是latin1_swedish_ci排序規(guī)則。如果你只指定了字符集，而沒有指定排序規(guī)則，那么系統(tǒng)會自動將排序規(guī)則設(shè)置為該字符集的默認(rèn)排序規(guī)則。比如，如果你將character_set_server的值設(shè)置為utf8mb4字符集而沒有設(shè)置collation_server的值，那么collation_server的值會自動變?yōu)閡tf8mb4_general_ci排序規(guī)則，因為utf8mb4_general_ci是utf8mb4字符集的默認(rèn)排序規(guī)則。

[root@gw ~]# vim /usr/my.cnf [mysqld] character-set-server=utf8mb4 collation-server=utf8mb4_general_ci

在使用CREATE DATABASE命令創(chuàng)建數(shù)據(jù)庫時如果沒有指定數(shù)據(jù)庫字符集和排序規(guī)則， 那么服務(wù)器端字符集和排序規(guī)則就會作為默認(rèn)值，它們的用途僅在于這里。因此可以說，服務(wù)器端字符集和排序規(guī)則是MySQL數(shù)據(jù)庫中數(shù)據(jù)(除元數(shù)據(jù)外)的可能的默認(rèn)值。

 

2、 MySQL中的每一個庫都有一個數(shù)據(jù)庫字符集和數(shù)據(jù)庫排序規(guī)則。CREATE DATABASE 和 ALTER DATABASE語句都有選項可以指定數(shù)據(jù)庫字符集和排序規(guī)則：

CREATE DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] [[DEFAULT] COLLATE collation_name]   ALTER DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] [[DEFAULT] COLLATE collation_name]

MySQL按照下面的方式?jīng)Q定數(shù)據(jù)庫的字符集和排序規(guī)則：

• 如果CHARACTER SETcharset_name和COLLATEcollation_name都指定了，那么數(shù)據(jù)庫字符集和排序規(guī)則就是所指定的charset_name和collation_name。

• 如果只指定了CHARACTER SETcharset_name而沒有指定COLLATE，那么數(shù)據(jù)庫字符集和排序規(guī)則就是所指定的charset_name和該字符集默認(rèn)的排序規(guī)則。

• 如果只指定了COLLATEcollation_name而沒有指定CHARACTER SET，那么數(shù)據(jù)庫字符集和排序規(guī)則就是該collation_name相關(guān)聯(lián)的字符集和所指定的collation_name。

• 如果CHARACTER SETcharset_name和COLLATEcollation_name都沒有指定，那么數(shù)據(jù)庫字符集和排序規(guī)則就使用MySQL服務(wù)器字符集和排序規(guī)則（見上一小節(jié)）。

數(shù)據(jù)庫默認(rèn)字符集和排序規(guī)則可以從character_set_database 和 collation_database這兩個系統(tǒng)變量得知。要查看指定數(shù)據(jù)庫的默認(rèn)字符集和排序規(guī)則，使用命令：

mysql> use db_name; mysql> SELECT @@character_set_database, @@collation_database;

也可以使用下面的命令：

mysql> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';

數(shù)據(jù)庫字符集和排序規(guī)則影響服務(wù)器運行的這些方面：

• 對于CREATE TABLE語句，如果創(chuàng)建表時未顯式指定字符集和排序規(guī)則，數(shù)據(jù)庫字符集和排序規(guī)則被用做表的默認(rèn)字符集和排序規(guī)則。要覆蓋該行為，顯式使用CHARACTER SET 和 COLLATE選項。

• 對于不包括CHARACTER SET選項的LOAD DATA語句，服務(wù)器使用變量character_set_database所指示的字符集來解析文件中的信息。要覆蓋該行為，顯式使用CHARACTER SET選項。

• 對于存儲的程序(過程和函數(shù))，在創(chuàng)建程序時如果字符數(shù)據(jù)參數(shù)(character data parameters)的聲明未使用CHARACTER SET 和 COLLATE選項，那么數(shù)據(jù)庫字符集和排序規(guī)則會用做字符數(shù)據(jù)參數(shù)的字符集和排序規(guī)則。要覆蓋該行為，顯式使用CHARACTER SET 和 COLLATE選項。

 

3、 每個表都有一個表字符集和表排序規(guī)則。CREATE TABLE 和 ALTER TABLE語句都有選項可以指定表字符集和排序規(guī)則：

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]

示例：

CREATE TABLE t1 ( ... ) CHARACTER SET latin1 COLLATE latin1_danish_ci;

MySQL按照下面的方式?jīng)Q定表的字符集和排序規(guī)則：

• 如果CHARACTER SETcharset_name和COLLATEcollation_name都指定了，那么表字符集和排序規(guī)則就是所指定的charset_name和collation_name。

• 如果只指定了CHARACTER SETcharset_name而沒有指定COLLATE，那么表字符集和排序規(guī)則就是所指定的charset_name和該字符集默認(rèn)的排序規(guī)則。

• 如果只指定了COLLATEcollation_name而沒有指定CHARACTER SET，那么表字符集和排序規(guī)則就是該collation_name相關(guān)聯(lián)的字符集和所指定的collation_name。

• 如果CHARACTER SETcharset_name和COLLATEcollation_name都沒有指定，那么表字符集和排序規(guī)則就使用數(shù)據(jù)庫字符集和排序規(guī)則（見上一小節(jié)）。

表字符集和排序規(guī)則被用作列定義中的默認(rèn)值，如果在單個的列定義中沒有指定列字符集和排序規(guī)則。表字符集和排序規(guī)則是MySQL的擴展，并不是標(biāo)準(zhǔn)SQL中的東西。

 

4、 每一個"字符"列（即是，CHAR、VARCHAR或TEXT類型的列）都有一個列字符集和列排序規(guī)則。CREATE TABLE 和 ALTER TABLE語句中都有選項可以指定列字符集和排序規(guī)則：

col_name {CHAR | VARCHAR | TEXT} (col_length) [CHARACTER SET charset_name] [COLLATE collation_name]

ENUM和SET類型的列中也有選項：

col_name {ENUM | SET} (val_list) [CHARACTER SET charset_name] [COLLATE collation_name]

示例：

CREATE TABLE t1 ( col1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci );   ALTER TABLE t1 MODIFY col1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_swedish_ci;

MySQL按照下面的方式?jīng)Q定列的字符集和排序規(guī)則：

• 如果CHARACTER SETcharset_name和COLLATEcollation_name都指定了，那么列字符集和排序規(guī)則就是所指定的charset_name和collation_name。

• 如果只指定了CHARACTER SETcharset_name而沒有指定COLLATE，那么列字符集和排序規(guī)則就是所指定的charset_name和該字符集默認(rèn)的排序規(guī)則。

• 如果只指定了COLLATEcollation_name而沒有指定CHARACTER SET，那么列字符集和排序規(guī)則就是該collation_name相關(guān)聯(lián)的字符集和所指定的collation_name。

• 如果CHARACTER SETcharset_name和COLLATEcollation_name都沒有指定，那么列字符集和排序規(guī)則就使用表字符集和排序規(guī)則（見上一小節(jié)）。

 

字符串字面量的字符集

除了前面所說的，每一個字符串字面量(string literal)都有一個字符集和排序規(guī)則。對于簡單語句SELECT 'string'，這里用到的string有一個連接默認(rèn)字符集和排序規(guī)則(connection default character set and collation)，分別由系統(tǒng)變量character_set_connection和collation_connection控制。一個字符串字面量可能會有一個可選的字符集introducer和COLLATE子句，來將它指定為使用特定字符集和排序規(guī)則的字符串：

[_charset_name]'string' [COLLATE collation_name]

示例：

SELECT 'abc'; SELECT _latin1'abc'; SELECT _binary'abc'; SELECT _utf8'abc' COLLATE utf8_danish_ci;

_charset_name這個表達(dá)式的正式叫法叫做introducer。它告訴解析器：它后面所接的字符串使用字符集charset_name。MySQL按照下面的方式?jīng)Q定字符串字面量的字符集和排序規(guī)則：

• 如果_charset_name和COLLATEcollation_name都指定了，那么字符串字面量字符集和排序規(guī)則就是所指定的charset_name和collation_name。

• 如果只指定了_charset_name而沒有指定COLLATE，那么字符串字面量字符集和排序規(guī)則就是所指定的charset_name和該字符集默認(rèn)的排序規(guī)則。

• 如果只指定了COLLATEcollation_name而沒有指定_charset_name，那么字符串字面量字符集和排序規(guī)則就是系統(tǒng)變量character_set_connection所指定的連接默認(rèn)字符集和所指定的collation_name。collation_name必須是連接默認(rèn)字符集的排序規(guī)則之一。

• 如果_charset_name和COLLATEcollation_name都沒有指定，那么字符串字面量字符集和排序規(guī)則就使用系統(tǒng)變量character_set_connection和collation_connection所指定的連接默認(rèn)字符集和排序規(guī)則。

 

連接相關(guān)的字符集

每一個客戶端都有一個連接相關(guān)的(connection-related)字符集和排序規(guī)則。一個"connection"就是指當(dāng)你連接到MySQL服務(wù)器時的連接?？蛻舳税l(fā)送SQL語句，比如查詢，是通過到服務(wù)器的連接。服務(wù)器返回數(shù)據(jù)，比如查詢結(jié)果或錯誤信息，是通過到客戶端的連接。這導(dǎo)致了下面幾個與客戶端連接相關(guān)的字符集和排序規(guī)則的問題：

1、 當(dāng)它離開客戶端時語句的字符集是什么？

服務(wù)器會將character_set_client系統(tǒng)變量的值當(dāng)做是客戶端所發(fā)送的SQL語句的字符集。

 

2、 服務(wù)器在接收到SQL語句后，會將它轉(zhuǎn)換成什么字符集？

服務(wù)器會將客戶端所發(fā)送的SQL語句的字符集，從character_set_client轉(zhuǎn)換成character_set_connection所指向的字符集(除了那些有introducer的字符串字面量)。字符串字面量的比較則使用collation_connection所指向的排序規(guī)則。對于那些有列值的字符串的比較，則與collation_connection沒什么關(guān)系，因為列有它們自己的排序規(guī)則，那個排序規(guī)則的優(yōu)先級更高。

 

3、 服務(wù)器在將SQL查詢結(jié)果或錯誤信息發(fā)送回給客戶端之前會將它們轉(zhuǎn)換成什么字符集？

服務(wù)器會將SQL查詢結(jié)果的字符集轉(zhuǎn)換成系統(tǒng)變量character_set_results所指向的字符集。要轉(zhuǎn)換的數(shù)據(jù)包括列值和元數(shù)據(jù)(比如列名和錯誤信息)。

 

有兩個命令可以統(tǒng)一設(shè)置連接相關(guān)的字符集：

1、 SET NAMES命令

SET NAMES命令告訴MySQL服務(wù)器，客戶端使用的是什么字符集與它交互的。SET NAMES命令設(shè)置的值只會對當(dāng)前會話生效。語法格式：

SET NAMES 'charset_name' [COLLATE 'collation_name']

一條 SET NAMES 'charset_name' 語句(COLLATE 'collation_name'是可選的)實際上等同于下面的三條語句：

SET character_set_client = charset_name; SET character_set_results = charset_name; SET character_set_connection = charset_name; // 這第三條語句也隱含了將變量collation_connection設(shè)置為charset_name字符集默認(rèn)的排序規(guī)則

2、 SET CHARACTER SET命令

SET CHARACTER SET命令類似于SET NAMES，但將character_set_connection和collation_connection設(shè)置為character_set_database和collation_database。語法格式：

SET CHARACTER SET charset_name;

一條 SET CHARACTER SET charset_name 命令實際上等同于下面的三條語句：

SET character_set_client = charset_name; SET character_set_results = charset_name; SET collation_connection = @@collation_database; // 這第三條語句也隱含了執(zhí)行命令SET character_set_connection = @@character_set_database;

 

對于MySQL客戶端程序mysql、mysqladmin、mysqlcheck、mysqlimport和mysqlshow，它們是按照下面的規(guī)則來決定與MySQL服務(wù)器端通信時所使用的字符集的：

1、 在缺少其它信息的情況下，它們會使用預(yù)設(shè)的默認(rèn)字符集(通常是latin1)來與MySQL服務(wù)器端通信。這個預(yù)設(shè)的默認(rèn)字符集也可以在MySQL配置文件中使用下面選項指定：

[root@gw ~]# vim /usr/my.cnf [client] default-character-set=utf8mb4

2、 程序可以自動檢測要使用哪一個字符集，基于操作系統(tǒng)設(shè)置，比如LANG或LC_ALL環(huán)境變量的值。比如，如果操作系統(tǒng)中LANG環(huán)境變量的值為ru_RU.KOI8-R，會使得這些客戶端程序變成使用koi8r字符集。這一點會比第1點優(yōu)先。

3、 這些程序支持使用選項 --default-character-set 來讓用戶顯式指定字符集，以覆蓋程序自動決定的字符集。這一點會比第1、2點都優(yōu)先。

 

如果你想要服務(wù)器在返回SQL查詢結(jié)果或錯誤信息給客戶端時不執(zhí)行字符集轉(zhuǎn)換，可以將character_set_results變量設(shè)為NULL或binary：

SET character_set_results = NULL;

 

查看字符集

要顯示MySQL中所支持的所有字符集，可以查看information_schema.character_sets表或使用show character set命令，后面可以加上 LIKE 或 WHERE子句進(jìn)行過濾：

mysql> SHOW CHARACTER SET; mysql> select * from information_schema.character_sets;

 

一個給定的字符集至少會有一個排序規(guī)則。大多數(shù)的字符集支持多個排序規(guī)則，其中一個是默認(rèn)的。要顯示字符集所支持的所有排序規(guī)則，可以查看information_schema.collations表或使用show collation命令，后面可以加上 LIKE 或 WHERE子句進(jìn)行過濾：

mysql> SHOW COLLATION; mysql> select * from information_schema.collations;

 

如果要使用所有跟字符集和排序規(guī)則相關(guān)的系統(tǒng)變量，可以使用命令：

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character_set%' OR Variable_name LIKE 'collation%';

執(zhí)行后，可以看到有下面這些變量：

當(dāng)前題目：MySQL5.6中的字符集
瀏覽地址：http://jinyejixie.com/article16/ijghgg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供外貿(mào)建站、小程序開發(fā)、網(wǎng)站設(shè)計公司、App設(shè)計、搜索引擎優(yōu)化、域名注冊

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)