MySQL插入emoji表情失敗問題的解決方案

標籤：插入資料   char   資料庫   ...   jdbc   password   update   cte   emoji表情   

前言

之前一直認為UTF-8是萬能的字元集問題解決方案，直到最近遇到這個問題。最近在做新浪微博的爬蟲， 在存庫的時候發現只要保持emoji表情，就回拋出以下異常：

Incorrect string value: ‘\xF0\x90\x8D\x83\xF0\x90...‘

眾所周知UTF-8是3個位元組, 其中已經包括我們日常能見過的絕大多數字型. 但3個位元組遠遠不夠容納所有的文字, 所以便有了utf8mb4, utf8mb4是utf8的超集, 佔4個位元組, 向下相容utf8. 我們日常用的emoji表情就是4個位元組了.

所以在此我們像utf8的資料表插入資料就會報出Incorrect string value這個錯誤.

Google一下很容易就找到瞭解決方案, 具體解決辦法是如下：

一、修改資料表的字元集為utf8mb4

這點很簡單, 修改語句網上找一大堆, 不過建議重建立表, 使用 mysqldump -uusername -ppassword database_name table_name > table.sql 備份相應資料表, 並修改其中的建表語句的字元集為 utf8mb4 即可, 然後 mysql -uusername -ppassword database_name < table.sql 重新匯入sql即可完成修改字元集操作.

二、MySQL資料庫版本要5.5.3及以上

網路上所有的文章都說明要MySQL 5.5.3以上的版本才支援utf8mb4, 不過我使用的資料庫版本為5.5.18, 最終仍能解決問題, 所以同學們不要急著找營運哥哥升級資料庫先, 先試試能不能自己解決問題.

三、修改資料庫設定檔/etc/my.cnf並重啟mysql服務

主要是修改資料庫的預設字元集, 以及串連, 查詢的字元集, [Mysql支援emoji Emoji 升級編碼為UTF8MB4][1] 這篇文章有詳細的設定方法, [深入Mysql字元集設定][2] 這篇文章有其中設定的各個字元集的作用, 大家可以科普下.

四、升級MySQL Connector到5.1.21及以上

以上所有的操作, 最關鍵的是步驟3, 修改資料庫的設定檔, 其中大概修改了

[client]# 用戶端來來源資料的預設字元集default-character-set = utf8mb4[mysqld]# 服務端預設字元集character-set-server=utf8mb4# 串連層預設字元集collation-server=utf8mb4_unicode_ci[mysql]# 資料庫預設字元集default-character-set = utf8mb4

這些配置指定了資料從用戶端到服務端所經過的一條條管道使用的字元集, 其中每一個管道出現問題都可能會導致插入失敗或者亂碼.

但很多時候, 線上的資料庫是不能隨便修改資料庫檔案的, 所以我們的營運同學很果斷的回絕了我修改資料庫設定檔的請求(T_T)

所以就只能用代碼解決了, 一開始是準備從JDBC串連時候就指定使用的字元集處下手.

jdbc:mysql://localhost:3306/ding?characterEncoding=UTF-8

主要把UTF-8修改為utf8mb4對於的Java Style Charset字串應該就能解決問題吧?

不過很遺憾的是, Java JDBC並不存在utf8mb4對於的字元集. 使用UTF-8的時候可以相容urf8mb4並自動轉換字元集.

For example, to use 4-byte UTF-8 character sets with Connector/J, configure the MySQL server with character_set_server=utf8mb4, and leave characterEncoding out of the Connector/J connection string. Connector/J will then autodetect the UTF-8 setting. – [MySQL:Using Character Sets and Unicode][3]

後來科普了一下, 在每一次查詢請求的時候, 可以顯式的指定使用的字元集, 使用 set names utf8mb4 可以指定本次連結的字元集為utf8mb4, 但這個設定在每次串連被釋放後都會失效.

目前的解決辦法是, 在需要插入utf8mb4的時候, 顯示地調用執行set names utf8mb4, 如:

jdbcTemplate.execute("set names utf8mb4");jdbcTempalte.execute("...");

需要注意的是, 我們在使用一下ORM架構的時候, 因為效能最佳化原因, 架構會延遲提交, 除非事務結束或者使用者主動調用強制提交, 負責執行的set names utf8mb4仍然不會生效.

在這裡我使用的是myBatis, 以MessageDao為例

// MessageDaopublic interface MessageDao { @Update("set names utf8mb4") public void setCharsetToUtf8mb4(); @Insert("insert into tb_message ......") public void insert(Message msg);}// test codeSqlSession sqlSession = sqlSessioFactory.openSession();messageDao = sqlSession.getMapper(MessageDao.class);messageDao.setCharsetToUtf8mb4();// 強制提交sqlSession.commit();messageDao.insert(message);

至此, 問題便解決了..

哎, 如果世事能那麼順利就好了, 在項目中, mybatis是執行個體是交由Spring去管理的, 也就是說我拿不到sqlSession, 也就是強制提交不了. 並且因為Spring事務架構的限制, 他並不允許使用者顯式調用強制提交. 目前還在糾結這個問題.

有兩個解決思路:

• 使用AOP, 在可能插入4位元組UTF8字元的時候, 前置方法執行set names utf8mb4, 但該方案還不能確定AOP的方法會被Spring進行交易管理麼, 並且在前置方法中,拿到的連結是否和接下來拿到的連線物件是同一個session.
• 研究Spring JDBC的建立方法, 寫一個hook在每次建立新的資料庫連接的時候, 都執行一次set names utf8mb4, 這樣就保證每一次拿到的連結都是設定過字元集的.

總結

以上就是這篇文章的全部內容了，待有時間再實驗一下以上兩種方案。希望本文的內容對大家的學習或者工作能帶來一定的協助，如果有疑問大家可以留言交流，謝謝大家對指令碼之家的支援。

MySQL插入emoji表情失敗問題的解決方案