在mybatis写动态sql的时修改涉及时间类型的时候报错Data truncation_error updating database. cause: com.mysql.cj.jdbc.-CSDN博客

文章讲述了在MyBatis中使用动态SQL时遇到的时间类型比较问题，由于忘记处理空值导致数据类型错误，给出了将空值判断从`xxxDate!`=nullandxxxDate=`修改为`xxxDate!`=null的解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

问题场景：

在mybatis写动态sql的时修改涉及时间类型的时候（纪录bug）

问题描述`

忘记时间不能与空串比较，没修改比较类型
没报类型比较异常
报日期值异常


<!--错误写法-->
 <if test="xxxDate!=null and xxxxDate=''">
                xxx_date=#{xxxDate}
</if>
<if test="xxxDate!=null and xxxDate=''">
                xxx_date=#{xxxDate}
 </if>
 <!--错误写法-->

报错如下

Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect date value: ‘’ for column ‘xxx’ at row 1

The error may involve defaultParameterMap

The error occurred while setting parameters

SQL: update xxx

Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect date value: ‘’ for column ‘xxx’ at row 1

; Data truncation: Incorrect date value: ‘’ for column ‘xxx_date’ at row 1; nested exception is com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect date value: ‘’ for column ‘xxx_date’ at row 1
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:104)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate $SqlS ess i o n I n t erce pt or . in v o k e (SqlS ess i o n T e m pl a t e . ja v a : 446) a t co m . s u n . p ro x y .$ Proxy36.update(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy45.update(Unknown Source)
at com.atguigu.base.BaseServiceImpl.update(BaseServiceImpl.java:35)
at com.alibaba.dubbo.common.bytecode.Wrapper3.invokeMethod(Wrapper3.java)
at com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:45)
at com.alibaba.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:71)
at com.alibaba.dubbo.config.invoker.DelegateProviderMetaDataInvoker.invoke(DelegateProviderMetaDataInvoker.java:48)
at com.alibaba.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:52)
at com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:61)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:74)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:41)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:77)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:71)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:131)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:37)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:37)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol $1. re pl y (D u bb o P ro t oco l . ja v a : 98) a t co m . a l ibaba . d u bb o . re m o t in g . e x c han g e . s u pp or t . h e a d er . He a d er E x c han g eH an d l er . han d l e R e q u es t (He a d er E x c han g eH an d l er . ja v a : 96) a t co m . a l ibaba . d u bb o . re m o t in g . e x c han g e . s u pp or t . h e a d er . He a d er E x c han g eH an d l er . rece i v e d (He a d er E x c han g eH an d l er . ja v a : 168) a t co m . a l ibaba . d u bb o . re m o t in g . t r an s p or t . Deco d eH an d l er . rece i v e d (Deco d eH an d l er . ja v a : 50) a t co m . a l ibaba . d u bb o . re m o t in g . t r an s p or t . d i s p a t c h er . C hann e lE v e n tR u nnab l e . r u n (C hann e lE v e n tR u nnab l e . ja v a : 79) a t ja v a . u t i l . co n c u rre n t . T h re a d P oo lE x ec u t or . r u nW or k er (T h re a d P oo lE x ec u t or . ja v a : 1149) a t ja v a . u t i l . co n c u rre n t . T h re a d P oo lE x ec u t or$ Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect date value: ‘’ for column ‘xxx’ at row 1
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
at sun.reflect.GeneratedMethodAccessor44.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy. $P ro x y 50. e x ec u t e (U nkn o w n S o u rce) a t or g . a p a c h e . iba t i s . e x ec u t or . s t a t e m e n t . P re p a re d St a t e m e n t H an d l er . u p d a t e (P re p a re d St a t e m e n t H an d l er . ja v a : 46) a t or g . a p a c h e . iba t i s . e x ec u t or . s t a t e m e n t . R o u t in g St a t e m e n t H an d l er . u p d a t e (R o u t in g St a t e m e n t H an d l er . ja v a : 74) a t or g . a p a c h e . iba t i s . e x ec u t or . S im pl e E x ec u t or . d o U p d a t e (S im pl e E x ec u t or . ja v a : 50) a t or g . a p a c h e . iba t i s . e x ec u t or . B a se E x ec u t or . u p d a t e (B a se E x ec u t or . ja v a : 117) a t or g . a p a c h e . iba t i s . e x ec u t or . C a c hin g E x ec u t or . u p d a t e (C a c hin g E x ec u t or . ja v a : 76) a t s u n . re f l ec t . N a t i v e M e t h o d A ccessor I m pl . in v o k e 0 (N a t i v e M e t h o d) a t s u n . re f l ec t . N a t i v e M e t h o d A ccessor I m pl . in v o k e (N a t i v e M e t h o d A ccessor I m pl . ja v a : 62) a t s u n . re f l ec t . De l e g a t in g M e t h o d A ccessor I m pl . in v o k e (De l e g a t in g M e t h o d A ccessor I m pl . ja v a : 43) a t ja v a . l an g . re f l ec t . M e t h o d . in v o k e (M e t h o d . ja v a : 498) a t or g . a p a c h e . iba t i s . pl ug in . Pl ug in . in v o k e (Pl ug in . ja v a : 63) a t co m . s u n . p ro x y .$ Proxy48.update(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
… 35 more

原因分析：

时间不能和空串比较，用快捷键必须细心

解决方案：

修改动态sql比较类型

 <if test="xxxDate!=null ">
	xxx_date=#{xxxDate},
 </if>