MySQL: Incorrect string value: '\xF0\xA4\xBD\x82'分析

一、問題由來

今天應用報錯：

### Error updating database.  Cause: java.sql.SQLException:
 Incorrect string value: '\xF0\xA4\x9E\x8F",...' for column 'message_content' at row 1 

二、初次分析

我們可以看到\xF0\xA4\x9E\x8F是4位元組很可能就是utf8mb4中某些生僻字的編碼。我們將表更改為utf8mb4後還是報錯。所以第一步一定要將欄位或者表修改為utf8mb4如下語句

alter table test101 convert to character set utf8mb4; or alter table test101 change `name` `name` varchar(20) CHARSET utf8mb4 DEFAULT NULL ; 

注意：這個語句是copy演算法會受到MDL LOCK的影響完全堵塞，不是online ddl如下：

mysql> alter table test204 algorithm=inplace, change `name` `name` 
varchar(20) CHARSET utf8 DEFAULT NULL ;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. 

三、詳細分析

隨後當然報錯還在繼續，然後我簡單描述一下我的分析的過程。具體知識見如下文章：

關於ORACLE和MYSQL中文字元亂碼的根源剖析]
http://blog.itpub.net/7728585/viewspace-2086271/  

我們當前環境為:

• 客戶端入庫字符集 utf8mb4
• 轉換字符集 utf8
• 儲存端字符集utf8mb4

對於這種情況的設定，對已某些生僻字比如：

insert into test204 values('????'); 

(????)的其utf8mb4編碼為：

就需要將四位元組的0Xf0a4bd82編碼轉換為utf8顯然是不可能的。因為utf8根本不認識而且還是三個位元組所以報錯如下：

[SQL] insert into test204 values('????');
[Err] 1366 - Incorrect string value: '\xF0\xA4\xBD\x82' for column 'name' at row 1 

為此實際上我們只需要做一個操作就是不做轉換及

set names utf8mb4; 

這樣環境如下：

• 客戶端入庫字符集 utf8mb4
• 轉換字符集 utf8mb4
• 儲存端字符集utf8mb4

insert into test204 values('????');
[SQL] insert into test204 values('????');
受影響的行: 1 時間: 0.218s 

插入成功。我們讀取資料檔案二進位制進行底層驗證取到的資料如下：

紅色部分為('????')這個字的十六進位制編碼。沒有問題資料入庫完成。

作者微信：