mysql中的utf8與utf8mb4儲存及區別

2023-02-05 14:03:38

一、如何設定utf8mb4

mysql中針對字串型別，在設定charset的時候可以精確到欄位。

如果只將某個欄位設定utf8mb4，那麼其他欄位不會受影響。

如果針對表來設定，那麼已經存在的欄位依然是utf8，並且會多出utf8的標記，之後所建立的欄位才會是utf8mb4。

如果針對庫來設定，那麼已經存在的表依然是utf8，之後所建立的表才會是utf8mb4。

除此之外呢，我們在連線資料庫的時候，也要指明charset=utf8mb4，否則的話，此連線無法向utf8mb4的欄位寫入資料，並且讀取的時候是亂碼。

在使用 navicat 的時候，發現沒有地方設定連線的字元編碼，他會自動掃面你的資料庫，表，欄位的編碼，來自動設定一個合適的編碼，當然，這也跟 navicat 版本有關，高版本才行，我的低版本就不行，如果你發現你的 navicat 無法顯示錶情，只能看到問好，那麼可以通過show variables like '%char%'檢視一下。

我還遇到一個情況，我的 navicat 沒法自動設定 utf8mb4，因此，在 utf8 的情況下，我將線上的表情同步到了我本地，這使得我在後面即使設定了 utf8mb4 的情況下也看不到表情，這是因為我在 utf8 的時候同步過來的資料被破壞了，字元集不相容，所以需要先設定好字元編碼再拉取一次資料。

二、問題

1、為什麼儲存的時候要區分utf8和utf8mb4

按理說，不管我存進去的是單位元組還是多位元組，本質都是二進位制，我寫入什麼你就存什麼不就好了，幹嘛還要有限制。這是因為，Mysql對每個欄位都定義了長度，比如varchar(10)表示10個字元，而不是位元組，所以當存入資料的時候，mysql是做了解析的，這樣才能知道字串裡有幾個字元；當面對4位元組字元的時候，mysql依然會以3位元組的編碼規則來解析，顯然會解析出錯的，因此就不讓寫入。

MySQL在5.5.3之後增加了這個utf8mb4的編碼，mb4就是most bytes 4的意思，專門用來相容四位元組的unicode。好在 utf8mb4 是 utf8 的超集，除了將編碼改為 utf8mb4 外不需要做其他轉換。當然，為了節省空間，一般情況下使用 utf8 也就夠了。

utf8 是 Mysql 中的一種字元集，只支援最長三個位元組的 UTF-8 字元，可能是因為 Mysql 剛開始開發那會，Unicode 還沒有4位元組的字元。至於後續的版本為什麼不對 4 位元組長度的 UTF-8 字元提供支援，應該是為了向後相容性的考慮，還有就是4位元組字元確實很少用到。

2、為什麼讀取的時候要區分utf8和utf8mb4

按理說，我讀取的都是二進位制，不管是三位元組還是四位元組，我自己來展示，為什麼在讀取 utf8mb4 欄位的時候，我使用 utf8 的連線得到的是亂碼，使用 utf8mb4 連線得到的是正常的。實際上我的電腦是能展示四位元組字元的。

因為mysql有個聯結器元件，它處於使用者端和伺服器之間，用於字元集的轉換。

現在有一個欄位name，為了相容emoj表情，欄位設定為utf8mb4，在寫入的時候資料庫連線設定了charset=utf8mb4，因此可以正常寫入；在讀取的時候資料庫連線設定charset=utf8，於是讀出來展示的時候是亂碼，如果改成charset=utf8mb4，讀出來就能正常展示，那就是說，utf8的連線讀到的結果並不是真實的資料，而是經過了聯結器的轉換，它將utf8mb4轉換成了utf8，四位元組字元被轉換成了三位元組，自然就是亂碼。

那麼，為什麼要有這個轉碼的過程呢？

那是因為mysql支援很多的字元編碼。

mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode            | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
40 rows in set

collation為排序規則，Maxlen為最大位元組數。

不同的編碼規則，會得到不同的二進位制數，因此正確的編碼轉換是必要的。

檢視當前的編碼

mysql> show variables like '%char%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | utf8   |
| character_set_connection | utf8   |
| character_set_database   | utf8   |
| character_set_filesystem | binary |
| character_set_results    | utf8   |
| character_set_server     | utf8   |
| character_set_system     | utf8   |
| character_sets_dir       |        |
+--------------------------+--------+

設定當前連線的編碼，只針對此連線有效

mysql -h xxxxxx.mysql.rds.aliyuncs.com -u xxxxxx -p xxxxxx

mysql> set names gbk;

mysql> show variables like '%char%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | gbk    |
| character_set_connection | gbk    |
| character_set_database   | utf8   |
| character_set_filesystem | binary |
| character_set_results    | gbk    |
| character_set_server     | utf8   |
| character_set_system     | utf8   |
| character_sets_dir       |        |
+--------------------------+--------+

這個命令會同時修改character_set_client, character_set_connection, character_set_results

我們在接資料庫的時候設定的charset=utf8在內部就是呼叫的set names utf8。

所以，代表使用者端的編碼有三個，這三個編碼基本是一致的。其他的都是伺服器端的的編碼。

character_set_client 使用者端

character_set_connection 聯結器

character_set_results 返回的結果集

既然是一樣的，為什麼使用者端要搞三個設定呢，這就要從資料傳輸的流程上來看。

聯結器：連線使用者端與伺服器端，進行字元集的轉換。

聯結器的工作流程：

請求

character_set_client --> character_set_connection -->character_set_server

響應