mysql字符集(character sets)是指一系列符號(hào)以及符號(hào)對(duì)應(yīng)的編碼的集合，比如英文字母可以用ASCII編碼，中文可以用GBK或者UTF8編碼。校對(duì)規(guī)則(collations)則是指一種比較字符的規(guī)則，這種比較規(guī)則決定了mysql如何進(jìn)行排序以及如何對(duì)字符比較大小。

mysql的character sets和collations有很多種，而且可以在多個(gè)維度去配置，包含服務(wù)器的配置和客戶端的配置，對(duì)于初學(xué)者往往容易搞混，有時(shí)候出了亂碼等問題也不知道怎么排查。今天筆者就詳細(xì)梳理一下mysql中的character sets和collations。

我們先來看看mysql都可以配置哪些字符集，輸入如下命令查看。

show variables like '%character%';

?可以看到，有多達(dá)7個(gè)的character_set相關(guān)的參數(shù)，接下來我們就詳細(xì)說說這些參數(shù)。

查看mysql中支持的字符集和校對(duì)規(guī)則?

mysql中每個(gè)字符集都會(huì)對(duì)應(yīng)多個(gè)校對(duì)規(guī)則，是一對(duì)多的關(guān)系。比如utf8對(duì)應(yīng)的collation有utf8_general_ci，utf8_bin，utf8_unicode_ci等。而且每個(gè)character set會(huì)有個(gè)默認(rèn)的collation與之對(duì)應(yīng)，當(dāng)我們?cè)趧?chuàng)建數(shù)據(jù)庫或者創(chuàng)建表時(shí)如果只指定character set，不指定collation，就會(huì)使用character set默認(rèn)的collation。collation的命名是以對(duì)應(yīng)的character set為開頭，比如collation為utf8_general_ci，我們就知道這個(gè)collation對(duì)應(yīng)的字符集是utf8。

查看字符集

2種方式可以查看mysql中支持哪些字符集

1.?通過INFORMATION_SCHEMA.CHARACTER_SETS表來查看

2.?通過SHOW CHARACTER SET來查看，和上面表的結(jié)果是一樣的。

查看collations

通過 show collation?查看校對(duì)規(guī)則有哪些，還可在語句后面加where條件篩選。

配置與查看字符集與校對(duì)規(guī)則

服務(wù)器端相關(guān)character set和collation

mysql服務(wù)器端相關(guān)字符集是服務(wù)器對(duì)數(shù)據(jù)的存儲(chǔ)等相關(guān)的字符集，不涉及客戶端的問題。

mysql服務(wù)器端的字符集和校對(duì)規(guī)則可以在四個(gè)級(jí)別指定：server, database, table, column

server級(jí)別

在mysql5.7中，server character set 和 server collation 默認(rèn)為 latin1 和 latin1_swedish_ci，在mysql8中，默認(rèn)為utf8mb4 和 utf8mb4_0900_ai_ci

如果想要修改server級(jí)別的character set和collation，可以在啟動(dòng)時(shí)指定參數(shù)

?mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_0900_ai_ci

或者將上面的參數(shù)寫入到my.ini中[mysqld]下面，如下圖。

server級(jí)別的character set和collation的作用是為創(chuàng)建數(shù)據(jù)庫(CREAT DATABASE)時(shí)指定默認(rèn)字符集和校對(duì)規(guī)則。也就是說創(chuàng)建數(shù)據(jù)庫時(shí)如果沒有指定character set和collation，就默認(rèn)使用server級(jí)別的。

server級(jí)別的character set 和 collation可通過如下命令查看

show variables like 'character_set_server' show variables like 'collation_server'

?

database級(jí)別

數(shù)據(jù)庫級(jí)別的character set和collation可以在創(chuàng)建和修改數(shù)據(jù)庫時(shí)指定，如果不指定，則使用server級(jí)別的。

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]舉例：create database demo character set utf8 collate utf8_general_ci;

數(shù)據(jù)庫的character set和collation可通過INFORMATION_SCHEMA.SCHEMATA表查看。

比如下圖，我創(chuàng)建了三個(gè)數(shù)據(jù)庫test, test2, test3，test數(shù)據(jù)庫的character set和collation分別為utf8和utf8_general_ci，而test2和test3數(shù)據(jù)庫的character set和collation分別為latin1和latin1_swedish_ci。

也可以通過如下方式查看

USE db_name; SELECT @@character_set_database, @@collation_database;

