熊哈哈唉 2022-04-19 11:36 采纳率: 80%
浏览 67
已结题

bigint超出范围

问题遇到的现象和发生背景

sql语句报错

问题相关代码,请勿粘贴截图
update PNEC_AFTERSALE.HMCS_REFUND_ORDERS rfo
        set rfo.push_oms_status='C',
        rfo.UPDATE_DATE=(NOW() - STR_TO_DATE('1970-1-1 8', '%Y-%m-%d %H')) *
                       86400000 + CAST(cast(DATE_FORMAT(now(3),'%f') AS CHAR(3)) AS SIGNED   INTEGER)
        where rfo.refund_order_no = #{refundId, jdbcType = VARCHAR}
        and OBJECT_VERSION_NUMBER = #{objectVersionNumber,jdbcType=DECIMAL}
        <if test="_parameter.containsKey('refundStatus')">
            and rfo.refund_status = #{refundStatus, jdbcType = VARCHAR}
        </if>


运行结果及报错内容

Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: BIGINT value is out of range in '((now() - str_to_date('1970-1-1 8','%Y-%m-%d %H')) * 86400000)'

; Data truncation: BIGINT value is out of range in '((now() - str_to_date('1970-1-1 8','%Y-%m-%d %H')) * 86400000)'; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: BIGINT value is out of range in '((now() - str_to_date('1970-1-1 8','%Y-%m-%d %H')) * 86400000)'
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:104) ~[spring-jdbc-5.2.2.RELEASE.jar!/:5.2.2.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.2.RELEASE.jar!/:5.2.2.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.2.RELEASE.jar!/:5.2.2.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.2.RELEASE.jar!/:5.2.2.RELEASE]
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) ~[mybatis-spring-1.3.2.jar!/:1.3.2]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) ~[mybatis-spring-1.3.2.jar!/:1.3.2]
at com.sun.proxy.$Proxy119.update(Unknown Source) ~[?:?]
at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294) ~[mybatis-spring-1.3.2.jar!/:1.3.2]
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63) ~[mybatis-3.4.6.jar!/:3.4.6]
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) ~[mybatis-3.4.6.jar!/:3.4.6]
at com.sun.proxy.$Proxy151.updateSuccess(Unknown Source) ~[?:?]
at com.hand.hmall.afterSale.service.impl.RefundOrderPushToOmsServiceImpl.pushRefundOrders(RefundOrderPushToOmsServiceImpl.java:154) ~[classes!/:0.0.1-SNAPSHOT]
at com.hand.hmall.afterSale.service.impl.RefundOrderPushToOmsServiceImpl$$FastClassBySpringCGLIB$$6ec47a65.invoke() ~[classes!/:0.0.1-SNAPSHOT]
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.2.2.RELEASE.jar!/:5.2.2.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:769) ~[spring-aop-5.2.2.RELEASE.jar!/:5.2.2.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.2.2.RELEASE.jar!/:5.2.2.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747) ~[spring-aop-5.2.2.RELEASE.jar!/:5.2.2.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366) ~[spring-tx-5.2.2.RELEASE.jar!/:5.2.2.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:99) ~[spring-tx-5.2.2.RELEASE.jar!/:5.2.2.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.2.RELEASE.jar!/:5.2.2.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747) ~[spring-aop-5.2.2.RELEASE.jar!/:5.2.2.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689) ~[spring-aop-5.2.2.RELEASE.jar!/:5.2.2.RELEASE]
at com.hand.hmall.afterSale.service.impl.RefundOrderPushToOmsServiceImpl$$EnhancerBySpringCGLIB$$90b91518.pushRefundOrders() ~[classes!/:0.0.1-SNAPSHOT]

我的解答思路和尝试过的方法
我想要达到的结果
  • 写回答

1条回答 默认 最新

  • 黑白码农 2022-04-19 11:58
    关注
    (NOW() - STR_TO_DATE('1970-1-1 8', '%Y-%m-%d %H')) *
                           86400000 + CAST(cast(DATE_FORMAT(now(3),'%f') AS CHAR(3)) AS SIGNED   INTEGER)
    

    错误原因:你这个值超过了bigint的20亿范围了, 为啥要用时间戳乘以86400000 ;你这个NOW() - STR_TO_DATE('1970-1-1 8', '%Y-%m-%d %H')算出来的和当前时间戳就相差8小时啊,是你写错了,还是我没看懂你的目的

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

问题事件

  • 系统已结题 4月27日
  • 已采纳回答 4月19日
  • 创建了问题 4月19日

悬赏问题

  • ¥60 Qcustomplot绘制实时动态曲线
  • ¥20 运用matlab画x-y图
  • ¥15 用idea运行项目,运行tomcat报错:断言失败
  • ¥15 Sqlserver查询链接服务器数据问题
  • ¥15 Bibtex4Word 引用中文文献
  • ¥20 用opencv c/c++ 转换成灰度图,然后做一下直方图均衡,输出mp4文件
  • ¥20 matlab中的双层数值积分
  • ¥50 服务器打印水晶报表问题
  • ¥30 gradle环境下javafx项目如何使用druid连接池
  • ¥15 服务器打印水晶报表问题