在项目中碰到这样一段mybatis的sql语句:
<update id="updateBatch" parameterType="list">
update road_stud
<trim prefix="set" suffixOverrides=",">
<trim prefix="city_id =case" suffix="end,">
<foreach collection="roadStudList" item="i" index="index">
<if test="i.cityId!=null">
when bike_sn=#{i.bikeSn} then #{i.cityId}
</if>
</foreach>
</trim>
<trim prefix="spike_message =case" suffix="end,">
<foreach collection="roadStudList" item="i" index="index">
<if test="i.roadStud!=null">
when bike_sn=#{i.bikeSn} then #{i.roadStud}
</if>
</foreach>
</trim>
<trim prefix="merchant_sn =case" suffix="end,">
<foreach collection="roadStudList" item="i" index="index">
<if test="i.merchantSn!=null">
when bike_sn=#{i.bikeSn} then #{i.merchantSn}
</if>
</foreach>
</trim>
update_time = now()
</trim>
where
<foreach collection="roadStudList" separator="or" item="i" index="index">
bike_sn=#{i.bikeSn}
</foreach>
</update>
在通过问导师和自己查询后,理解了意思;
<trim prefix="set" suffixOverrides=","> 就是set语句,去掉多余的“,”
<trim prefix="city_id =case" suffix="end,">
<foreach collection="roadStudList" item="i" index="index">
<if test="i.cityId!=null">
when bike_sn=#{i.bikeSn} then #{i.cityId}
</if>
</foreach>
</trim>
这个是利用mysql的case语句(相当于switch语句),
语法格式:
updata table set
a=case
when b=1 then
c else d
end,
e=case
when f=1 then
g else h
end
where i in (1,2,3,4,5)
例子:
updata users set
uname=case
when uID=1 then 101
when uID=17 then 1017
when uID=18 then 1018
end,
upassword=case
when uID=1 then 101
when uID=17 then 1017
when uID=18 then 1018
end
where uID in (1,17,18)
或者
SELECT
case username
when 'admin' then '超级管理员'
when 'admin2' then '2管理'
when 'admin3' then '3管理'
else '其他普通用户' end
FROM users;
<trim prefix="city_id =case" suffix="end,">
prefix是前置拼接的字符串,suffix是后置拼接的字符串
剩下的foreach和if标签就不用说了都知道;
项目中最后的where条件是用 or ,当然这里也可以用 in
那么 or 和 in 有什么对比呢:
如果in或or所在的列有索引或主键的话,执行时间基本是一致的
如果没有主键或索引,or的效率要远远低于in的执行效率。