解决MYSQLIncorrectDECIMALvalue：0forcolumnatrow-1问
题
1.错误⽇志：
最近遇到⼀个很棘⼿的问题，也就是报 Incorrect DECIMAL value: '0' for column '' at row -1 异常，异常⽇志如下：
10:49:09.021 logback [95118531] DEBUG p.d.D.updateDeviceAscription - ==> Preparing: update devices_info SET front_name = ? ,coding = ? ,f
10:49:09.646 logback [95118531] DEBUG p.d.D.updateDeviceAscription - ==> Parameters: 维达(String), V4083876(String), 267831(Long), 维达(String), 10:49:10.061 logback [95118531] ERROR druid.sql.Statement - {conn-10002, pstmt-20009} execute error. update devices_info SET front_name = ?
,coding = ?
,fur_id = ?
,real_name = ?
,transfer_time = ?
where 1 = 1
and (CONVERT(sn,Decimal(60)) BETWEEN CONVERT(?,Decimal(60)) AND CONVERT(?,Decimal(60)) )
java.sql.SQLException: Incorrect DECIMAL value: '0' for column '' at row -1
sql.ateSQLException(SQLError.java:957)
sql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
sql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
sql.jdbc.MysqlIO.ndCommand(MysqlIO.java:2478)
sql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
sql.SQL(ConnectionImpl.java:2551)
sql.uteInternal(PreparedStatement.java:1861)
sql.ute(PreparedStatement.java:1192)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3051)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049)
at com.alibaba.druid.proxy.ute(PreparedStatementProxyImpl.java:167)
at com.alibaba.druid.ute(DruidPooledPreparedStatement.java:498)
flect.NativeMethodAccessorImpl.invoke0(Native Method)
flect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
flect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at flect.Method.invoke(Method.java:498)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.$ute(Unknown Source)
at org.utor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
at org.utor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
flect.NativeMethodAccessorImpl.invoke0(Native Method)
flect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
flect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at flect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
at batisplus.plugins.PerformanceInterceptor.intercept(PerformanceInterceptor.java:151)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at com.sun.proxy.$Proxy569.query(Unknown Source)
flect.NativeMethodAccessorImpl.invoke0(Native Method)
flect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
flect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at flect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy569.query(Unknown Source)
at org.utor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
at org.utor.BaExecutor.queryFromDataba(BaExecutor.java:324)
at org.utor.BaExecutor.query(BaExecutor.java:156)
at org.utor.CachingExecutor.query(CachingExecutor.java:109)我孰与城北徐公美
at org.utor.CachingExecutor.query(CachingExecutor.java:83)
at org.utor.CachingExecutor.query(CachingExecutor.java:83)
flect.NativeMethodAccessorImpl.invoke0(Native Method)
flect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
flect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at flect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy568.query(Unknown Source)
at org.apache.ibatis.ssion.defaults.DefaultSqlSession.lectList(DefaultSqlSession.java:148)
at org.apache.ibatis.ssion.defaults.DefaultSqlSession.lectList(DefaultSqlSession.java:141)
at org.apache.ibatis.ssion.defaults.DefaultSqlSession.lectOne(DefaultSqlSession.java:77)
flect.NativeMethodAccessorImpl.invoke0(Native Method)
flect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
flect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at flect.Method.invoke(Method.java:498)
batis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
at com.sun.proxy.$Proxy85.lectOne(Unknown Source)
batis.spring.SqlSessionTemplate.lectOne(SqlSessionTemplate.java:166)
at org.apache.ibatis.ute(MapperMethod.java:82)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy155.updateDeviceAscription(Unknown Source)
flect.NativeMethodAccessorImpl.invoke0(Native Method)
flect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
flect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at flect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy156.updateDeviceAscription(Unknown Source)
at com.st.modular.bizs.rvice.impl.DevicesInfoServiceImpl.deviceTransfer(DevicesInfoServiceImpl.java:268)
flect.NativeMethodAccessorImpl.invoke0(Native Method)
flect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
flect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at flect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at ansaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at ansaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
at ansaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy181.deviceTransfer(Unknown Source)
flect.NativeMethodAccessorImpl.invoke0(Native Method)
flect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
flect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at flect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at com.alibaba.druid.support.spring.stat.DruidStatInterceptor.invoke(DruidStatInterceptor.java:72)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy181.deviceTransfer(Unknown Source)
at com.st.ansfer(DevicesInfoController.java:371)
flect.NativeMethodAccessorImpl.invoke0(Native Method)
flect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
flect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at flect.Method.invoke(Method.java:498)
at org.hod.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
at org.hod.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
at org.springframework.web.hod.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
at org.springframework.web.hod.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) at org.springframework.web.hod.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
at org.springframework.web.hod.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
at org.springframework.web.hod.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.rvlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
at org.springframework.web.rvlet.DispatcherServlet.doService(DispatcherServlet.java:901)
at org.springframework.web.rvlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
at org.springframework.web.rvlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
at javax.rvlet.http.HttpServlet.rvice(HttpServlet.java:707)
at org.springframework.web.rvlet.FrameworkServlet.rvice(FrameworkServlet.java:846)
at javax.rvlet.http.HttpServlet.rvice(HttpServlet.java:790)
at io.undertow.rvlet.handlers.ServletHandler.handleRequest(ServletHandler.java:74)
at io.undertow.rvlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129)
at org.springframework.boot.web.filter.ApplicationContextHeaderFilter.doFilterInternal(ApplicationContextHeaderFilter.java:55)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at io.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.rvlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at com.st.modular.auth.filter.AuthFilter.parameterDecodeFilter(AuthFilter.java:250)
at com.st.modular.auth.filter.AuthFilter.doFilterInternal(AuthFilter.java:232)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at io.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.rvlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
猫尾红at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:123)
at io.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.rvlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at org.springframework.ace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:110)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at io.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.rvlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at org.springframework.curity.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
at org.springframework.curity.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
at org.springframework.curity.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
at org.springframework.curity.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
夏俊艾
at org.springframework.curity.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114)
at org.springframework.curity.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.curity.web.ssion.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
at org.springframework.curity.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.curity.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
at org.springframework.curity.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.curity.web.rvletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)
at org.springframework.curity.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.curity.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
at org.springframework.curity.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:96)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.curity.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.curity.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
at org.springframework.curity.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.curity.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:64)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.curity.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.t.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
at org.springframework.curity.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.t.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
金正昆商务礼仪
at org.springframework.curity.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.curity.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
at org.springframework.curity.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:347)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:263)
at io.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.rvlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at org.springframework.boot.actuate.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:106)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
贺辞
at io.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.rvlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at io.ManagedFilter.doFilter(ManagedFilter.java:61)
at io.undertow.rvlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
at io.undertow.rvlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)
at io.undertow.rvlet.handlers.curity.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
at io.undertow.rvlet.handlers.ServletChain$1.handleRequest(ServletChain.java:64)
at io.undertow.rvlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
at io.undertow.rvlet.handlers.curity.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:131)
at io.undertow.rvlet.handlers.curity.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)
at io.undertow.rver.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
at io.undertow.curity.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)
at io.undertow.rvlet.handlers.curity.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)
at io.undertow.curity.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)
高中教材at io.undertow.rvlet.handlers.curity.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)
at io.undertow.curity.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)
at io.undertow.rver.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
at io.undertow.rver.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
at io.undertow.rvlet.handlers.SessionRestoringHandler.handleRequest(SessionRestoringHandler.java:119)
at io.undertow.rvlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:292)
at io.undertow.rvlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:81)
at io.undertow.rvlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:138)
at io.undertow.rvlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:135)
at io.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)
at io.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
at io.undertow.rvlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:272)
at io.undertow.rvlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:81)
at io.undertow.rvlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:104)
at io.undertow.uteRootHandler(Connectors.java:336)
at io.undertow.rver.HttpServerExchange$1.run(HttpServerExchange.java:830)
at urrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at urrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
2：问题描述：
最开始代码是可以正常跑，后⾯不知道同事改了什么配置然后就不可以了，就开始报 Incorrect DECIMAL value: '0' for column '' at row -1 这个异常， 在⽹上也找了很多⽅法，⼤部分都是说明修改数据库配置⽂件的sql_mode，将STRICT_TRANS_TABLES这个校验给去掉，正常情况下修改后就解决了，（或者是使⽤round 或者 cast 函数来做转换，我⽤的是CONVERT这个函数，因为我这边需要转换的字符串长度⽐较长，所以只能⽤CONVERT来做转换，因为 ROUND 超过20位转换结果不正确，所以只能⽤CONVERT这个来做转换）。
↵修改了 数据库配置⽂件的sql_mode并重启服务后，直接执⾏sql是正常没有问题，执⾏SELECT @@SESSION.sql_mode; 结果如下：
但是程序跑还是报⼀样的错，纠结的半天，然后程序写sql来执⾏ SELECT @@SESSION.sql_mode; 发现修改
STRICT_TRANS_TABLES这个没有⽣效，结果如下：
清缓存，重启都试过了，但是还是不⾏，问题的具体情况就是这样。
3：解决⽅法
逻辑：对数据输⼊严格校验，避免出现转换值为null的情况，或者对于null的情况从逻辑上进⾏控制
因为直接修改配置sql_mode 没有⽣效了，所以只能从sql逻辑上去避免这个问题了，以下是修改后的demo
<!-- 更新设备归属 -->爬格子练习
<lect id="updateDeviceAscription" resultType="java.lang.Integer">
update devices_info SET front_name = #{params.frontName}
,coding = #{ding}
,fur_id = #{params.furId}
,real_name = #{alName}
口算乘法教案
,transfer_time = #{ansFerTime}
where 1 = 1
<choo>
<when test="params.snStart != null and params.snStart != '' and params.snEnd != null and params.snEnd != '' and params.source != '09' ">
<!-- and (CONVERT(sn,Decimal(60)) BETWEEN CONVERT(#{params.snStart},Decimal(60)) AND CONVERT(#{params.snEnd},Decimal(60)) ) -->
AND ((sn REGEXP '[^0-9.]')=0 AND CONVERT(sn,Decimal(60)) BETWEEN CONVERT(#{params.s
nStart},Decimal(60)) AND CONVERT(#{params.snEn </when>
<when test="params.snStart != null and params.snStart != '' and params.snEnd != null and params.snEnd != '' and params.source == '09' ">
AND ROUND(substring(sn, 3,8),20) BETWEEN ROUND(substring(#{params.snStart}, 3,8),20) AND ROUND(substring(#{params.snEnd}, 3,8),20)
AND substring(sn,1,4) = substring(#{params.snStart},1,4) AND substring(sn,1,4)=substring(#{params.snEnd},1,4)
</when>
<otherwi>
<if test="params.snStart != null and params.snStart != ''">
and sn in( ${params.snStart} )
</if>
<if test="params.snEnd != null and params.snEnd != '' ">
and sn in( ${params.snEnd} )
</if>
</otherwi>
</choo>
</lect>
由原来注释的写发变更成圈中的写法。
4：MySQL的sql_mode模式
mysql数据库的中有⼀个环境变量sql_mode，定义了mysql应该⽀持的sql语法，数据校验等。
KEY说明
ONLY_FULL_GROUP_BY 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的，因为列不在GROUP BY从句中
STRICT_TRANS_TABLES在该模式下，如果⼀个值不能插⼊到⼀个事务表中，则中断当前的操作，对⾮事务表不做任何限制
NO_ZERO_IN_DATE 在严格模式，不接受⽉或⽇部分为0的⽇期。如果使⽤IGNORE选项，我们为类似的⽇期插⼊’0000-00-00’。在⾮严格模式，可以接受该⽇期，但会⽣成警告。
NO_ZERO_DATE 在严格模式，不要将 '0000-00-00’做为合法⽇期。你仍然可以⽤IGNORE选项插⼊零⽇期。在⾮严格模式，可以接受该⽇期，但会⽣成警告
ERROR_FOR_DIVISION_BY_ZERO 在严格模式，在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产⽣错误(否则为警告)。如果未给出该模式，被零除时MySQL返回NULL。如果⽤到INSERT IGNORE或UPDATE IGNORE中，MySQL⽣成被零除警告，但操作结果为NULL。
NO_AUTO_CREATE_USER防⽌GRANT⾃动创建新⽤户，除⾮还指定了密码。