數(shù)據(jù)庫級(jí)別的character set和collation會(huì)影響以下行為：

1.?創(chuàng)建表時(shí)，會(huì)默認(rèn)使用database的character set和collation。

2.?使用mysql的LOAD DATA加載數(shù)據(jù)時(shí)，默認(rèn)采用database的character set和collation。

3.?作用于存儲(chǔ)過程和函數(shù)，在創(chuàng)建存儲(chǔ)過程和函數(shù)時(shí)，傳遞的參數(shù)默認(rèn)采用database的character set。

比如我的database?character set為latin1，然后我創(chuàng)建一個(gè)存儲(chǔ)過程proc1(nameStr varchar(20))，當(dāng)我調(diào)用存儲(chǔ)過程call proc1('張三')時(shí)，會(huì)報(bào)錯(cuò)，因?yàn)榇鎯?chǔ)過程proc1的參數(shù)只支持latin1字符集。

table級(jí)別

table的character set和collation可在創(chuàng)建表時(shí)指定，不指定默認(rèn)采用database級(jí)別的。

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]

table級(jí)別的character set和collation會(huì)影響column級(jí)別的character set和collation，也就是column如果不指定character set和collation，就會(huì)默認(rèn)繼承table級(jí)別的。

要想查看表的character set和collation，可通過information_schema.TABLES表查看，不過這個(gè)表只保存了collation信息，但是通過collation我們就能知道character set是什么了。

?或者通過如下命令查看

column級(jí)別

指定column級(jí)別的character set和collation，只有column的類型為字符型，如char,varchar,text等時(shí)，才可指定character set和collation。

col_name {CHAR | VARCHAR | TEXT} (col_length)[CHARACTER SET charset_name][COLLATE collation_name]舉例 CREATE TABLE t1 (col1 VARCHAR(5)CHARACTER SET latin1COLLATE latin1_german1_ci );ALTER TABLE t1 MODIFYcol1 VARCHAR(5)CHARACTER SET latin1COLLATE latin1_swedish_ci;

要查看列的character set和collation，可通過information_schema.COLUMNS表查看。

客戶端相關(guān)character set和collation

客戶端可能和mysql服務(wù)器采用不同的字符集，比如我們的java應(yīng)用用的是一套字符集，mysql服務(wù)器用的是另一套字符集，這種情況下就要指定客戶端和服務(wù)器連接交互時(shí)的字符集。和客戶端相關(guān)的字符集配置有3個(gè)參數(shù)：character_set_client、character_set_connection、character_set_results。

這3個(gè)參數(shù)都是session級(jí)別的，也就是說不同的客戶端連接時(shí)可以把這3個(gè)參數(shù)指定為不同的值，而且在客戶端和服務(wù)器連接建立成功后，可以動(dòng)態(tài)修改這3個(gè)值，不需要重啟mysql。

參數(shù)作用

mysql服務(wù)器把從客戶端接收的數(shù)據(jù)從character_set_client 轉(zhuǎn)成?character_set_connection，然后進(jìn)行后續(xù)處理。把查詢結(jié)果轉(zhuǎn)成character_set_results返回給客戶端。所以character_set_client和character_set_connection是在向mysql發(fā)送命令的時(shí)候起作用，character_set_results是在接收mysql數(shù)據(jù)時(shí)起作用。

參數(shù)查看與設(shè)置

在連接mysql成功后，通過如下2種方式查看這三個(gè)參數(shù)的值。

方式一：

SELECT * FROM performance_schema.session_variables WHERE VARIABLE_NAME IN ( 'character_set_client', 'character_set_connection', 'character_set_results', 'collation_connection' ) ORDER BY VARIABLE_NAME;

方式二：

SHOW SESSION VARIABLES LIKE 'character\_set\_%'; SHOW SESSION VARIABLES LIKE 'collation\_%';

設(shè)置這三個(gè)參數(shù)的值有以下3種方式。

方式一：

在mysql客戶端連接時(shí)指定，可以配置在my.ini中，或者寫在命令行后面。

[mysql]

default-character-set=utf8

mysql -uroot -p?--default-character-set=utf8

方式二：

set names?charset_name?

這個(gè)命令等同于如下命令

SET character_set_client = charset_name;

SET character_set_results = charset_name;

SET?character_set_connection?=?charset_name;

方式三：

set character set charset_name，注意區(qū)分和set names的區(qū)別。這個(gè)命令會(huì)把character_set_connection設(shè)置為和database一樣，而不是指定的charset_name。

