Data truncation: Data truncated for column异常的解决

最新推荐文章于 2024-04-01 20:35:20 发布

iteye_4589

最新推荐文章于 2024-04-01 20:35:20 发布

阅读量512

点赞数

文章标签： iBATIS MySQL Java JDBC Eclipse

本文链接：https://blog.csdn.net/iteye_4589/article/details/82006793

版权

从excel 读取数据插入mysql库中，
莫名其妙地少数几条记录的中文字段用程序插入不了，而用客户端写sql可以成功，用肉眼看没发现那几条记录数据有什么异常。
Error: 错误:basename=福×××× 口cellId_b=46错误原因：
--- The error occurred in com/fsti/cdr/persistence/sqlmapdao/sql/ImportFile.xml.
--- The error occurred while applying a parameter map.
--- Check the insertCell-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'BASENAME' at row 1
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/fsti/cdr/persistence/sqlmapdao/sql/ImportFile.xml.
--- The error occurred while applying a parameter map.
--- Check the insertCell-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'BASENAME' at row 1
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'BASENAME' at row 1
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:500)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:85)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:62)
at com.fsti.cdr.service.ImportFileService.insertCell(ImportFileService.java:42)
at com.fsti.cdr.zte.parser.ExcelParser$1.run(ExcelParser.java:124)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'BASENAME' at row 1
at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:758)
at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2128)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:998)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:84)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
... 6 more

Caused by:
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'BASENAME' at row 1
at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:758)
at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2128)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:998)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:84)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:500)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:85)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:62)
at com.fsti.cdr.service.ImportFileService.insertCell(ImportFileService.java:42)
at com.fsti.cdr.zte.parser.ExcelParser$1.run(ExcelParser.java:124)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)

Caused by:
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'BASENAME' at row 1
at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:758)
at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2128)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:998)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:84)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:500)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:85)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:62)
at com.fsti.cdr.service.ImportFileService.insertCell(ImportFileService.java:42)
at com.fsti.cdr.zte.parser.ExcelParser$1.run(ExcelParser.java:124)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)

将字段改为varchar(255)和设置默认值都不能解决。其实中文值得程度没有超过255，不是长度不够引起。
将字段类型改为varbinary能解决上面的问题。
读取时要进行转换：convert(col using gb2312)