MySQL截断错误：Truncated incorrect DOUBLE value '4303712665A'排查求助

错误日志 #

[http-nio-8093-exec-6] INFO  com.jfz.pc.interceptor.SqlCostInterceptor  62 - ============>SQL：[UPDATE jfz_simu_vip_net_remind SET prd_code='P7raoxwpmo', is_update_net='1', uid='5314221707', update_time='1706669051', state='1', phone='【无法展示用户手机号，已隐藏】', trigger_mode='2', platform='0' WHERE (uid = '5314221707' AND prd_code = 'P7raoxwpmo'), 执行耗时: 8 ms]==========
[http-nio-8093-exec-6] INFO  com.jfz.pc.aspect.LogPrintAspect  60 - ================= End ==================

[http-nio-8093-exec-6] ERROR com.jfz.pc.advice.ExceptionControllerAdvice  68 - 接口访问异常[/utils/choice/add]
org.springframework.dao.DataIntegrityViolationException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: '4303712665A'
### The error may exist in com/jfz/pc/mapper/result/JfzSimuVipNetRemindMapper.java (best guess)
### The error may involve com.jfz.pc.mapper.result.JfzSimuVipNetRemindMapper.update-Inline
### The error occurred while setting parameters
### SQL: UPDATE jfz_simu_vip_net_remind  SET prd_code=?, is_update_net=?,    uid=?, update_time=?, state=?, phone=?,  trigger_mode=?, platform=?      WHERE (uid = ? AND prd_code = ?)
### Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: '4303712665A'
; Data truncation: Truncated incorrect DOUBLE value: '4303712665A'
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:100)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
        at jdk.proxy2/jdk.proxy2.$Proxy125.update(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:288)
        at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:64)
        at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
        at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
        at jdk.proxy2/jdk.proxy2.$Proxy212.update(Unknown Source)
        at jdk.internal.reflect.GeneratedMethodAccessor2031.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:568)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
        at org.springframework.aop.aspectj.AspectJAfterAdvice.invoke(AspectJAfterAdvice.java:49)

表结构 #

CREATE TABLE `jfz_simu_vip_net_remind` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT ,
      `prd_code` varchar(20) NOT NULL ,
      `is_update_net` tinyint(4) DEFAULT NULL ,
      `is_net_to_price` tinyint(4) DEFAULT NULL ,
      `net_up` decimal(22,8) DEFAULT NULL ,
      `net_down` decimal(22,8) DEFAULT NULL ,
      `uid` varchar(20) NOT NULL ,
      `update_time` int(11) DEFAULT NULL ,
      `state` tinyint(4) DEFAULT '1' ,
      `phone` varchar(20) DEFAULT NULL ,
      `email` varchar(50) DEFAULT NULL ,
      `trigger_mode` tinyint(2) DEFAULT '2' ,
      `platform` tinyint(2) DEFAULT NULL ,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `prd_code` (`prd_code`) USING BTREE,
  KEY `uid` (`uid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

相关代码（MyBatis-Plus） #

public boolean addSimuVipNetRemind(Long uid, String prdCode, String phone, int platform) {
    LambdaQueryWrapper<JfzSimuVipNetRemind> wrapper = Wrappers.lambdaQuery(JfzSimuVipNetRemind.class)
            .eq(JfzSimuVipNetRemind::getUid, uid)
            .eq(JfzSimuVipNetRemind::getPrdCode, prdCode)
            .last("limit 1");

    JfzSimuVipNetRemind one = getOne(wrapper);
    if (Objects.isNull(one)) {
        one = new JfzSimuVipNetRemind();
    }
    one.setUid(String.valueOf(uid));
    one.setPrdCode(prdCode);
    one.setUpdateTime((int) (new Date().getTime() / 1000));
    one.setPhone(phone);
    one.setIsUpdateNet(JfzSimuVipNetRemind.IS_UPDATE_NET_TRUE);
    one.setTriggerMode(JfzSimuVipNetRemind.TRIGGER_MODE_MANUALLY);
    one.setState(JfzSimuVipNetRemind.STATE_OK);
    one.setPlatform(platform);

    LambdaQueryWrapper<JfzSimuVipNetRemind> updateWrapper = Wrappers.lambdaQuery(JfzSimuVipNetRemind.class)
            .eq(JfzSimuVipNetRemind::getUid, uid)
            .eq(JfzSimuVipNetRemind::getPrdCode, prdCode);
    return this.saveOrUpdate(one, updateWrapper);
}

问题核心 #

拦截器打印的SQL里没有'4303712665A'这个值，但数据库里存在'4303712665'这个uid，不清楚错误值的来源和末尾的'A'是怎么产生的。

原因分析 #

1. 字段类型不匹配：数据库uid是varchar类型，但实体类中uid大概率是Long类型。MyBatis在绑定参数时，会尝试将数据库中字符串类型的uid转换为Long，当遇到'4303712665A'这种含字母的字符串时，就会触发截断错误。
2. 拦截器打印范围有限：拦截器只打印了getOne的SQL，但报错的是saveOrUpdate内部执行的更新逻辑。saveOrUpdate会先根据updateWrapper查询数据，若数据库中存在非法格式的uid记录，查询时就会触发类型转换错误。
3. 参数传递逻辑冲突：代码中updateWrapper直接传入Long类型的uid，MyBatis会按数值类型处理参数，和数据库的字符串类型uid进行比较时，MySQL会隐式转换字符串为数值，遇到带字母的uid就会报错。

解决方案 #

1. 修正实体类字段类型：将JfzSimuVipNetRemind中的uid字段类型从Long改为String，和数据库varchar(20)类型保持一致，避免不必要的类型转换。
2. 统一参数传递格式：在所有Wrapper查询中，将Long类型的uid转为String后再传入：

   LambdaQueryWrapper<JfzSimuVipNetRemind> wrapper = Wrappers.lambdaQuery(JfzSimuVipNetRemind.class)
           .eq(JfzSimuVipNetRemind::getUid, String.valueOf(uid))
           .eq(JfzSimuVipNetRemind::getPrdCode, prdCode)
           .last("limit 1");
   

   同理修改updateWrapper的参数传递逻辑。
3. 清理数据库脏数据：检查jfz_simu_vip_net_remind表中的uid字段，找出'4303712665A'这类非法记录，清理或修正数据。
4. 开启MyBatis DEBUG日志：调整日志级别为DEBUG，查看完整的参数绑定过程，确认报错时实际执行的SQL和参数值，精准定位问题环节。

内容的提问来源于stack exchange，提问作者robotCode