mysql 隐式转换问题

谁借莪１个温暖的怀抱￠ 2021-11-29 20:02 343阅读 0赞

# 问题描述 #

数据库中kafka\_id字段为vachar类型，代码中设置的是Long类型。在做update 操作时报错

07-22 11:31:00 080 dapeng-container-biz-pool-29 ERROR [ac46002b62adc2ec] - Data truncation: Truncated incorrect DOUBLE value: '178021_0}'
    java.sql.BatchUpdateException: Data truncation: Truncated incorrect DOUBLE value: '178021_0}'
            at com.mysql.jdbc.PreparedStatement.executePreparedBatchAsMultiStatement(PreparedStatement.java:1436) ~[mysql-connector-java-5.1.36.jar:5.1.36]

造成此问题的原因是mysql的隐式转换。  
我们执行这条语句

select kafka_id from t_business_info_detail where kafka_id = 178021;

结果为

'178021'
    '178021_1'
    '178021_0'
    '178021_0'}

很明显，这里多操作了一些数据。

# MySQL 的隐式类型转换原则： #

*  两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
 *  两个参数都是字符串，会按照字符串来比较，不做类型转换
 *  两个参数都是整数，按照整数来比较，不做类型转换
 *  十六进制的值和非数字做比较时，会被当做二进制串，和数字做比较时会按下面的规则处理
 *  有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
 *  有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
 *  所有其他情况下，两个参数都会被转换为浮点数再进行比较

# 实例 #

SELECT 1+'1'; =>2
    SELECT 1+'a'; =>1
    SELECT CONCAT(2,' test'); =>'2 test'
    SELECT 38.8, CAST(38.8 AS CHAR); =>38.8 '38.8'
    SELECT 38.8, CONCAT(38.8); =>38.8 '38.8'
    
    select 'a' = 0; =>1 =>将'a'转为了0
    select '1a' = 1; =>1 =>将'1a'转为了1
    select '1a1b' = 1; =>1 =>将'1a1b'转为了1
    select '1a2b3' = 1; =>1 =>将'1a2b3'转为了1
    select '11b2c3' = 1; =>0 =>将'11b2c3'转为了11
    select 'a1b2c3' = 0; =>1 =>将'a1b2c3'转为了0
    
    select 'a' = 1; =>0
    select '1a' = 0; =>0
    select '1a1b' = 0; =>0
    select '1a2b3' = 0; =>0
    select 'a1b2c3' = 1; =>0

从上面的例子可以看出，当把字符串转为数字的时候，其实是从左边开始处理的。

*  如果字符串的第一个字符就是非数字的字符，那么转换为数字就是0
 *  如果字符串以数字开头
 *  如果字符串中都是数字，那么转换为数字就是整个字符串对应的数字
 *  如果字符串中存在非数字，那么转换为的数字就是开头的那些数字对应的值