今天遇到一个很奇怪的问题: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},',%')))