日期:2014-05-16  浏览次数:20780 次

MySQL java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x90</...' for
//注: 里面的**不是导致问题的特殊字符,因为那个特殊字符je也显示不出来,而且会截断内容,所以只好这么写了

背景:
数据库编码,建表编码,Content字段编码都设置为utf8,collation是默认的utf8_default(也尝试过修改为其他的,未果,似乎不是collation的问题)
mysql> status;
--------------
mysql  Ver 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: 16  Questions: 409901760  Slow queries: 12290  Opens: 899  Flush tables: 1  Open tables: 246  Queries 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 [HY000]; error code [1366];
--- 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.or