MySQL Java emoji support

前言

現在只要做 mobile 服務都必須支援 emoji 等表情符號

雖然 AWS Aurora DB 已經設定好 character set utf8mb4 , utf8mb4_unicode_ci，想說應該這樣就沒問題了，沒想到還是被我踩到 mysql-connector-java library 的地雷

錯誤訊息

class org.springframework.jdbc.UncategorizedSQLException:PreparedStatementCallback; 
uncategorized SQLException for SQL [update example set content = ?, updateDate = sysdate() where id = ?]; 
SQL state [HY000]; error code [1366]; 
Incorrect string value: '\xF0\x9F\x98\x80\xF0\x9F...' for column 'content' at row 1; 
nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x80\xF0\x9F...' for column 'content' at row 1

解決辦法

很簡單，就是將 mysql-connector-java library 升級到 5.1.13 之後的版本就可以了！！

Changes in MySQL Connector/J 5.1.13 (2010-06-24)

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)

Reference

mysql/Java服务端对emoji的支持

​