mysql修改错误记录Data truncation: Truncated incorrect DOUBLE

今天遇到一个很奇怪的问题：sql放到数据库中执行没一点问题，倒是写成xml在执行就出现Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: ‘58,59,62,67’ * 看总结

原sql：

 void updateNextNames(@Param("fatherAddress") Address fatherAddress, @Param("oldName") String oldName);
 
 
 UPDATE b_address
        SET chain_names = REPLACE ( chain_names, #{oldName}, #{fatherAddress.name} )
  WHERE
	        (
	    LEVEL = #{fatherAddress.level}+1 and (chain_ids=#{fatherAddress.id} or chain_ids LIKE CONCAT('%,',#{fatherAddress.id})))

	    OR (
        LEVEL > #{fatherAddress.level}+1 and (chain_ids LIKE CONCAT(#{fatherAddress.id},',%') or chain_ids LIKE CONCAT('%,',#{fatherAddress.id},',%')))

开始以为是REPLACE 函数的问题，果不其然，把这个sql拆分之后就没有出现错误
拆分为：

<select id="selectNextNames" resultType="com.yiwei.system.domain.Address">
        select  * from  b_address
        WHERE
	        (
	    LEVEL = #{fatherAddress.level}+1 and (chain_ids=#{fatherAddress.id} or chain_ids LIKE CONCAT('%,',#{fatherAddress.id})))

	    OR (
        LEVEL > #{fatherAddress.level}+1 and (chain_ids LIKE CONCAT(#{fatherAddress.id},',%') or chain_ids LIKE CONCAT('%,',#{fatherAddress.id},',%')))
    </select>

    <update id="updateNextNames">

        UPDATE b_address
        SET chain_names = REPLACE ( chain_names, #{oldName}, #{fatherAddress.name} )
        WHERE
        id in
        <foreach collection="addressList" item="item" open="(" close=")" separator=",">
            #{item.id}
        </foreach>
#这里的addressList为上面查询语句查出来的列表
    </update>

这就让我很疑问了啊，然后想了想，何不写成子查询呢

<update id="updateNextNames">

    UPDATE b_address
    SET chain_names = REPLACE (
    chain_names,
    #{oldName}, #{fatherAddress.name} )
    WHERE
        id IN (SELECT
        *
    FROM
        (
    SELECT
    id
    FROM
    b_address
    WHERE
    ( LEVEL = #{fatherAddress.level}+1 and (chain_ids=#{fatherAddress.id} or chain_ids LIKE CONCAT('%,',#{fatherAddress.id})))
    OR ( LEVEL > #{fatherAddress.level}+1 and (chain_ids LIKE CONCAT(#{fatherAddress.id},',%') or chain_ids LIKE CONCAT('%,',#{fatherAddress.id},',%'))
    )) AS c )

</update>

但是这样一运行我就蒙圈了，又不行了

此时冷静思考了下，这行错误提示，58,59,62,67肯定是不能取值为double的，这个里面哪里有double呢，只有传值进来的fatherAddress.id为long，chain_ids链为string，和long去和string验证相等，是不是这个问题，有了思考要付诸行动，奥力给（前面address类里面id定义：private Long id;）

可以定义为private String id;

void updateNextNames(@Param("fatherId")String fatherId, @Param("level")Integer level, @Param("fatherName")String fatherName,@Param("oldName") String oldName);

UPDATE b_address
    SET chain_names = REPLACE ( chain_names, #{oldName}, #{fatherName} )
    WHERE
     (
 LEVEL = #{level}+1 and (chain_ids=#{fatherId} or chain_ids LIKE CONCAT('%,',#{fatherId})))

 OR (
    LEVEL > #{level}+1 and (chain_ids LIKE CONCAT(#{fatherId},',%') or chain_ids LIKE CONCAT('%,',#{fatherId},',%')))

果然，没一点问题，总结：mysql数据库中是varchar类型，不能在xml中去匹配相等一个long。

（chain_ids=#{fatherAddress.id}） 这个问题很偏激，不是常见的，查询语句select不会出现，只有修改中的where查询会出现，删除和插入也不会出现（已测），至于为什么修改会出现感兴趣的可以研究下update的实现和别的区别，欢迎评论区留言指出！ 激动的心，颤抖的手，点波关注你再走！