等于如下命令。

SET character_set_client = charset_name;

SET character_set_results = charset_name;

SET collation_connection = @@collation_database;

character_set_client、character_set_connection、character_set_results深入理解

一般我們把character_set_client、character_set_connection、character_set_results這三個(gè)值設(shè)置為一樣的比較好，這樣可以避免不必要的麻煩。但是具體這幾個(gè)參數(shù)怎樣在發(fā)揮作用，下面我們通過幾個(gè)例子來看一下。注意，下面的例子可能會(huì)比較容易把人繞暈，如果不感興趣，可以不看。上面講解的關(guān)于字符集和校對(duì)規(guī)則的知識(shí)應(yīng)該可以應(yīng)對(duì)日常開發(fā)了。

character_set_results參數(shù)作用示例

這里我們可以做個(gè)試驗(yàn)加深理解，我們先試驗(yàn)一下character_set_results。

我們建一個(gè)表，字符集設(shè)置為utf8，在表中插入一行數(shù)據(jù)。注意我這里插入數(shù)據(jù)是用的mysql 連接工具datagrip，沒有在cmd窗口中用mysql命令行，因?yàn)橛妹钚锌赡軙?huì)影響后面的分析結(jié)果。

create table demo (id int auto_increment,name varchar(20) null,constraint demo_pkprimary key (id) ) character set utf8; insert into demo(name) value ('gitcat熊');

然后我們打開cmd窗口，連接mysql服務(wù)器。可以看到，連接好后，默認(rèn)的character_set_client、character_set_connection、character_set_results都是gbk，這是因?yàn)閣indows系統(tǒng)默認(rèn)的編碼是gbk。

然后我們查詢數(shù)據(jù)看看。可以看到中文可以正常顯示。這是因?yàn)閙ysql server端demo表的編碼是utf8，但是character_set_results配置的是gbk，所以mysql在返回?cái)?shù)據(jù)之前把查出的數(shù)據(jù)轉(zhuǎn)成了gbk，又因?yàn)槲覀僣md窗口是以gbk編碼的，所以就正常顯示出了數(shù)據(jù)。?

我們把character_set_results設(shè)置成utf8再看看。可以看到顯示出問題了。因?yàn)槲覀兏嬖Vmysql server?character_set_results=utf8，所以mysql就會(huì)把查詢結(jié)果轉(zhuǎn)成utf8返回給我們，但是我們的cmd窗口是gbk編碼的，窗口會(huì)按照gbk解碼數(shù)據(jù)顯示，自然就顯示出問題了。?

我們可以驗(yàn)證一下。熊的utf8編碼為E7868A，我們把這個(gè)編碼轉(zhuǎn)成gbk看看，因?yàn)間bk是兩個(gè)字節(jié)為單位編碼，所以我們先查詢一下gbk編碼E786對(duì)應(yīng)的字是什么，從下圖可以看到正好是我們返回的select結(jié)果的第一個(gè)漢字“鐔”，然后再查一下8A，查不到可顯示的字符，因?yàn)镚BK的編碼范圍是在8140-FEFE，所以返回結(jié)果顯示了個(gè)“□”。?

接下來我們把當(dāng)前cmd窗口修改成用utf8編碼顯示，設(shè)置方式網(wǎng)上有很多，就是在cmd窗口執(zhí)行一下命令 chcp 65001，會(huì)打開一個(gè)新的窗口，我們?cè)谛麓翱谥匦逻B接mysql，可以看到，連接后的默認(rèn)編碼已經(jīng)變成utf8了。

這時(shí)我們?cè)俨樵円幌?#xff0c;可以看到可以正常顯示。

這時(shí)如果我們把character_set_results設(shè)置成gbk，反而不能正常顯示了。因?yàn)槲以O(shè)置character_set_results=gbk，mysql server就會(huì)認(rèn)為你要把查詢結(jié)果轉(zhuǎn)成gbk，mysql?server好心把結(jié)果給你轉(zhuǎn)成gbk了，結(jié)果你的窗口又以u(píng)tf8解碼來顯示，當(dāng)然就出問題了。 熊的gbk編碼是D0DC，Dü對(duì)應(yīng)的unicode，正好也是D0和DC，印證了mysql服務(wù)器確實(shí)把熊這個(gè)字轉(zhuǎn)成gbk編碼傳給我們了。

