一文帶你了解MySQL字符集和比較規(guī)則
1. MySQL中的utf8和utf8mb4
在MySQL中，我經(jīng)常會(huì)看見(jiàn)utf8和utf8mb4這兩種編碼格式，但是這兩種格式我并不了解，一般創(chuàng)建數(shù)據(jù)庫(kù)的時(shí)候，如果讓我選，我默認(rèn)會(huì)選擇utf8，但是這個(gè)和UTF-8長(zhǎng)得最像。
但是實(shí)際上，這兩種是有區(qū)別的。
- utf8：utf8實(shí)際上是utf8mb3的別名，它是割版的UTF-8字符集，只使用1-3字節(jié)表示字符。
- utf8mb4：這個(gè)才是正宗的UTF-8字符集，使用1-4字節(jié)表示字符。
所以，如果想要在程序中存儲(chǔ)emoji表情的話，是需要使用utf8mb4字符集的，因?yàn)閑moji表情是需要使用4字節(jié)編碼一個(gè)字符。
在MySQL8.0中，已經(jīng)極大程度優(yōu)化了utf8mb4字符集的性能，并將其設(shè)置為默認(rèn)字符集
2. MySQL中的比較規(guī)則
比較規(guī)則是指對(duì)于某種字符集來(lái)說(shuō)，制定用來(lái)比較字符大小的多種規(guī)則，一種字符集多種規(guī)則，一個(gè)默認(rèn)規(guī)則。
在MySQL中，可以通過(guò)以下命令查看比較規(guī)則
show collation [like 匹配的模式];
這些命名是有規(guī)律的。
- 比較規(guī)則的名稱(chēng)與其關(guān)聯(lián)的字符集的名稱(chēng)開(kāi)頭，比如上述比較規(guī)則有些以u(píng)tf8mb3開(kāi)頭
- 后面緊接著該比較規(guī)則所應(yīng)用的語(yǔ)言。比如utf8mb3_roman_ci表示羅馬語(yǔ)的比較規(guī)則
- 名稱(chēng)的后綴意味這該比較規(guī)則是否區(qū)分語(yǔ)言中的重音、大小寫(xiě)等。
| 后綴 | 描述 |
|---|---|
| _ai | 不區(qū)分重音 |
| _as | 區(qū)分重音 |
| _ci | 不區(qū)分大小寫(xiě) |
| _cs | 區(qū)分大小寫(xiě) |
| _bin | 以二進(jìn)制方式進(jìn)行比較 |
3. 各級(jí)別的字符集和比較規(guī)則
在MySQL中有四個(gè)級(jí)別的字符集和比較規(guī)則。
- 服務(wù)器級(jí)別
- 數(shù)據(jù)庫(kù)級(jí)別
- 表級(jí)別
- 列級(jí)別
3.1 服務(wù)器級(jí)別
MySQL提供了兩種系統(tǒng)變量表示服務(wù)器級(jí)別的字符集和比較規(guī)則
| 系統(tǒng)變量 | 描述 |
|---|---|
| character_set_server | 服務(wù)器級(jí)別的字符集 |
| collation_server | 服務(wù)器級(jí)別的比較規(guī)則 |
可以通過(guò)SET語(yǔ)句來(lái)改變這兩個(gè)系統(tǒng)變量。
也可以通過(guò)修改配置文件來(lái)修改
3.2 數(shù)據(jù)庫(kù)級(jí)別
在創(chuàng)建和修改數(shù)據(jù)庫(kù)時(shí)候可以指定數(shù)據(jù)庫(kù)的字符集和比較規(guī)則
如果想要查看當(dāng)前數(shù)據(jù)庫(kù)的字符集和比較規(guī)則，可以查看下面兩個(gè)系統(tǒng)變量的值
| 系統(tǒng)變量 | 描述 |
|---|---|
| character_set_database | 當(dāng)前數(shù)據(jù)庫(kù)的字符集 |
| collation_database | 當(dāng)前數(shù)據(jù)庫(kù)的比較規(guī)則 |
需要注意的是，這兩個(gè)系統(tǒng)變量并不同與描述服務(wù)器級(jí)別的字符集和比較規(guī)則的那兩個(gè)比較規(guī)則，這兩個(gè)系統(tǒng)變量只是用來(lái)告訴用戶(hù)當(dāng)前數(shù)據(jù)庫(kù)的字符集和比較規(guī)則是什么，并不能通過(guò)修改兩個(gè)變量的值來(lái)修改檔期按數(shù)據(jù)庫(kù)的字符集和比較規(guī)則。
如果在創(chuàng)建數(shù)據(jù)庫(kù)的時(shí)候不指定字符集和比較規(guī)則，那么將會(huì)使用服務(wù)器級(jí)別的字符集和比較規(guī)則來(lái)作為數(shù)據(jù)庫(kù)的字符集和比較規(guī)則。
3.3 表級(jí)別
可以在創(chuàng)建和修改表的時(shí)候指定表的字符集和比較規(guī)則
如果創(chuàng)建表的語(yǔ)句沒(méi)有指定字符集和比較規(guī)則，則會(huì)使用該表所在數(shù)據(jù)庫(kù)的字符集和比較規(guī)則作為該表的字符集和比較規(guī)則。
3.4 列級(jí)別
可以在創(chuàng)建和修改列的時(shí)候指定該列的字符集和比較規(guī)則
對(duì)于某一列來(lái)說(shuō)，如果創(chuàng)建和修改表的語(yǔ)句沒(méi)有指定字符集和比較規(guī)則，則會(huì)使用該列所在的表的字符集和比較規(guī)則作為其字符集和比較規(guī)則。
注意：如果在修改列的字符集的時(shí)候，如果存儲(chǔ)的數(shù)據(jù)不能使用修改后的字符集進(jìn)行表示，則會(huì)報(bào)錯(cuò)
4. 修改字符集或比較規(guī)則
字符集和比較規(guī)則之間是相互聯(lián)系的，只修改其一需要遵循以下規(guī)則：
- 只修改字符集，則比較規(guī)則將變?yōu)樾薷暮蟮淖址哪J(rèn)比較規(guī)則。
- 只修改比較規(guī)則，則字符集將變?yōu)樾薷暮蟮谋容^規(guī)則對(duì)應(yīng)的字符集。
5. MySQL中的字符集轉(zhuǎn)換過(guò)程
在客戶(hù)端發(fā)送請(qǐng)求的時(shí)候，一般來(lái)說(shuō)，客戶(hù)端編碼請(qǐng)求字符串時(shí)使用的字符集與操作系統(tǒng)當(dāng)前使用的字符集一致。
但是如果MySQL客戶(hù)端設(shè)置了default-character-set啟動(dòng)選項(xiàng)的時(shí)候，那么服務(wù)器就會(huì)忽視操作系統(tǒng)當(dāng)前使用的字符集。
而在服務(wù)器接收請(qǐng)求的時(shí)候，服務(wù)器接收到的請(qǐng)求實(shí)際上就是一個(gè)字節(jié)序列。服務(wù)器會(huì)將這個(gè)字節(jié)序列看作是使用系統(tǒng)變量character_set_client表示的字符集編碼的字節(jié)序列。
注意的是，這個(gè)系統(tǒng)變量character_set_client是session級(jí)別的
客戶(hù)端發(fā)送請(qǐng)求的字符集和服務(wù)器接收請(qǐng)求的字符集是兩個(gè)獨(dú)立的字符集，一般來(lái)說(shuō)，客戶(hù)端發(fā)送請(qǐng)求所用字符集應(yīng)和服務(wù)器接收請(qǐng)求的字符集應(yīng)保持一致，但是也會(huì)出現(xiàn)不一致的時(shí)候
如果服務(wù)器的character_set_client對(duì)應(yīng)的字符集不能解釋請(qǐng)求的字節(jié)序列，那么服務(wù)器就會(huì)發(fā)出警告。
在服務(wù)器處理請(qǐng)求的時(shí)候，會(huì)將服務(wù)器接收請(qǐng)求的character_set_client對(duì)應(yīng)的字符集進(jìn)行編碼的字節(jié)序列轉(zhuǎn)換成session級(jí)別的系統(tǒng)變量character_set_connection對(duì)應(yīng)的字符集進(jìn)行編碼的字節(jié)序列。
對(duì)于character_set_connection我們可以通過(guò)SET命令單獨(dú)修改此系統(tǒng)變量，除了character_set_connection，還有一個(gè)與其對(duì)應(yīng)的系統(tǒng)變量collation_connection，這個(gè)系統(tǒng)變量表示了這些字符串應(yīng)使用哪種比較規(guī)則。
同樣這個(gè)系統(tǒng)變量collation_connection可以通過(guò)SET命令進(jìn)行修改。
如果客戶(hù)端發(fā)送請(qǐng)求、服務(wù)器接收請(qǐng)求和服務(wù)器處理請(qǐng)求這一過(guò)程中，字符集和比較規(guī)則不一樣，會(huì)出現(xiàn)什么情況呢？
就這個(gè)表來(lái)說(shuō)
向客戶(hù)端發(fā)送一條查詢(xún)語(yǔ)句
由于當(dāng)前操作系統(tǒng)使用的是gbk字符集，所以字符串‘我’是使用GBK字符集進(jìn)行編碼的，比較規(guī)則為gbk_chinese_ci，而列C使用的是utf8字符集編碼的，比較規(guī)則為utf8_general_ci。
那么在這種情況下，列的字符集和排序規(guī)則的優(yōu)先級(jí)更高，也就是說(shuō)這里需要將‘我’從GBK字符集轉(zhuǎn)換成utf8字符集，然后使用列c的比較規(guī)則進(jìn)行比較。
當(dāng)服務(wù)器生成響應(yīng)的時(shí)候，是不是直接將列c也就是utf8字符集進(jìn)行編碼的結(jié)果發(fā)送回客戶(hù)端呢？
答案是不一定，這取決與SESSION級(jí)別的系統(tǒng)變量character_set_results的值，結(jié)果會(huì)從utf8字符集編碼的轉(zhuǎn)換成
character_set_results對(duì)應(yīng)的字符編碼后的字節(jié)序列
| 系統(tǒng)變量 | 描述 |
|---|---|
| character_set_client | 服務(wù)器認(rèn)為請(qǐng)求是按照系統(tǒng)變量指定的字符集進(jìn)行編碼的 |
| character_set_connection | 服務(wù)器在處理請(qǐng)求時(shí)，會(huì)將請(qǐng)求字符序列從character_set_client轉(zhuǎn)換成character_set_connection |
| character_set_results | 服務(wù)器采用該系統(tǒng)變量指定的字符集對(duì)返回客戶(hù)端的字符串進(jìn)行編碼 |
由于三個(gè)變量均為SESSION級(jí)別，因此每個(gè)客戶(hù)端與服務(wù)器建立連接的時(shí)候都會(huì)維護(hù)這三個(gè)變量。
最后就是客戶(hù)端接收響應(yīng)了，響應(yīng)也就是一個(gè)字節(jié)序列，收到的字節(jié)序列會(huì)直接寫(xiě)進(jìn)黑框框中，再由黑框框?qū)⒆止?jié)序列接受成人類(lèi)能懂的字符（如果沒(méi)有特殊設(shè)置，一般使用操作系統(tǒng)當(dāng)前使用的字符集進(jìn)行解釋?zhuān)?/p>
參考：《MySQL是怎樣運(yùn)行的：從根兒上理解 MySQL》
總結(jié)
到此這篇關(guān)于MySQ字符集和比較規(guī)則的文章就介紹到這了,更多相關(guān)MySQ字符集和比較規(guī)則內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
本文介紹了在MySQL中使用ALTER?TABLE語(yǔ)句為表添加索引的幾種常見(jiàn)方式,包括普通索引、唯一索引、主鍵索引和全文索引,感興趣的可以了解一下2024-11-11
這篇文章主要給大家介紹了關(guān)于MySQL全面瓦解之查詢(xún)的正則匹配的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
本文主要介紹了MySQL索引失效之隱式轉(zhuǎn)換的問(wèn)題，文中通過(guò)示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-01-01
這篇文章主要介紹了MySQL自增主鍵,MySQL的自增主鍵并不一定是連續(xù)的,自增值的保存位置和修改機(jī)制決定了一種自增值不連續(xù)的場(chǎng)景,下面就來(lái)具體介紹一下,感興趣的可以了解一下2025-01-01
mysql5.7創(chuàng)建用戶(hù)授權(quán)刪除用戶(hù)撤銷(xiāo)授權(quán)
這篇文章主要介紹了mysql5.7創(chuàng)建用戶(hù)授權(quán)刪除用戶(hù)撤銷(xiāo)授權(quán)的方法,非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友可以參考下2017-02-02
文章介紹了通過(guò)SSH連接MySQL數(shù)據(jù)庫(kù)的方法,強(qiáng)調(diào)了確保MySQL默認(rèn)端口3306沒(méi)有被防火墻阻攔,以及在Ubuntu系統(tǒng)中默認(rèn)root的SSH是禁止的,應(yīng)使用其他賬戶(hù)登錄2025-02-02
mysql 存儲(chǔ)過(guò)程判斷重復(fù)的不插入數(shù)據(jù)
這篇文章主要介紹了下面是一個(gè)較常見(jiàn)的場(chǎng)景，判斷表中某列是否存在某值，如果存在執(zhí)行某操作,需要的朋友可以參考下2017-01-01
詳解MySQL中timestamp和datetime時(shí)區(qū)問(wèn)題導(dǎo)致做DTS遇到的坑
datetime和timestamp這兩種類(lèi)型都是用于表示年月日時(shí)分秒格式的數(shù)據(jù)，那么他們有那么區(qū)別，本文就詳細(xì)的介紹一下MySQL timestamp和datetime坑，文中通過(guò)示例代碼介紹的非常詳細(xì)，感興趣的小伙伴們可以參考一下2021-12-12
Mysql行轉(zhuǎn)列把逗號(hào)分隔的字段拆分成多行兩種方法
在數(shù)據(jù)庫(kù)中有時(shí)候我們需要將一些行數(shù)據(jù)轉(zhuǎn)換為列數(shù)據(jù),這在處理數(shù)據(jù)透視表、報(bào)表生成等場(chǎng)景下非常常見(jiàn),這篇文章主要給大家介紹了關(guān)于Mysql行轉(zhuǎn)列把逗號(hào)分隔的字段拆分成多行的兩種方法,需要的朋友可以參考下2024-05-05
Mysql8.0壓縮包安裝方法(詳細(xì)教程一步步安裝)
這篇文章主要給大家介紹了關(guān)于Mysql8.0壓縮包安裝方法,文中介紹的非常詳細(xì),Mysql安裝的時(shí)候可以有msi安裝和zip解壓縮兩種安裝方式,文中通過(guò)圖文介紹的非常詳細(xì),需要的朋友可以參考下2023-07-07