ACMUG徵集原創技術文章。詳情請新增 A_CMUG或者掃描文末二維碼關注我們的微信公眾號。有獎徵稿，請傳送稿件至：acmug@acmug.com。
3306現金有獎徵稿說明：知識無價，勞動有償，ACMUG特約撰稿人有獎回報計劃（修訂版）

---

作者簡介：周曉

網路常用id seanlook 。以前在TP-LINK做了2年Oracle DBA，後來專職做MySQL了。平時在工作中遇到的些問題和處理經驗，有空會寫寫放在自己的網站上 

---

01

utf8 與 utf8mb4 異同

先看 官方手冊 https://dev.mysql.com/doc/refman/5.6/en/charset-unicode-utf8mb4.html 的說明：

The character set named utf8 uses a maximum of three bytes per character and contains only BMP characters. The utf8mb4 character set uses a maximum of four bytes per character supports supplementary characters:

• For a BMP character, utf8 and utf8mb4 have identical storage characteristics: same code values, same encoding, same length.

• For a supplementary character, utf8 cannot store the character at all, whereas utf8mb4 requires four bytes to store it. Because utf8 cannot store the character at all, you have no supplementary characters in utf8 columns and need not worry about converting characters or losing data when upgrading utf8 data from older versions of MySQL.

MySQL在 5.5.3 之後增加了 utf8mb4 字元編碼，mb4即 most bytes 4。簡單說 utf8mb4 是 utf8 的超集並完全相容utf8，能夠用四個位元組儲存更多的字元。

但拋開資料庫，標準的 UTF-8 字符集編碼是可以用 1~4 個位元組去編碼21位字元，這幾乎包含了是世界上所有能看見的語言了。然而在MySQL裡實現的utf8最長使用3個位元組，也就是隻支援到了 Unicode 中的 （U+0000至U+FFFF），包含了控制符、拉丁文，中、日、韓等絕大多數國際字元，但並不是所有，最常見的就算現在手機端常用的表情字元 emoji和一些不常用的漢字，如 “墅” ，這些需要四個位元組才能編碼出來。

注：QQ裡面的內建的表情不算，它是透過特殊對映到的一個gif圖片。一般輸入法自帶的就是。

也就是當你的資料庫裡要求能夠存入這些表情或寬字元時，可以把欄位定義為 utf8mb4，同時要注意連線字符集也要設定為utf8mb4，否則在  下會出現 Incorrect string value: /xF0/xA1/x8B/xBE/xE5/xA2… for column 'name'這樣的錯誤，非嚴格模式下此後的資料會被截斷。

提示：另外一種能夠儲存emoji的方式是，不關心資料庫表字符集，只要連線字符集使用 latin1，但相信我，你絕對不想這個幹，一是這種字符集混用管理極不規範，二是儲存空間被放大（讀者可以想下為什麼）。

02

utf8mb4_unicode_ci 與 utf8mb4_general_ci 如何選擇

字元除了需要儲存，還需要排序或比較大小，涉及到與編碼字符集對應的 排序字符集（collation）。ut8mb4對應的排序字符集常用的有 utf8mb4_unicode_ci、utf8mb4_general_ci，到底採用哪個在 stackoverflow 上有個討論，

主要從排序準確性和效能兩方面看：

• 準確性
  utf8mb4_unicode_ci 是基於標準的Unicode來排序和比較，能夠在各種語言之間精確排序
  utf8mb4_general_ci 沒有實現Unicode排序規則，在遇到某些特殊語言或字元是，排序結果可能不是所期望的。
  但是在絕大多數情況下，這種特殊字元的順序一定要那麼精確嗎。比如Unicode把?、?當成ss和OE來看；而general會把它們當成s、e，再如????ā?各自都與 A 相等。

• 效能
  utf8mb4_general_ci 在比較和排序的時候更快
  utf8mb4_unicode_ci 在特殊情況下，Unicode排序規則為了能夠處理特殊字元的情況，實現了略微複雜的排序演算法。
  但是在絕大多數情況下，不會發生此類複雜比較。general理論上比Unicode可能快些，但相比現在的CPU來說，它遠遠不足以成為考慮效能的因素，索引涉及、SQL設計才是。 我個人推薦是utf8mb4_unicode_ci，將來 8.0 裡也極有可能使用變為預設的規則。

這也從另一個角度告訴我們，不要可能產生亂碼的欄位作為主鍵或唯一索引。我遇到過一例，以 url 來作為唯一索引，但是它記錄的有可能是亂碼，導致後來想把它們修復就特別麻煩。

03

怎麼從utf8轉換為utf8mb4

3.1 “偽”轉換

如果你的表定義和連線字符集都是utf8，那麼直接在你的表上執行

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4;

則能夠該表上所有的列的character型別變成 utf8mb4，表定義的預設字符集也會修改。連線的時候需要使用set names utf8mb4便可以插入四位元組字元。（如果依然使用 utf8 連線，只要不出現四位元組字元則完全沒問題）。

上面的 convert 有兩個問題，一是它不能ONLINE，也就是執行之後全表禁止修改，有關這方面的討論見 ；二是，它可能會自動該表欄位型別定義，如 ，可以透過 MODIFY 指定型別為原型別。

另外 ALTER TABLE tbl_name DEFAULT CHARACTER SET utf8mb4 這樣的語句就不要隨便執行了，特別是當表原本不是utf8時，除非表是空的或者你確認表裡只有拉丁字元，否則正常和亂的就混在一起了。

最重要的是，你連線時使用的latin1字符集寫入了歷史資料，表定義是latin1或utf8，不要期望透過 ALTER ... CONVERT ... 能夠讓你達到用utf8讀取歷史中文資料的目的，沒鳥用，老老實實做邏輯dump。所以我才叫它“偽”轉換

