【BUG記錄】MySQL插入Emoji表情報錯"Incorrect string value"

sum墨發表於2024-06-24
MySql

大家好呀，我是summo，這次的文章標題是一個Mysql資料庫的SQL錯誤，遇到的同學自然懂，沒遇到的同學希望你永遠也不要遇到。

一、錯誤說明

Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x90\xA6' for column 'name' at row 1這個錯誤通常是由於資料庫列的字符集設定不支援某些特殊字元（例如表情符號），導致在插入或更新資料時失敗。根源就是MySQL使用的是utf8編碼，utf8編碼預設每個字元3個位元組，而Emoji表情使用的Unicode編碼佔4個位元組，所以寫入資料庫的時候會寫入失敗並報錯。

二、錯誤分析

1. 分析表的字符集

有設計表結構經驗的同學應該都知道MySQL的儲存引擎，如InnoDB和MyISAM，但表的字符集不知道大家清不清楚。簡單來說表的字符集（Character Set）決定了表中可以儲存的字元型別及其編碼方式。最適合儲存表情符號的字符集是utf8mb4 ，所以遇到這個錯誤時首先檢查一下錯誤表的字符集。

（1）檢視 MySQL 的系統變數，以確認 MySQL 例項的預設字符集

SHOW VARIABLES LIKE 'character_set%';

（2）檢視錶的建立語句，從中可以看到表和列的字符集設定資訊

SHOW CREATE TABLE sample_table;

如果檢查後資料庫字符集和表字符集都是utf8mb4，但是新增表情符號還是失敗的話，那麼跟資料庫就沒有關係了。

2. 確認資料庫連線配置是否支援utf8mb4

這一步也簡單，就是確認spring.datasource.url中是否也包含 utf8mb4 配置，如果沒有的話，就加上useUnicode=true&characterEncoding=utf8mb4，如：

spring.datasource.url=jdbc:mysql://localhost:3306/your_database_name?useUnicode=true&characterEncoding=utf8mb4

網上搜尋的文章大部分都是這兩個解決思路，有些甚至會讓你去看MySQL的配置，修改後重新啟動MySQL，但最終可能卻並不能解決這個問題。如果你確定字符集是對的，資料庫連線配置也是對的，但報錯還是存在，那麼就在執行SQL之前執行一次set names utf8mb4語句。

3. set names utf8mb4怎麼加？

（1）沒有使用連線池

如果你沒有使用連線池，那麼在呼叫SQL之前，需要手動執行一次set names utf8mb4語句，如下：

conn = DriverManager.getConnection(url, user, password);
stmt = conn.createStatement();
stmt.execute("SET NAMES utf8mb4");

（2）Druid連線池

使用Druid連線池的話，那麼可以直接加一行配置就行了，如下:

spring.datasource.druid.connection-init-sqls=set names utf8mb4

（3）Hikari連線池

同Druid連線池，加一行配置就行了，如下：

spring.datasource.hikari.connection-init-sql=SET NAMES utf8mb4

其他連線池我就不列舉了，大同小異。

三、原理解釋

set names utf8mb4 透過確保客戶端、連線和伺服器之間的資料傳輸在同一字符集下進行，從根本上避免了字符集不匹配的問題，所以執行該命令能夠解決你插入表情符號時報錯的問題。

當你執行 set names utf8mb4 語句時，它實際上做了以下幾件事情：

  • 設定客戶端字符集：
    使MySQL客戶端的字符集為utf8mb4，這意味著客戶端（也就是你的應用程式）傳送給MySQL伺服器的資料將被解釋為utf8mb4格式。

  • 設定連線字符集：
    使資料庫連線層（也叫連線字符集）的編碼為utf8mb4。這保證了當資料在客戶端和伺服器之間傳輸時，被正確地編碼和處理。

  • 設定結果字符集：
    使MySQL伺服器返回給客戶端的查詢結果（比如SELECT語句的結果）都使用utf8mb4編碼。

四、小結一下

最初，我的記錄方式更偏向簡單的筆記，後來發現筆記太亂，為了提高查閱效率，我開始給每個部分加上標題和段落，這樣它們就演變成了短文。隨著時間的推移，我逐漸增加了內容的層次，加入了前因、詳細的分析過程以及小結，這些改進使我的記錄更加完善，最終變成了結構化的文章。

不過，在文章標題的選擇上，我有些猶豫。儘管起一個吸引人的標題能讓文章更有吸引力，但如果將來遇到類似的問題時，因為標題花哨、不明確而導致找不到解決方案，那就得不償失了。

相關文章