mysql insert sqlexception_MySQL插入数据异常分析 SQL state [HY000]; error code [1366];

关键词: java mysql 插入异常 1366 HY000

今天分享一个mysql数据插入异常的问题，由于工作排期问题，没有时间去深入研究这个问题，也因此困扰了我很久，问题大概是这样的，当我们把4个字节的字符插入到数据表列字符集为3个字节的表中时，会抛出以下异常，在没有异常捕获的情况下，导致程序崩溃以及事务持续性不健全

环境

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，大致意思就是说对某数据库表列ERROR 插入了不正确的字符串值\xF4\x80\x80\x8062...，那到底为什么是错误的呢，首先我们可以看到那个不正确的字符串的特征，是一个4字节的十六进制的字符\xF4\x80\x80\x80，再查查表字符集发现为UTF-8，到这里还没发现根本问题，再仔细翻看了mysql官方version release notes，发现在version 5.5.3中发现有如下发布改进记录：

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.

可以发现在version 5.5.3之后，增加了一种和utf-8类似的字符集utf8mb4，并且它的编码支持4个字节每个字符，再看看mysql的字符集所支持的字节范围：

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 |

...

可以发现utf8最大支持3个字节的字符，到这里就基本确定是因为这个字符集设定所引起的异常，于是我试着修改对应列的字符集，这里的字符集分为表级字符集以及列级字符集，如果没有特别设定，列级字符集默认继承表级字符集，于是为了降低产品的影响面，我试着将发生异常的列的字符集改成utf8mb4，

ALTER TABLE ``.`

VARCHAR(500) CHARACTER SET 'utf8mb4' NULL DEFAULT NULL ;

然后再重现了一下上面的异常，发现数据插入成功。至此，问题解决。

总结，这个异常的发生是由于4字节的字符插入到字符集为3字节的列中，在插入数据之前，字符集验证失败，字节溢出导致插入失败异常。

解决方法： 将对应列的字符集改成utf8mb4

——END——

作者 : Eason，专注各种技术、平台、集成，不满现状，喜欢改改改

文章、技术合作，大胆的扫一扫，害羞的请邮件

Email : eason.lau02@hotmail.com