一文帶你了解MySQL字符集和比較規(guī)則
1. MySQL中的utf8和utf8mb4
在MySQL中,我經(jīng)常會看見utf8和utf8mb4這兩種編碼格式,但是這兩種格式我并不了解,一般創(chuàng)建數(shù)據(jù)庫的時候,如果讓我選,我默認(rèn)會選擇utf8,但是這個和UTF-8長得最像。
但是實際上,這兩種是有區(qū)別的。
- utf8:utf8實際上是utf8mb3的別名,它是割版的UTF-8字符集,只使用1-3字節(jié)表示字符。
- utf8mb4:這個才是正宗的UTF-8字符集,使用1-4字節(jié)表示字符。
所以,如果想要在程序中存儲emoji表情的話,是需要使用utf8mb4字符集的,因為emoji表情是需要使用4字節(jié)編碼一個字符。
在MySQL8.0中,已經(jīng)極大程度優(yōu)化了utf8mb4字符集的性能,并將其設(shè)置為默認(rèn)字符集
2. MySQL中的比較規(guī)則
比較規(guī)則是指對于某種字符集來說,制定用來比較字符大小的多種規(guī)則,一種字符集多種規(guī)則,一個默認(rèn)規(guī)則。
在MySQL中,可以通過以下命令查看比較規(guī)則
show collation [like 匹配的模式];
這些命名是有規(guī)律的。
- 比較規(guī)則的名稱與其關(guān)聯(lián)的字符集的名稱開頭,比如上述比較規(guī)則有些以utf8mb3開頭
- 后面緊接著該比較規(guī)則所應(yīng)用的語言。比如utf8mb3_roman_ci表示羅馬語的比較規(guī)則
- 名稱的后綴意味這該比較規(guī)則是否區(qū)分語言中的重音、大小寫等。
后綴 | 描述 |
---|---|
_ai | 不區(qū)分重音 |
_as | 區(qū)分重音 |
_ci | 不區(qū)分大小寫 |
_cs | 區(qū)分大小寫 |
_bin | 以二進(jìn)制方式進(jìn)行比較 |
3. 各級別的字符集和比較規(guī)則
在MySQL中有四個級別的字符集和比較規(guī)則。
- 服務(wù)器級別
- 數(shù)據(jù)庫級別
- 表級別
- 列級別
3.1 服務(wù)器級別
MySQL提供了兩種系統(tǒng)變量表示服務(wù)器級別的字符集和比較規(guī)則
系統(tǒng)變量 | 描述 |
---|---|
character_set_server | 服務(wù)器級別的字符集 |
collation_server | 服務(wù)器級別的比較規(guī)則 |
可以通過SET語句來改變這兩個系統(tǒng)變量。
也可以通過修改配置文件來修改
3.2 數(shù)據(jù)庫級別
在創(chuàng)建和修改數(shù)據(jù)庫時候可以指定數(shù)據(jù)庫的字符集和比較規(guī)則
如果想要查看當(dāng)前數(shù)據(jù)庫的字符集和比較規(guī)則,可以查看下面兩個系統(tǒng)變量的值
系統(tǒng)變量 | 描述 |
---|---|
character_set_database | 當(dāng)前數(shù)據(jù)庫的字符集 |
collation_database | 當(dāng)前數(shù)據(jù)庫的比較規(guī)則 |
需要注意的是,這兩個系統(tǒng)變量并不同與描述服務(wù)器級別的字符集和比較規(guī)則的那兩個比較規(guī)則,這兩個系統(tǒng)變量只是用來告訴用戶當(dāng)前數(shù)據(jù)庫的字符集和比較規(guī)則是什么,并不能通過修改兩個變量的值來修改檔期按數(shù)據(jù)庫的字符集和比較規(guī)則。
如果在創(chuàng)建數(shù)據(jù)庫的時候不指定字符集和比較規(guī)則,那么將會使用服務(wù)器級別的字符集和比較規(guī)則來作為數(shù)據(jù)庫的字符集和比較規(guī)則。
3.3 表級別
可以在創(chuàng)建和修改表的時候指定表的字符集和比較規(guī)則
如果創(chuàng)建表的語句沒有指定字符集和比較規(guī)則,則會使用該表所在數(shù)據(jù)庫的字符集和比較規(guī)則作為該表的字符集和比較規(guī)則。
3.4 列級別
可以在創(chuàng)建和修改列的時候指定該列的字符集和比較規(guī)則
對于某一列來說,如果創(chuàng)建和修改表的語句沒有指定字符集和比較規(guī)則,則會使用該列所在的表的字符集和比較規(guī)則作為其字符集和比較規(guī)則。
注意:如果在修改列的字符集的時候,如果存儲的數(shù)據(jù)不能使用修改后的字符集進(jìn)行表示,則會報錯
4. 修改字符集或比較規(guī)則
字符集和比較規(guī)則之間是相互聯(lián)系的,只修改其一需要遵循以下規(guī)則:
- 只修改字符集,則比較規(guī)則將變?yōu)樾薷暮蟮淖址哪J(rèn)比較規(guī)則。
- 只修改比較規(guī)則,則字符集將變?yōu)樾薷暮蟮谋容^規(guī)則對應(yīng)的字符集。
5. MySQL中的字符集轉(zhuǎn)換過程
在客戶端發(fā)送請求的時候,一般來說,客戶端編碼請求字符串時使用的字符集與操作系統(tǒng)當(dāng)前使用的字符集一致。
但是如果MySQL客戶端設(shè)置了default-character-set啟動選項的時候,那么服務(wù)器就會忽視操作系統(tǒng)當(dāng)前使用的字符集。
而在服務(wù)器接收請求的時候,服務(wù)器接收到的請求實際上就是一個字節(jié)序列。服務(wù)器會將這個字節(jié)序列看作是使用系統(tǒng)變量character_set_client表示的字符集編碼的字節(jié)序列。
注意的是,這個系統(tǒng)變量character_set_client是session級別的
客戶端發(fā)送請求的字符集和服務(wù)器接收請求的字符集是兩個獨立的字符集,一般來說,客戶端發(fā)送請求所用字符集應(yīng)和服務(wù)器接收請求的字符集應(yīng)保持一致,但是也會出現(xiàn)不一致的時候
如果服務(wù)器的character_set_client對應(yīng)的字符集不能解釋請求的字節(jié)序列,那么服務(wù)器就會發(fā)出警告。
在服務(wù)器處理請求的時候,會將服務(wù)器接收請求的character_set_client對應(yīng)的字符集進(jìn)行編碼的字節(jié)序列轉(zhuǎn)換成session級別的系統(tǒng)變量character_set_connection對應(yīng)的字符集進(jìn)行編碼的字節(jié)序列。
對于character_set_connection我們可以通過SET命令單獨修改此系統(tǒng)變量,除了character_set_connection,還有一個與其對應(yīng)的系統(tǒng)變量collation_connection,這個系統(tǒng)變量表示了這些字符串應(yīng)使用哪種比較規(guī)則。
同樣這個系統(tǒng)變量collation_connection可以通過SET命令進(jìn)行修改。
如果客戶端發(fā)送請求、服務(wù)器接收請求和服務(wù)器處理請求這一過程中,字符集和比較規(guī)則不一樣,會出現(xiàn)什么情況呢?
就這個表來說
向客戶端發(fā)送一條查詢語句
由于當(dāng)前操作系統(tǒng)使用的是gbk字符集,所以字符串‘我’是使用GBK字符集進(jìn)行編碼的,比較規(guī)則為gbk_chinese_ci,而列C使用的是utf8字符集編碼的,比較規(guī)則為utf8_general_ci。
那么在這種情況下,列的字符集和排序規(guī)則的優(yōu)先級更高,也就是說這里需要將‘我’從GBK字符集轉(zhuǎn)換成utf8字符集,然后使用列c的比較規(guī)則進(jìn)行比較。
當(dāng)服務(wù)器生成響應(yīng)的時候,是不是直接將列c也就是utf8字符集進(jìn)行編碼的結(jié)果發(fā)送回客戶端呢?
答案是不一定,這取決與SESSION級別的系統(tǒng)變量character_set_results的值,結(jié)果會從utf8字符集編碼的轉(zhuǎn)換成
character_set_results對應(yīng)的字符編碼后的字節(jié)序列
系統(tǒng)變量 | 描述 |
---|---|
character_set_client | 服務(wù)器認(rèn)為請求是按照系統(tǒng)變量指定的字符集進(jìn)行編碼的 |
character_set_connection | 服務(wù)器在處理請求時,會將請求字符序列從character_set_client轉(zhuǎn)換成character_set_connection |
character_set_results | 服務(wù)器采用該系統(tǒng)變量指定的字符集對返回客戶端的字符串進(jìn)行編碼 |
由于三個變量均為SESSION級別,因此每個客戶端與服務(wù)器建立連接的時候都會維護(hù)這三個變量。
最后就是客戶端接收響應(yīng)了,響應(yīng)也就是一個字節(jié)序列,收到的字節(jié)序列會直接寫進(jìn)黑框框中,再由黑框框?qū)⒆止?jié)序列接受成人類能懂的字符(如果沒有特殊設(shè)置,一般使用操作系統(tǒng)當(dāng)前使用的字符集進(jìn)行解釋)
參考:《MySQL是怎樣運(yùn)行的:從根兒上理解 MySQL》
總結(jié)
到此這篇關(guān)于MySQ字符集和比較規(guī)則的文章就介紹到這了,更多相關(guān)MySQ字符集和比較規(guī)則內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL連接器提升應(yīng)用功能與數(shù)據(jù)存儲能力
這篇文章主要為大家介紹了MySQL連接器提升應(yīng)用功能與數(shù)據(jù)存儲能力,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-10-10解決ERROR?2003?(HY000):?Can‘t?connect?to?MySQL?server?
在Windows系統(tǒng)上使用Django連接Ubuntu虛擬機(jī)中的MySQL數(shù)據(jù)庫時,遇到無法連接的問題,排查后發(fā)現(xiàn)是由于MySQL綁定的IP地址改變導(dǎo)致的,下面就來介紹一下問題解決,感興趣的可以了解一下2024-09-09Mysql匿名登錄無法創(chuàng)建數(shù)據(jù)庫問題解決方案
這篇文章主要介紹了Mysql匿名登錄無法創(chuàng)建數(shù)據(jù)庫問題解決方案,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-12-12幾種MySQL中的聯(lián)接查詢操作方法總結(jié)
這篇文章主要介紹了幾種MySQL中的聯(lián)接查詢操作方法總結(jié),文中包括一些代碼舉例講解,需要的朋友可以參考下2015-04-04Mysql服務(wù)添加 iptables防火墻策略的方案
這篇文章主要介紹了給Mysql服務(wù)添加 iptables防火墻策略的方案,本文給大家分享兩種解決方案,需要的朋友可以參考下2021-04-04