3.2 character-set-server

一旦你決定使用utf8mb4，強烈建議你要修改服務端 character-set-server=utf8mb4，不同的語言對它的處理方法不一樣，c++, php, python可以設定character-set，但java驅動依賴於 character-set-server 選項，後面有介紹。

同時還要謹慎一些特殊選項，如 。個人不建議設定全域性 init_connect。

04

key 768 long 錯誤

字符集從utf8轉到utf8mb4之後，最容易引起的就是索引鍵超長的問題。

對於錶行格式是 COMPACT或 REDUNDANT，InnoDB有單個索引最大位元組數 768 的限制，而欄位定義的是能儲存的字元數，比如 VARCHAR(200) 代表能夠存200個漢字，索引定義是字符集型別最大長度算的，即 utf8 maxbytes=3, utf8mb4 maxbytes=4，算下來utf8和utf8mb4兩種情況的索引長度分別為600 bytes和800bytes，後者超過了768，導致出錯：Error 1071: Specified key was too long; max key length is 767 bytes。

COMPRESSED和DYNAMIC格式不受限制，但也依然不建議索引太長，太浪費空間和cpu搜尋資源。

如果已有定義超過這個長度的，可加上字首索引，如果暫不能加上字首索引（像唯一索引），可把該欄位的字符集改回utf8或latin1。
但是，（ 敲黑板啦，很重要 ），要防止出現 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '=' 錯誤：連線字符集使用utf8mb4，但 SELECT/UPDATE where條件有utf8型別的列，且條件右邊存在不屬於utf8字元，就會觸發該異常。表示踩過這個坑。

再多加一個友好提示：EXPLAIN 結果裡面的 key_len 指的搜尋索引長度，單位是bytes，而且是以字符集支援的單字元最大位元組數算的，這也是為什麼 INDEX_LENGTH 膨脹厲害的一個原因。

05

C/C++ 記憶體空間分配問題

這是我們這邊的開發遇到的一個棘手的問題。C或C++連線MySQL使用的是linux系統上的 libmysqlclient 動態庫，程式獲取到資料之後根據自定義的一個網路協議，按照mysql欄位定義的固定位元組數來傳輸資料。從utf8轉utf8mb4之後，c++裡面針對character單字元記憶體空間分配，從3個增加到4個，引起異常。

這個問題其實是想說明，使用utf8mb4之後，官方建議儘量用 varchar 代替 char，這樣可以減少固定儲存空間浪費（關於char與varchar的選擇，可參考 ）。但開發設計表時 varchar 的大小不能隨意加大，它雖然是變長的，但客戶端在定義變數來獲取資料時，是以定義的為準，而非實際長度。按需分配，避免程式使用過多的記憶體。

06

java驅動使用

Java語言裡面所實現的UTF-8編碼就是支援4位元組的，所以不需要配置 mb4 這樣的字眼，但如果從MySQL讀寫emoji，MySQL驅動版本要在 5.1.13 及以上版本，資料庫連線依然是 characterEncoding=UTF-8 。

但還沒完，遇到一個大坑。 裡還有這麼一段話：

	
		
		

			Connector/J did not support utf8mb4 for servers 5.5.2 and newer.
		

		

			Connector/J now auto-detects servers configured with character_set_server=utf8mb4 or treats the Java encoding utf-8 passed
		

		

			using characterEncoding=... as utf8mb4 in the SET NAMES= calls it makes when establishing the connection. (Bug #54175) 
		

意思是，java驅動會自動檢測服務端 character_set_server 的配置，如果為utf8mb4，驅動在建立連線的時候設定 SET NAMES utf8mb4。然而其他語言沒有依賴於這樣的特性。

07

主從複製錯誤

這個問題沒有遇到，只是看官方文件有提到，曾經也看到過類似的技術文章。
大概就是從庫的版本比主庫的版本低，導致有些字符集不支援；或者人工修改了從庫上的表或欄位的字符集定義，都有可能引起異常。

08

join 查詢問題

這個問題是之前在姜承堯老師公眾號看到的一篇文章 “MySQL表欄位字符集不同導致的索引失效問題”，自己也驗證了一下，的確會有問題：

	
		
		

			CREATE TABLE t1 (
		

		

			  f_id varchar(20) NOT NULL,
		

		

			  f_action char(25) NOT NULL DEFAULT '' COMMENT '',
		

		

			PRIMARY KEY (`f_id`),
		

		

			) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
		

		

			


		

		

			CREATE TABLE t1_copy_mb4 (
		

		

			  f_id varchar(20) CHARACTER SET utf8mb4 NOT NULL,
		

		

			  f_action char(25) NOT NULL DEFAULT '' COMMENT '',
		

		

			PRIMARY KEY (`f_id`),
		

		

			) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
		

		

			


		

		

			1.EXPLAIN extended select * from t1 INNER JOIN t1_copy_mb4 t2 on t1.f_id=t2.f_id where t1.f_id='421036'; 
		

		

			


		

		

			2.EXPLAIN extended select * from t1 INNER JOIN t1_copy_mb4 t2 on t1.f_id=t2.f_id where t2.f_id='421036'; 
		

對應上面1,2 的截圖：

其中 2 的warnings 有convert:

• (convert(t1.f_id using utf8mb4) = ‘421036’)

官網能找到這一點解釋的還是開頭那個地址：

	
		
		

			Similarly, the following comparison in the WHERE clause works according to the collation of utf8mb4_col:
		

		

			SELECT * FROM utf8_tbl, utf8mb4_tbl
		

		

			WHERE utf8_tbl.utf8_col = utf8mb4_tbl.utf8mb4_col;
		

只是索引失效發生在utf8mb4列 在條件左邊。（關於MySQL的隱式型別轉換，見）。

09

參考

• https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-conversion.html