細(xì)心的網(wǎng)友可能會(huì)發(fā)現(xiàn)，這里有個(gè)問題，就是為什么cmd窗口是以u(píng)nicode解碼的，而不是utf8？因?yàn)槲覀兎祷氐臄?shù)據(jù)格式不符合utf8規(guī)則！

utf8編碼規(guī)則如下，可以看到，不是隨便給個(gè)2字節(jié)或者3字節(jié)的編碼，都能用utf8來解析出字符。所以猜測(cè)cmd窗口對(duì)于不能用utf8解析的編碼，只能以u(píng)nicode來解碼了。如果我們返回的編碼正好是符合utf8規(guī)則的，那么cmd窗口就會(huì)以u(píng)tf8正確解析出結(jié)果。

Unicode符號(hào)范圍 | UTF-8編碼方式 (十六進(jìn)制) | （二進(jìn)制） --------------------+--------------------------------------------- 0000 0000-0000 007F | 0xxxxxxx 0000 0080-0000 07FF | 110xxxxx 10xxxxxx 0000 0800-0000 FFFF | 1110xxxx 10xxxxxx 10xxxxxx 0001 0000-0010 FFFF | 11110xxx 10xxxxxx 10xxxxxx 10xxxxxx 以漢字“嚴(yán)”為例，演示如何實(shí)現(xiàn)UTF-8編碼。 已知“嚴(yán)”的unicode是4E25（100111000100101）， 根據(jù)上表，可以發(fā)現(xiàn)4E25處在第三行的范圍內(nèi)（0000 0800-0000 FFFF）， 因此“嚴(yán)”的UTF-8編碼需要三個(gè)字節(jié)，即格式是“1110xxxx 10xxxxxx 10xxxxxx”。 然后，從“嚴(yán)”的最后一個(gè)二進(jìn)制位開始，依次從后向前填入格式中的x，多出的位補(bǔ)0。 這樣就得到了，“嚴(yán)”的UTF-8編碼是“11100100 10111000 10100101”，轉(zhuǎn)換成十六進(jìn)制就是E4B8A5。

真的如我們猜測(cè)的這樣嗎？我們可以繼續(xù)做試驗(yàn)來看。請(qǐng)接著往下看，下面會(huì)更精彩！

我們可以來做個(gè)實(shí)驗(yàn)驗(yàn)證上面我們的猜想。還是保持我們上面的實(shí)驗(yàn)環(huán)境，即cmd窗口是utf8模式，設(shè)置character_set_results=gbk，然后我們?cè)跀?shù)據(jù)表demo中插入如下一條數(shù)據(jù)，注意我插入數(shù)據(jù)用的是datagrip，不是在當(dāng)前cmd窗口，因?yàn)楫?dāng)前cmd窗口我們修改了一些參數(shù)，直接在這里插入會(huì)影響我們分析。

insert into demo(name) values('鍦ㄥ悧');

然后查詢一下結(jié)果。

?

很神奇，竟然沒有"亂碼"，當(dāng)然其實(shí)是亂碼了，因?yàn)楹臀覀償?shù)據(jù)庫中存入的結(jié)果不一樣了。我們分析一下這里發(fā)生了什么。

首先mysql server從數(shù)據(jù)庫中查出"鍦ㄥ悧"這3個(gè)字，然后發(fā)現(xiàn)character_set_results=gbk，好吧，那mysql server就按照要求，把這3個(gè)字轉(zhuǎn)成gbk格式發(fā)送給客戶端，這3個(gè)字對(duì)應(yīng)的gbk編碼是E59C(鍦)A8E5(ㄥ)9097(悧)，現(xiàn)在cmd窗口接收到這個(gè)編碼了，要顯示在屏幕上了，因?yàn)閏md窗口設(shè)置的是utf8編碼，所以就以u(píng)tf8方式來解析收到的編碼E59CA8E59097，而這一串編碼正好對(duì)應(yīng)utf8中的"在嗎"。這也印證了我們之前的猜想，如果server傳回來的數(shù)據(jù)是符合utf8編碼規(guī)則的，cmd窗口就會(huì)以u(píng)tf8規(guī)則幫我們解碼出數(shù)據(jù)顯示。

character_set_client、character_set_connection參數(shù)作用

