MySQL支持多種字符集(character set)提供用戶存儲數(shù)據(jù)，同時允許用不同排序規(guī)則(collation)做比較。下面基于MySQL5.7介紹一下字符集相關(guān)變量的使用。

一、字符集、字符序的概念與聯(lián)系

在數(shù)據(jù)的存儲上，MySQL提供了不同的字符集支持。而在數(shù)據(jù)的對比操作上，則提供了不同的字符序支持。MySQL提供了不同級別的設(shè)置，包括server級、database級、table級、column級，可以提供非常精準(zhǔn)的設(shè)置。

什么是字符集、字符序?簡單的來說：

• 字符集(character set)：定義了字符以及字符的編碼。
• 字符序(collation)：定義了字符的比較規(guī)則。

舉個例子：

有四個字符：A、B、a、b，這四個字符的編碼分別是A = 0, B = 1, a = 2, b = 3。這里的字符 + 編碼就構(gòu)成了字符集(character set)。

二、MySQL支持的字符集、字符序

MySQL支持多種字符集 與 字符序。

• 一個字符集對應(yīng)至少一種字符序(一般是1對多)。
• 兩個不同的字符集不能有相同的字符序。
• 每個字符集都有默認(rèn)的字符序。

1. 查看支持的字符集

可以通過以下方式查看MYSQL支持的字符集。

SHOW CHARACTER SET; 
select * from information_schema.HARACTER_SETS; 

2. 查看支持的字符序

可以通過如下方式查看MYSQL支持的字符序。

SHOW COLLATION WHERE Charset = 'utf8'; 
SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME="utf8"; 

三、系統(tǒng)的字符集(character_set_system)

character_set_system為元數(shù)據(jù)的字符集，即所有的元數(shù)據(jù)都使用同一個字符集。試想如果元數(shù)據(jù)采用不同字符集，INFORMATION_SCHEMA中的相關(guān)信息在不同行之間就很難展示。同時該字符集要能夠支持多種語言，方便不同語言人群使用自己的語言命名database、table、column。MySQL選擇UTF-8作為元數(shù)據(jù)編碼，用源碼固定。

查看system字符集：

select @@global.character_set_system; 

因為很少設(shè)定，所以就不做指定介紹了。

四、server的字符集、字符序(character_set_server/collation_server)

當(dāng)create database沒有指定charset/collation就會用character_set_server/collation_server，這兩個變量可以動態(tài)設(shè)置，有session/global級別。

在源碼中character_set_server/collation_server實際對應(yīng)一個變量，因為一個collation對應(yīng)著一個charset，所以源碼中只記錄CHARSET_INFO結(jié)構(gòu)的collation_server即可。當(dāng)修改character_set_server，會選擇對應(yīng)charset的默認(rèn)collation。對于其他同時有charset和collation的變量，源碼記錄也都是記錄collation。

character_set_server、collation_server分別對應(yīng)server字符集、server字符序。

1. 查看server字符集、字符序

分別對應(yīng)character_set_server、collation_server兩個系統(tǒng)變量。

SET GLOBAL SHOW_COMPATIBILITY_56=ON; 
SHOW VARIABLES LIKE "character_set_server";  
SHOW VARIABLES LIKE "collation_server"; 

2. 啟動服務(wù)時指定

可以在MySQL服務(wù)啟動時，指定server字符集、字符序。如不指定，默認(rèn)的字符序分別為latin1、latin1_swedish_ci

mysqld --character-set-server=latin1 --collation-server=latin1_swedish_ci 

3. 配置文件指定

除了在命令行參數(shù)里指定，也可以在配置文件里指定，如下所示。

[client] 
default-character-set=utf8 
[mysql] 
default-character-set=utf8 
[mysqld] 
collation-server = utf8_unicode_ci 
init-connect='SET NAMES utf8' 
character-set-server = utf8 

4. 運行時修改

例子：運行時修改(重啟后會失效，如果想要重啟后保持不變，需要寫進(jìn)配置文件里)

mysql> SET character_set_server = utf8 ; 

5. 編譯時指定默認(rèn)字符集、字符序

character_set_server、collation_server的默認(rèn)值，可以在MySQL編譯時，通過編譯選項指定：

cmake . -DDEFAULT_CHARSET=latin1 -DDEFAULT_COLLATION=latin1_german1_ci 

6. 實例

通過設(shè)置session中不同的character_set_server使創(chuàng)建database的默認(rèn)charset和collation不同。

set character_set_server='utf8'; 
create database d1; 
select * from information_schema.schemata where SCHEMA_NAME='d1'; 
set character_set_server='latin1'; 
create database d2; 
select * from SCHEMATA where SCHEMA_NAME='d2'; 

