10分鐘完成MySQL對(duì)emoji的支持

公司有新要求，ios客戶端要上線評(píng)論中可以使用emoji表情的功能，在mysql 5.5 之前，UTF-8編碼只支持1-3個(gè)字節(jié)；從MySQL 5.5開(kāi)始，可以支持4個(gè)字節(jié)UTF編碼 utf8mb4 ，一個(gè)字符能夠支持更多的字符集，也能夠支持更多表情符號(hào)。

utf8mb4兼容utf8，且比utf8能表示更多的字符，是utf8字符集的超集。所以現(xiàn)在一些新的業(yè)務(wù)，比如IOS中的emoji表情，會(huì)將MySQL數(shù)據(jù)庫(kù)的字符集設(shè)置為utf8mb4。

先看問(wèn)題：

Caused by: java.sql.SQLException: Incorrect string value: '\xF6\x9D\x98\x84' for column 'comment' at row 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)

如果我們將列comment設(shè)置為varchar(100)，用于存儲(chǔ)評(píng)論信息，現(xiàn)在上線新功能存儲(chǔ)emoji表情，插入emoji表情就會(huì)報(bào)出上述錯(cuò)誤，UTF-8編碼有可能是兩個(gè)、三個(gè)、四個(gè)字節(jié)。Emoji表情是4個(gè)字節(jié)，而Mysql的utf8編碼最多3個(gè)字節(jié)，所以數(shù)據(jù)插不進(jìn)去。utf8mb4兼容utf8，且比utf8能表示更多的字符。

解決方案：將Mysql的編碼從utf8轉(zhuǎn)換成utf8mb4。

網(wǎng)上的文章各執(zhí)一詞，本文就生產(chǎn)環(huán)境中真實(shí)可用的參數(shù)進(jìn)行記錄

整體操作流程其實(shí)并不難

一：首先我們修改my.cnf參數(shù)

[client]
default-character-set=utf8mb4
  
  
[mysql]
default-character-set=utf8mb4
  
  
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
character-set-client-handshake = false

二：對(duì)數(shù)據(jù)庫(kù)相關(guān)的表進(jìn)行字符集修改

將數(shù)據(jù)庫(kù)轉(zhuǎn)換為utf8mb4

mysql> ALTER DATABASE erp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

將已經(jīng)建好的表也轉(zhuǎn)換成utf8mb4 

mysql>ALTER TABLE `erp_comment` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

將需要使用emoji的字段設(shè)置類型為： 

mysql>ALTER TABLE `erp_comment` MODIFY COLUMN `comment`  varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

三：重啟數(shù)據(jù)庫(kù)服務(wù)器使之生效

[root@HE3 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.... SUCCESS!

四：登錄數(shù)據(jù)庫(kù)檢查是否如下：

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client    | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database  | utf8mb4            |
| character_set_filesystem | binary            |
| character_set_results    | utf8mb4            |
| character_set_server    | utf8mb4            |
| character_set_system    | utf8              |
| collation_connection    | utf8mb4_unicode_ci |
| collation_database      | utf8mb4_unicode_ci |
| collation_server        | utf8mb4_unicode_ci |
+--------------------------+--------------------+
rows in set (0.00 sec)

特別說(shuō)明下：collation_connection/collation_database/collation_server如果是utf8mb4_general_ci，沒(méi)有關(guān)系。但必須保證character_set_client/character_set_connection/character_set_database/character_set_results/character_set_server為utf8mb4。

五：讓開(kāi)發(fā)那邊的pom配置中，去掉characterEncoding參數(shù)，并重新編譯一下

如果你用的是java服務(wù)器，升級(jí)或確保你的mysql connector版本高于5.1.13，否則仍然無(wú)法使用utf8mb4

最后再讓前端應(yīng)用插入emoji表情，就可以了。

一些小知識(shí)點(diǎn):

其中character-set-server 和 collation-server 這些設(shè)置為utf8mb4字符集是比較容易理解的，就是將MySQL數(shù)據(jù)庫(kù)相關(guān)的字符集都設(shè)置為utf8mb4；

但為了實(shí)現(xiàn)客戶端utf8連接到MySQL后，使用的也是utf8mb4字符集，就在 mysqld配置中配置了 init_connect='SET NAMES utf8mb4' 表示初始化連接都設(shè)置為utf8mb4字符集，再配置一個(gè) skip-character-set-client-handshake = true 忽略客戶端字符集設(shè)置，不論客戶端是何種字符集，都按照init_connect中的設(shè)置進(jìn)行使用，這樣就滿足了應(yīng)用的需求。