Mysql Java JDBC 执行SQL报错 Data truncation: Truncated incorrect DOUBLE value: ‘张三‘

场景：当使用MySQL客户端执行SQL 可以正常执行，但是当使用Mysql Java JDBC 执行SQL报错 Data truncation: Truncated incorrect DOUBLE value: '张三'

参考文献：史上最全的mysql jDBC参数 - 王小明的个人页面 - OSCHINA - 中文开源技术交流社区

异常信息：

09:47:09  [UPDATE - 0 rows, 0.005 secs]  [Code: 1292, SQL State: 22001]  Data truncation: Truncated incorrect DOUBLE value: '张三'
Code: 1292 SQL State: 22007 --- Truncated incorrect DOUBLE value: '张三'
Code: 1105 SQL State: HY000 --- Unknown error
... 1 statement(s) executed, 0 rows affected, exec/fetch time: 0.005/0.000 sec  [0 successful, 1 SQL warnings, 1 errors]

sql信息：

update diis_sd_deposit_normal_data_tmp_C1203134000011 a,
(select rowtable.ino,
    rowtable.sClientName as ccc,
    (select count(1) from (
        select colltable.ino,colltable.sClientName
        from diis_sd_deposit_normal_data_tmp_C1203134000011 colltable
        where sbankcode = 'C1203134000011'
        and iinsuranceid = 8
        and iFlowId = 134
        and isSumRow = '0'
        and stype = 'PDPD02'
        group by colltable.iClientNo) t ) as aaa
from diis_sd_deposit_normal_data_tmp_C1203134000011 rowtable
where sbankcode = 'C1203134000011'
and iinsuranceid = 8
and iFlowId = 134
and isSumRow = '1'
and stype = 'PDPD02'
) b
set a.iUpdateCount = a.iUpdateCount + 1,a.sErrorCodes = concat(coalesce(a.sErrorCodes,''),'32401;')
where b.ino = a.ino and abs(b.aaa - b.ccc) > 0.23

解决思路：

判断是sql 的哪一段出现了String 转double 的类型错误：

abs(b.aaa - b.ccc) > 0.23

经过排查是b.ccc 是String 串，与b.aaa 运算时报错

查询b.ccc 和 b.aaa的值

select b.aaa, b.ccc, length(b.ccc) from
(select rowtable.ino,
    rowtable.sClientName as ccc,
    (select count(1) from (
        select colltable.ino,colltable.sClientName
        from diis_sd_deposit_normal_data_tmp_C1203134000011 colltable
        where sbankcode = 'C1203134000011'
        and iinsuranceid = 8
        and iFlowId = 134
        and isSumRow = '0'
        and stype = 'PDPD02'
        group by colltable.iClientNo) t ) as aaa
from diis_sd_deposit_normal_data_tmp_C1203134000011 rowtable
where sbankcode = 'C1203134000011'
and iinsuranceid = 8
and iFlowId = 134
and isSumRow = '1'
and stype = 'PDPD02'
) b

结果显示：

aaa  ccc  length(b.ccc)
1    1    1
1    1    1
1    1    1
1    1    1
1    1    1
1    1    1
1    1    1
1    1    1
1    1    1
1    1    1

没有发现问题，把这个sql 拿到mysql 的命令行进行操作时发现执行成功，并且有一些警告，警告信息与Java 客户端执行的报错信息是一致的。

因为mysql 客户端连接时会无视这些警告，但是如果jdbc 连接时开启了  jdbcCompliantTruncation（默认是true）,会将警告当做异常抛出。

jdbcCompliantTruncation：连接到支持告警的服务器时（MySQL 4.1.0和更高版本），当按照JDBC的要求截短数据时，驱动程序是否应抛出java.sql.DataTruncation异常？ 默认是true 抛出