五、database的字符集、字符序(character_set_database/collation_database)

指定數(shù)據(jù)庫級別的字符集、字符序。同一個MySQL服務(wù)下的數(shù)據(jù)庫，可以分別指定不同的字符集/字符序。該變量值session級別表示當(dāng)前database的charset/collation，在后面的源碼版本中該變量可能修正為只讀，不建議修改該值。其global級別變量后面也會移除。

1. 設(shè)置數(shù)據(jù)的字符集/字符序

可以在創(chuàng)建、修改數(shù)據(jù)庫的時候，通過CHARACTER SET、COLLATE指定數(shù)據(jù)庫的字符集、排序規(guī)則。

-創(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] 

例子：創(chuàng)建數(shù)據(jù)庫test_schema，字符集設(shè)置為utf8，此時默認(rèn)的排序規(guī)則為utf8_general_ci。

CREATE DATABASE `test_schema` DEFAULT CHARACTER SET utf8; 

2. 查看數(shù)據(jù)庫的字符集/字符序

有3種方式可以查看數(shù)據(jù)庫的字符集/字符序。

查看test_schema的字符集、排序規(guī)則。(需要切換默認(rèn)數(shù)據(jù)庫)：

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

查看test_schema的字符集、數(shù)據(jù)庫(不需要切換默認(rèn)數(shù)據(jù)庫)：

mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME  
FROM information_schema.SCHEMATA WHERE schema_name="test_schema"; 

查看創(chuàng)建數(shù)據(jù)庫的語句，來查看字符集：

mysql> SHOW CREATE DATABASE test_schema; 

六、table的字符集、字符序

創(chuàng)建表、修改表的語法如下，可通過CHARACTER SET、COLLATE設(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] 

1. 創(chuàng)建table并指定字符集/字符序

指定字符集為utf8，字符序則采用默認(rèn)的。

CREATE TABLE `test_schema`.`test_table` ( 
 `id` INT NOT NULL COMMENT '', 
 PRIMARY KEY (`id`) COMMENT '') 
DEFAULT CHARACTER SET = utf8; 

2. 查看table的字符集/字符序

同樣，有3種方式可以查看table的字符集/字符序。

• 方式一：通過SHOW TABLE STATUS查看table狀態(tài)，注意Collation為utf8_general_ci，對應(yīng)的字符集為utf8。

 SHOW TABLE STATUS FROM test_schema \G; 

• 方式二：查看information_schema.TABLES的信息。

 USE test_schema; 
 SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE  

• 方式三：通過SHOW CREATE TABLE確認(rèn)。

 SHOW CREATE TABLE test_table; 

3. table字符集、字符序如何確定

假設(shè)CHARACTER SET、COLLATE的值分別是charset_name、collation_name。如果創(chuàng)建table時：

• 明確了charset_name、collation_name，則采用charset_name、collation_name。
• 只明確了charset_name，但collation_name未明確，則字符集采用charset_name，字符序采用charset_name對應(yīng)的默認(rèn)字符序。
• 只明確了collation_name，但charset_name未明確，則字符序采用collation_name，字符集采用collation_name關(guān)聯(lián)的字符集。
• charset_name、collation_name均未明確，則采用數(shù)據(jù)庫的字符集、字符序設(shè)置。

七、column的字符集、排序

類型為CHAR、VARCHAR、TEXT的列，可以指定字符集/字符序，語法如下：

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

1. 新增column并指定字符集/排序規(guī)則

例子如下：(創(chuàng)建table類似)

mysql> ALTER TABLE test_table ADD COLUMN char_column VARCHAR(25) CHARACTER SET utf8; 

2. 查看column的字符集/字符序

mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA="test_schema" AND TABLE_NAME="test_table" AND COLUMN_NAME="char_column"; 
+--------------------+-----------------+ 
| CHARACTER_SET_NAME | COLLATION_NAME | 
+--------------------+-----------------+ 
| utf8 | utf8_general_ci | 
+--------------------+-----------------+ 
1 row in set (0.00 sec) 

3. column字符集/排序規(guī)則確定

假設(shè)CHARACTER SET、COLLATE的值分別是charset_name、collation_name：

• 如果charset_name、collation_name均明確，則字符集、字符序以charset_name、collation_name為準(zhǔn)。
• 只明確了charset_name，collation_name未明確，則字符集為charset_name，字符序為charset_name的默認(rèn)字符序。
• 只明確了collation_name，charset_name未明確，則字符序為collation_name，字符集為collation_name關(guān)聯(lián)的字符集。
• charset_name、collation_name均未明確，則以table的字符集、字符序為準(zhǔn)。