mysql trim函数 中标签属性的用法

mysql trim函数 中标签属性的用法

1.sql语句

 <update id="updateMerchantCompanyBatch" parameterType="java.util.List">
        update BAOFOO_MA.ma_merchant_company
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="id_card =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                        when member_id=#{item.member_id} then #{item.id_card}
                </foreach>
            </trim>
            <trim prefix="link_mobile =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                        when member_id=#{item.member_id} then #{item.link_mobile}
                </foreach>
            </trim>
            <trim prefix="bank_account =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                        when member_id=#{item.member_id} then #{item.bank_account}
                </foreach>
            </trim>
        </trim>
        where member_id in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item.member_id}
        </foreach>
    </update>

2.翻译成SQL语句

UPDATE BAOFOO_MA.ma_merchant_company SET id_card= CASE WHEN member_id ='100000178' THEN 110 END, link_mobile = CASE WHEN member_id = '100000178' THEN 888 END 
WHERE member_id IN (100000178)

trim标签的属性

1. prefix：前缀覆盖并增加其内容。也就是给中的sql语句加上前缀；
2. suffix：后缀覆盖并增加其内容。给包裹的sql语句加上后缀；
3. prefixOverrides：前缀判断的条件。取消指定的前缀，如where；
4. suffixOverrides：后缀判断的条件。取消指定的后缀，如and | or.，逗号等。(自动去除后面多余“，”)