MYSQL資料庫與Emoji表情的故事

雲是風的夢發表於2019-03-12
MySql資料庫

問題背景

手機上眾多輸入法和鍵盤支援輸入 emoji 表情，給早期設計的程式造成了越來越多的干擾。

  1. 移動端購物的流行，2018 年 “雙十一”全網移動端交易達到 93.6%
  2. 微信年度報告裡 80 後愛用的“齜牙”表情，早在 2017 年 QQ 釋出的統計資料就超過 303 億。

最近我們團隊就遇到了一個線上問題，就是由於使用者下單備註使用 emoji 表情引起的問題。

通過解決這個問題，主要了解下面 3 個方面內容：

  1. Mysql 編碼概念
  2. Mysql“亂碼”是怎麼來的：字元編碼轉換
  3. Emoji 在 Mysql 使用問題

問題分析

昨天突然接到業務反饋，監控告警了，有幾個訂單卡單了，一直在某個系統裡面沒有推到下游，最終導致錯過了配送時間。

查詢日誌中心，ES 搜尋到了某系統 Job 拉資料儲存資料庫的異常資訊。

展開詳細日誌發現了關鍵日誌：

ERROR 1366: Incorrect string value: '\xF0\x9F\x99\x8F...' for column 'Remark' at row 2。

試圖將一個 4 位元組的字元寫入到一個 3 位元組的列 Remark, 當然是報錯了。

然後我們看了一下使用者的備註 Remark 資訊，**上午家裡沒人，請下午送，謝謝?

這個謝謝真心不容易啊，老鐵。emoji 表情，下單完全沒有問題。DBA 搜尋了一下歷史訂單資料庫，是支援的。找了一下報錯的 Job 服務，發現操作的庫是最近從 SQL Server 轉到 mysql 的，拉取列印發貨單資訊的時候出錯了。

問題原因

相關字元和編碼知識點

Unicode 字符集有好幾種編碼方式，比如常見的 utf-8，utf-16,utf-32 等。 utf-8 是它是一種變長的編碼方式，採用 1-4 個位元組表示字元，utf-16 採用固定的 2 個位元組，utf-32 則採用 4 個位元組儲存。

C#裡面 Encoding.Unicode 實現為 2 個位元組的 Little-Endian UTF-16，如果是 4 個位元組使用 UTF-32。

Unicode 在第二個皮膚（Plane 1，SMP）規定了 emoji 的碼點和含義。每一個表情使用 4 個位元組。 所謂 Emoji 就是一種在 Unicode 位於、u1F601-\u1F64F 區段的字元。

SQL Server 的資料庫表，nvarchar 型別字串預設就是可變長度 Unicode 字串。

MySQL 版本 5.5.3 以下版本 utf8 字符集 utf8 最多表示 3 個位元組，5.5.3 以上版本支援 4 個位元組的 utf8 編碼字符集 utf8mb4,MySQL8.0 版預設字符集為 utf8mb4。

MYSQL 儲存 utf8 字元預設為無 BOM 的 Big-Endian 方式編碼。

MySQL 中的字符集轉換過程

字符集轉換
圖片來源於網路

瞭解字元編碼的轉換規則，我們就可以理解為何會產生亂碼以及字元插入失敗等問題。

https://www.ibm.com/developerworks/cn/java/analysis-and-summary-of-common-random-code-problems/index.html

解決方案

1. 瞭解當前字元編碼設定

我們先檢視一下系統配置的相關結果：

mysql> show variables like '%char%';
+--------------------------+----------------------------------------------+
| Variable_name            | Value                                        |
+--------------------------+----------------------------------------------+
| character_set_client     | utf8                                         |
| character_set_connection | utf8                                         |
| character_set_database   | utf8mb4                                      |
| character_set_filesystem | binary                                       |
| character_set_results    | utf8                                         |
| character_set_server     | utf8mb4                                      |
| character_set_system     | utf8                                         |
| character_sets_dir       | D:\mysql\mysql-8.0.11-winx64\share\charsets\ |
+--------------------------+-------------------------------------   ---------+
8 rows in set
複製程式碼

2. 設計資料庫時明確指定字符集

包括庫，表，欄位三個層級都要明確，否則按照從低到高原則使用 my.ini 預設配置。具體的建立語句不細說，請自行搜尋。

3. 統一字符集

目的是減少不必要的轉換，除非有特殊要求。特別注意保證轉換的時候不會由於字符集不相容而導致不可逆的轉換。例如部分 Unicode 字元在 Utf8 裡面是沒有的，部分 gbk 編碼也是。 具體來說就是：

客戶端， character-set-client，table charset 三個字符集完全一致就可以保證不會產生亂碼。

SQL 語句中的裸字串會受到 character_set_connection 字符集或 introducer 設定的影響，對於比較之類的操作可能產生完全不同的結果，需要小心！解決方法是在傳送查詢前執行一下下面這句： SET NAMES '***'

相當於下面的三句指令：
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;

4. 修復編碼損壞資料不可強行轉換字符集

通過 ALTER TABLE ... CHARSET=xxx 或 ALTER TABLE … CONVERT TO CHARACTER SET … 有可能把資料完全破壞，可行的做法可以參考盧鈞軼的部落格（參見引文5）。

寫在後面

關於 emoji 表情，隨著手機輸入的支援和年輕人的熱愛，想要不出問題必須思考好以下幾個問題：

  1. 功能設計明確該輸入框是否需要支援 emoji 表情
  2. 上下游鏈路約定好如何儲存和展示，字串擷取
  3. 運維和升級資料庫字符集相關問題需要謹慎，防止資料丟失
  4. 主從同步，注意低版本不支援的字符集 utf8mb4 的情況會導致同步失敗

資料來源

  1. 前瞻產業研究院報告解讀：移動支付便利化 十張圖瞭解 2018 年全球購物狂歡節高速增長背後的女人
  2. 阮一峰入門文章：Emoji 簡介
  3. 36 氪一篇有趣的文章：一波又一波 Emoji 表情推出，你的表情符號鍵盤還好嗎？
  4. 有意思的國外相關資料：你正在參與的語言革命｜Emoji：再建巴別塔
  5. 盧鈞軼：10分鐘學會理解和解決MySQL亂碼問題：10分鐘學會理解和解決MySQL亂碼問題
  6. MYSQL Help About Unicode:charset-unicode
  7. 常見亂碼問題分析和總結：亂碼分析

本文同步釋出在公眾號：MYSQL亂碼問題整理

相關文章