接下來我們?cè)囼?yàn)一下character_set_client、character_set_connection這兩個(gè)參數(shù)的設(shè)置對(duì)數(shù)據(jù)的影響。前面說了，server把從客戶端接收的數(shù)據(jù)從character_set_client 轉(zhuǎn)成character_set_connection，比如我們發(fā)送了一條insert語句，mysql server就會(huì)以為，你發(fā)送的語句是用character_set_client編碼的，但是我要轉(zhuǎn)成character_set_connection，然后再執(zhí)行插入語句。

我們還是先以默認(rèn)方式連接mysql數(shù)據(jù)庫，如下。

然后在我們上面建的demo表里插入一條數(shù)據(jù)。可以看到，數(shù)據(jù)被正確地插入和讀出。因?yàn)槲覀儺?dāng)前窗口的編碼是gbk，而設(shè)置的character_set_client和character_set_connection也是gbk，所以mysql就會(huì)把我們的數(shù)據(jù)從gbk轉(zhuǎn)成gbk，再存入數(shù)據(jù)庫，當(dāng)然就沒問題了。當(dāng)然存入數(shù)據(jù)庫時(shí)發(fā)現(xiàn)表的編碼是utf8，所以其實(shí)這里又轉(zhuǎn)成utf8存入數(shù)據(jù)庫的，這一步和character_set_client和character_set_connection就沒關(guān)系了，我們先不深究。

接下來繼續(xù)試驗(yàn)，這里只說一下結(jié)果，就不截圖了。

cmd窗口為gbk編碼時(shí)的情況

1. 如果cmd窗口是gbk編碼，character_set_client是gbk，character_set_connection是utf8，插入和查詢也沒問題。

2. 如果cmd窗口是gbk編碼，character_set_client是utf8，character_set_connection是utf8，插入無法插入，會(huì)報(bào)錯(cuò)如下。

我們來分析一下這種情況，因?yàn)楫?dāng)前窗口是gbk編碼，所以傳給server的編碼是gbk的，但是因?yàn)閏haracter_set_client=utf8，server誤以為是utf8的，按照utf8來解碼，發(fā)現(xiàn)和utf8編碼規(guī)則不符合，所以報(bào)錯(cuò)了。如果我們傳給server的gbk編碼正好也符合utf8編碼，server是不會(huì)報(bào)錯(cuò)的，會(huì)插入成功，只是插入的數(shù)據(jù)不是我們預(yù)期的。比如下圖的例子，我們還是以“鍦ㄥ悧”這三個(gè)字舉例，如前所述，“鍦ㄥ悧”這三個(gè)字的gbk編碼是E59C(鍦)A8E5(ㄥ)9097(悧)，而這個(gè)編碼正好也符合的utf8的編碼，server在拿到這個(gè)編碼后，按照utf8的規(guī)則解碼可以解碼成功，并且解碼出來是“在嗎”，于是就不會(huì)報(bào)錯(cuò)了。

?

?3. 如果cmd窗口是gbk編碼，character_set_client是utf8，character_set_connection是gbk，這種情況可以插入數(shù)據(jù)，但是插入的數(shù)據(jù)不對(duì)。

?因?yàn)槲覀僩bk編碼的熊字不能正確被解碼為utf8，所以在解碼為utf8時(shí)就已經(jīng)亂碼了，后面所有的操作都會(huì)是亂碼的，所以數(shù)據(jù)是不對(duì)的。

cmd窗口為utf8編碼時(shí)的情況

1. 如果cmd窗口是utf8編碼，character_set_client和character_set_connection設(shè)置成utf8，毫無疑問，這種情況肯定是沒問題的，因?yàn)槲覀冇玫木幋a都一樣。

2.?如果cmd窗口是utf8編碼，character_set_client=gbk和character_set_connection=utf8，結(jié)果如下，可以看到utf8編碼的熊為E7868A，E786正好對(duì)應(yīng)gbk的"鐔"，說明server是按照gbk解碼了utf8編碼的數(shù)據(jù)。

下面附一張?jiān)谇闆r2這種情況下的另一個(gè)插入語句，有了前面我們的分析，你能明白結(jié)果為什么是這樣了嗎？

3.?如果cmd窗口是utf8編碼，character_set_client=gbk和character_set_connection=gbk，2次插入語句結(jié)果分別如下。

4.?如果cmd窗口是utf8編碼，character_set_client=utf8和character_set_connection=gbk，結(jié)果如下。

?情況3和情況4大家可以根據(jù)前面的分析自己分析一下結(jié)果，看看是否理解了。

總結(jié)

以上是生活随笔為你收集整理的mysql字符集和校对规则(character sets and collations)详解的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。