關於 MySQL UTF8 編碼下生僻字元插入失敗/假死問題的分析

標籤：android   style   blog   http   io   color   ar   os   使用   

1、問題：mysql 遇到某些中文插入異常

最近有同學反饋了這樣一個問題：

上述語句在指令碼中 load 入庫的時候會 hang 住，web 前端、命令列操作則要麼拋出 

Incorrect string value: ‘\xF0\xA1\x8B\xBE\xE5\xA2...‘ for column ‘name‘，

要麼存入MYSQL資料庫的內容會被截斷或者亂碼，而換做其它的中文則一切正常。

嗯，看起來有點奇怪哈，按理說 utf8 編碼是覆蓋了所有中文的，不應該出現上述問題。

2、原因：此 utf8 非彼 utf8

那我們先來看看插入異常的中文和正常的中文有啥區別：

可以看到上面插入異常的文字佔了 4 個位元組，而我們插入正常的則只佔了 3 個位元組。但是 utf8 字元編碼不就是可變長，支援 1-4 位元組的嗎？會和這個有關？

嗯，看看官方文檔就知道了：

10.1.10.6 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)
The character set named utf8 uses a maximum of three bytes per character and contains only BMP characters. As of MySQL 5.5.3, the utf8mb4 character set uses a maximum of four bytes per character supports supplemental characters:

• For a BMP character, utf8 and utf8mb4 have identical storage characteristics: same code values, same encoding, same length.
• For a supplementary character, utf8 cannot store the character at all, while utf8mb4 requires four bytes to store it. Since utf8 cannot store the character at all, you do not have any supplementary characters in utf8 columns and you need not worry about converting characters or losing data when upgrading utf8 data from older versions of MySQL.
• utf8mb4 is a superset of utf8.

由官方文檔可知，mysql 支援的 utf8 編碼最大字元長度為 3 位元組，如果遇到 4 位元組的寬字元就會插入異常了。三個位元組的 UTF-8 最大能編碼的 Unicode 字元是 0xffff，也就是 Unicode 中的基本多文種平面（BMP）。也就是說，任何不在基本多文本平面的 Unicode字元，都無法使用 Mysql 的 utf8 字元集儲存。包括 Emoji 表情（Emoji 是一種特殊的 Unicode 編碼，常見於 ios 和 android 手機上），和很多不常用的漢字，以及任何新增的 Unicode 字元等等。

最初的 UTF-8 格式使用一至六個位元組，最大能編碼 31 位字元。最新的 UTF-8 規範只使用一到四個位元組，最大能編碼21位，正好能夠表示所有的 17個 Unicode 平面。
utf8 是 Mysql 中的一種字元集，只支援最長三個位元組的 UTF-8字元，也就是 Unicode 中的基本多文本平面。
Mysql 中的 utf8 為什麼只支援持最長三個位元組的 UTF-8字元呢？我想了一下，可能是因為 Mysql 剛開始開發那會，Unicode 還沒有輔助平面這一說呢。那時候，Unicode 委員會還做著 “65535 個字元足夠全世界用了”的美夢。Mysql 中的字串長度算的是字元數而非位元組數，對於 CHAR 資料類型來說，需要為字串保留足夠的長。當使用 utf8 字元集時，需要保留的長度就是 utf8 最長字元長度乘以字串長度，所以這裡理所當然的限制了 utf8 最大長度為 3，比如 CHAR(100)  Mysql 會保留 300位元組長度。至於後續的版本為什麼不對 4 位元組長度的 UTF-8 字元提供支援，我想一個是為了向後相容性的考慮，還有就是基本多文種平面之外的字元確實很少用到。
要在 Mysql 中儲存 4 位元組長度的 UTF-8 字元，需要使用 utf8mb4 字元集，但只有 5.5.3 版本以後的才支援（查看版本： select version();）。我覺得，為了擷取更好的相容性，應該總是使用 utf8mb4 而非 utf8.  對於 CHAR 類型資料，utf8mb4 會多消耗一些空間，根據 Mysql 官方建議，使用 VARCHAR  替代 CHAR。

3、解決方案

知道原因了，當然得談談有哪些方案可以解決開頭的問題。

3.1 升級 mysql 版本，並將utf8字元集升級到utf8mb4

升級你的 mysql 到 5.5.3 之後即可，查看當前環境版本： 

select version();

MySQL在5.5.3之後增加了這個utf8mb4的編碼，mb4就是most bytes 4的意思，專門用來相容四位元組的unicode。好在utf8mb4是utf8的超集，除了將編碼改為utf8bp4外不需要做其他轉換。當然，為了節省空間的，一般情況下使用utf8也就夠了。
所以好的技術就是，採用對當前而言最好的解決方案，然後再逐步迭代滿足新的需求。

