舒畅 发表于 2016-10-19 10:08:06

MySQL java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x90 //注: 里面的**不是导致问题的特殊字符，因为那个特殊字符je也显示不出来，而且会截断内容，所以只好这么写了
背景：
数据库编码，建表编码，Content字段编码都设置为utf8，collation是默认的utf8_default(也尝试过修改为其他的，未果，似乎不是collation的问题)

mysql> status;
--------------
mysqlVer 14.14 Distrib 5.1.49, for debian-linux-gnu (i686) using readline 6.1
Connection id:1402357
Current database:**
Current user:**
SSL:Not in use
Current pager:stdout
Using outfile:''
Using delimiter:;
Server version:5.0.38 Debian etch distribution
Protocol version:10
Connection:** via TCP/IP
Server characterset:gbk
Db   characterset:utf8
Client characterset:utf8
Conn.characterset:utf8
TCP port:4307
Uptime:187 days 22 hours 51 min 18 sec
Threads: 16Questions: 409901760Slow queries: 12290Opens: 899Flush tables: 1Open tables: 246Queries per second avg: 25.242
--------------


现象：
插入的数据中如果含有某些特殊字符，会导致插入数据失败，例如字符串”测试**插入数据。。。“，在console中insert是正常的，但是使用java代码insert的时候报错：

2012-02-06 14:44:43,741 ERROR BlaBlaServiceImpl:110 - insertOrUpdateBlaBla failed!
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state ; error code ;
--- The error occurred in com/company/project/base/BlaBla/BlaBla.xml.
--- The error occurred while applying a parameter map.
--- Check the BlaBla.insertBlaBla-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x90</...' for column 'Content' at row 1; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/company/project/base/BlaBla/BlaBla.xml.   
--- The error occurred while applying a parameter map.
--- Check the BlaBla.insertBlaBla-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x90</...' for column 'Content' 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.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:203)
at org.springframework.orm.ibatis.SqlMapClientTemplate.insert(SqlMapClientTemplate.java:364)
at com.company.project.base.BlaBla.BlaBlaDaoImpl.insertBlaBla(BlaBlaDaoImpl.java:81)
at com.company.project.base.BlaBla.BlaBlaServiceImpl.insertBlaBla(BlaBlaServiceImpl.java:108)
at com.company.project.check.BlaBla.ReplyRecorder.record(ReplyRecorder.java:66)
at com.company.project.check.CheckingChain.run(CheckingChain.java:67)
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/company/project/base/BlaBla/BlaBla.xml.   
--- The error occurred while applying a parameter map.
--- Check the BlaBla.insertBlaBla-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x90</...' for column 'Content' at row 1
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:107)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java:393)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.java:82)
at org.springframework.orm.ibatis.SqlMapClientTemplate$8.doInSqlMapClient(SqlMapClientTemplate.java:366)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:200)
... 5 more
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x90</...' for column 'Content' at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3603)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3535)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1989)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1362)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:80)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteUpdate(MappedStatement.java:216)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:94)
... 9 more


问题的可能原因：(未证实)
mysql中规定utf8字符MaxLen=3，但是某些unicode字符转成utf8编码之后有4个字节，于是就杯具了

String c = "*";
byte[] bytes = c.getBytes("utf8");
for(byte b : bytes){
System.out.print(Integer.toHexString(0x00FF & b)+" ");
}
// 输出 f0 9f 8d 8e


mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset| Description               | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| utf8   | UTF-8 Unicode               | utf8_general_ci   |      3 |
+----------+-----------------------------+---------------------+--------+


解决方案：
修改Content字段为MEDIUMBLOB(原来是MEDIUMTEXT)，并且把SELECT语句修改成
SELECT CAST(Content AS CHAR CHARACTER SET utf8) AS Content ....
INSERT语句不需要修改，测试ok
参考资料

[*] http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html CAST函数用法
[*] http://bugs.mysql.com/bug.php?id=30131 07年讨论到10年，结论是: Not a bug
[*] http://stackoverflow.com/questions/2108824/mysql-incorrect-string-value-error-when-save-unicode-string-in-django 问题可能是因为某些unicode字符转成utf8之后变成了4个字节，utf8 max-length=3

页: [1]