關(guān)鍵詞: java mysql 插入異常 1366 HY000

今天分享一個(gè)mysql數(shù)據(jù)插入異常的問題，由于工作排期問題，沒有時(shí)間去深入研究這個(gè)問題，也因此困擾了我很久，問題大概是這樣的，當(dāng)我們把4個(gè)字節(jié)的字符插入到數(shù)據(jù)表列字符集為3個(gè)字節(jié)的表中時(shí)，會(huì)拋出以下異常，在沒有異常捕獲的情況下，導(dǎo)致程序崩潰以及事務(wù)持續(xù)性不健全

環(huán)境

mysql version: 5.6.35
JDK : 1.6.0_38

異常拋出:

nested exception is java.sql.SQLException: Incorrect string value: '\xF4\x80\x80\x8062...' for column 'ERROR' at row 1 
     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83) 
     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) 
     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) 
     at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424) 
     at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410) 
     at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424) 
     at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374) 
     at org.springframework.orm.hibernate3.HibernateTemplate.saveOrUpdate(HibernateTemplate.java:748) 
     at com.cargosmart.sime.core.persistence.dao.BaseDao.saveOrUpdate(BaseDao.java:39) 
     at ....
    Caused by: java.sql.SQLException: Incorrect string value: '\xF4\x80\x80\x8062...' for column 'ERROR' at row 1 
     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) 

問題分析：
我們可以看到上面異常中：Incorrect string value: '\xF4\x80\x80\x8062...' for column 'ERROR' at row 1，大致意思就是說對(duì)某數(shù)據(jù)庫表列ERROR 插入了不正確的字符串值\xF4\x80\x80\x8062...，那到底為什么是錯(cuò)誤的呢，首先我們可以看到那個(gè)不正確的字符串的特征，是一個(gè)4字節(jié)的十六進(jìn)制的字符\xF4\x80\x80\x80，再查查表字符集發(fā)現(xiàn)為UTF-8，到這里還沒發(fā)現(xiàn)根本問題，再仔細(xì)翻看了mysql官方version release notes，發(fā)現(xiàn)在version 5.5.3中發(fā)現(xiàn)有如下發(fā)布改進(jìn)記錄：

• Changes in MySQL 5.5.3 (2010-03-24, Milestone 3)

Incompatible Change: The Unicode implementation has been extended to provide support for 
supplementary characters that lie outside the Basic Multilingual Plane (BMP). Noteworthy features:

utf16 and utf32 character sets have been added. These correspond to the UTF-16 and UTF-32 
encodings of the Unicode character set, and they both support supplementary characters.

The utf8mb4 character set has been added. This is similar to utf8, but its encoding allows up to 
four bytes per character to enable support for supplementary characters.

可以發(fā)現(xiàn)在version 5.5.3之后，增加了一種和utf-8類似的字符集utf8mb4，并且它的編碼支持4個(gè)字節(jié)每個(gè)字符，再看看mysql的字符集所支持的字節(jié)范圍：

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
...
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
...
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
...

可以發(fā)現(xiàn)utf8最大支持3個(gè)字節(jié)的字符，到這里就基本確定是因?yàn)檫@個(gè)字符集設(shè)定所引起的異常，于是我試著修改對(duì)應(yīng)列的字符集，這里的字符集分為表級(jí)字符集以及列級(jí)字符集，如果沒有特別設(shè)定，列級(jí)字符集默認(rèn)繼承表級(jí)字符集，于是為了降低產(chǎn)品的影響面，我試著將發(fā)生異常的列的字符集改成utf8mb4，

ALTER TABLE `<db name>`.`<table name>` CHANGE COLUMN `ERROR` `ERROR` 
VARCHAR(500) CHARACTER SET 'utf8mb4' NULL DEFAULT NULL  ;

然后再重現(xiàn)了一下上面的異常，發(fā)現(xiàn)數(shù)據(jù)插入成功。至此，問題解決。

總結(jié)，這個(gè)異常的發(fā)生是由于4字節(jié)的字符插入到字符集為3字節(jié)的列中，在插入數(shù)據(jù)之前，字符集驗(yàn)證失敗，字節(jié)溢出導(dǎo)致插入失敗異常。
解決方法： 將對(duì)應(yīng)列的字符集改成utf8mb4

——END——
作者 : Eason，專注各種技術(shù)、平臺(tái)、集成，不滿現(xiàn)狀，喜歡改改改
文章、技術(shù)合作，大膽的掃一掃，害羞的請(qǐng)郵件
Email : eason.lau02@hotmail.com