3.1.1 直接修改表結構

--修改資料庫字元集ALTER DATABASE test CHARACTER SET = utf8mb4;--修改表字元集alter table test convert to character set utf8mb4;--修改字元字元集ALTER TABLE `test` CHANGE COLUMN `name` `name` varchar(12) CHARACTER SET utf8mb4;

3.1.2 修改資料庫預設配置

[client]default-character-set = utf8mb4[mysqld]character-set-server=utf8mb4collation-server=utf8mb4_unicode_ci[mysql]default-character-set = utf8mb4

P.S. 如果你使用的是java語言，需要將jdbc驅動包升級到 mysql-connector-java-5.1.14.jar。
3.2 強行過濾掉生僻字串

從業務和技術的角度綜合考慮，可以做個折中，將生僻字串提前過濾掉，因為這類字串本來就使用的很少，即使存進資料庫了，展示、查詢的時候也會多少有其它的問題，不如直接過濾掉，mysql 不支援四位元組的 utf8 一方面可能是曆史包袱，另一方面估計也是為了省空間。

3.2.1 shell 過濾

比如，咱們可以直接先用 sed、awk、python、perl 處理下要 load 入庫的指令碼，將四位元組的生僻字全過濾再入庫：

3.2.2 java 中的過濾操作

判斷MySql支援Unicode字元的方法，偽碼為：

for i=1->nint c=str.codePointAt(i);if (c<0x0000||c>0xffff) {    return false;}

稍作修改即可。 3.3 避開用戶端亂碼：二進位儲存與查詢

為避免web頁面或者終端本身不支援utf8四位元組，可以採用二進位的方式來操作

create table t1(name varchar(20) charset utf8mb4);insert into t1 values(0xF0A09080);set charset binary;select * from t1;

4、應用、系統對 utf-8 四位元組字元的支援

最後順便總結下4位元組utf8字元的系統支援情況:

• windows xp: 我所測試的xp系統都不支援4位元組utf8字元, 瀏覽器用預留位置顯示
• windows 7: 支援4位元組utf8字元
• mac os x: 支援4位元組utf8字元
• iPhone/iPad: 支援4位元組utf8字元
• 許多的資料庫軟體或者shell終端都不支援4位元組utf8字元, 比如 Navicat、SecureCRT

以 php 情境為例說明:

• php串連會話設定編碼utf8, mysql後端欄位為text character set utf8: 寫入內容從4位元組utf8字元處被截斷
• php串連會話設定編碼utf8mb4, mysql後端欄位為text character set utf8: 內容可以完整寫入, 但是4位元組utf8字元被替換為問號"?"
• php串連會話設定編碼utf8mb4, mysql後端欄位為text character set utf8mb4: 完整支援4位元組utf8字元

從平台支援上來看, 隨著winxp的逐步淘汰, 對4位元組utf8字元的支援還是有必要的.
官方手冊對utf8mb4字元的說明中指出, utf8mb4是utf8的超集, 因此可放心升級. 5、最後的問題

看到這裡，不知道細心的你有沒有發現，本文的代碼為毛都是圖呢？要知道我自己寫文章很少把代碼搞成圖的，那是因為。。。

哇哈哈，真是哪壺不開提哪壺啊。。。。。。。。。

6、Refer：

[1] 談談字元集與字元編碼

http://my.oschina.net/leejun2005/blog/232732#OSC_h3_4

[2] sed matching any ascii code/hex byte

http://forums.gentoo.org/viewtopic-t-770576-start-0.html

[3] 10.1.10.6 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)

http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html

[4] Mysql 中的 utf8

http://blog.haipo.me/?p=1149

[5] Mysql中4位元組UTF8字元集問題

http://bbs.chinaunix.net/thread-3766853-1-1.html

[6] MySQL，UTF-8和emoji

http://shadyxu.com/mysql_utf8.html

[7] 關於MYSQL截斷內容問題解決

http://www.momotime.me/2014/10/mysql-utf8mb4/

[8] MySQL儲存4位元組字元

http://www.web-tinker.com/article/20643.html

[9] 關於UTF-8編碼的MySql拋出incorrect string value的問題

http://blog.csdn.net/tannasu/article/details/8064021

[10] 關於MYSQL截斷內容問題解決

http://daiweiyang.com/mysql_data_truncated/

關於 MySQL UTF8 編碼下生僻字元插入失敗/假死問題的分析