PreparedStatement 批量update update批量条件更新set

批量更新的几种实现方式

前言

增删改查-数据库四大基本操作，批量更新如何实现呢？

方法一：CASE 指令

MySQL官方给出的一个控制指令可以解决这个问题：CASE

SQL原理

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

这个是基本原理，有两种实现方式。<br />对于第一种语法，case_value是一个表达式。 将该值与每个WHEN子句中的when_value表达式进行比较，直到其中一个相等。 找到相等的when_value时，将执行相应的THEN子句statement_list。 如果when_value不相等，则执行ELSE子句statement_list。<br />此语法不能用于测试是否与NULL相等，因为NULL = NULL为false。具体原因查看：  [Section 3.3.4.6, “Working with NULL Values”](https://dev.mysql.com/doc/refman/5.7/en/working-with-null.html).<br />

对于第二种语法，将评估每个WHEN子句search_condition表达式，直到一个表达式为true为止，此时将执行其对应的THEN子句statement_list。 如果没有search_condition等于，则执行ELSE子句statement_list（如果存在）。如果没有when_value或search_condition与测试的值匹配并且CASE语句不包含ELSE子句，则CASE语句返回错误。总结：看起来只是写法不同，其实是应用场景不同，第一种只适合简单的等值判断，而第二种每个都可以写不同的判断条件！

举个例子：

复制代码代码如下:

UPDATE categories 
SET display_order = CASE id 
    WHEN 1 THEN 3 
    WHEN 2 THEN 4 
    WHEN 3 THEN 5 
END
WHERE id IN (1,2,3)

这句sql的意思是，更新display_order 字段，如果id=1 则display_order 的值为3，如果id=2 则 display_order 的值为4，如果id=3 则 display_order 的值为5。即是将条件语句写在了一起。这里的where部分不影响代码的执行，但是会提高sql执行的效率。确保sql语句仅执行需要修改的行数，这里只有3条数据进行更新，而where子句确保只有3行数据执行。如果更新多个值的话，只需要稍加修改：

UPDATE categories 
SET display_order = CASE id 
    WHEN 1 THEN 3 
    WHEN 2 THEN 4 
    WHEN 3 THEN 5 
END, 
title = CASE id 
    WHEN 1 THEN 'New Title 1'
    WHEN 2 THEN 'New Title 2'
    WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3)

到这里，已经完成一条mysql语句更新多条记录了。

Mybatis实现

第一种：

UPDATE `nuc_el_device_sku`
SET
    gmt_modified = now()
    <trim prefix=", organization_id = case id" suffix="end">
        <foreach collection="items" item="item" index="index">
            <if test="item.organizationId !=null">
                when #{item.id} then #{item.organizationId}
            </if>
        </foreach>
    </trim>
WHERE
        `id` in
        <foreach collection="items" index="index" open="(" item="item" separator="," close=")">
            #{item.id}
        </foreach>

第二种：

UPDATE `nuc_el_device_sku`
SET
    gmt_modified = now()
    <trim prefix=", organization_id = case" suffix="end">
        <foreach collection="items" item="item" index="index">
            <if test="item.organizationId !=null">
                when sku_id=#{item.id} then #{item.organizationId}
            </if>
        </foreach>
    </trim>
WHERE
        `id` in
        <foreach collection="items" index="index" open="(" item="item" separator="," close=")">
            #{item.id}
        </foreach>

方法二： Join Update

这种方法的背后原理 可以参考官方介绍：MySQL UPDATE JOIN syntax 或者 中文翻译

SQl 原理

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition
# 其中innerJoin 等价于 Join

举个例子

UPDATE `test` a JOIN (
    SELECT 1 AS id, 11 AS code, 'holy' AS name
    UNION 
    SELECT 2 AS id, 22 AS code, 'shit' AS name
    ) b USING(id, code)
SET a.name=b.name;

上述SQL要表达的更新语义是：将id=1且code=11的name更新为’holy’，将id=2且code=22的name更新为’shit’。注意，条件字段必须放在USING中

Mybatis实现

<update id="batchUpdate" parameterType="java.util.List">
    UPDATE cam_bank_statement_journal a
    JOIN (
    <foreach collection="list" item="item" index="index" open="" close="" separator="UNION">
        SELECT #{item.journalId} AS journal_id,
        #{item.tradeCategory} AS trade_category,
        #{item.availableBalance} AS available_balance,
        #{item.frozenAmount} AS claimed_amount
    </foreach>
    )b using(journal_id)
    SET a.tradeCategory = b.tradeCategory, a.availableBalance = b.availableBalance
</update>

方法三：曲线救国-- 使用批量插入

批量插入大家都会写，写的都很留。我们可以考虑将更新转化为批量插入。一般情况下 插入时如果主键重复mysql会报错，但是mysql 提供了两个指令，在唯一主键冲突时将插入自动转为更新的指令: replace into 和 insert into ...on duplicate key update ，这两个指令的区别见：MySQL语句的区别批量插入比较简单我就不实现了。

方法四：Foreach - 俗称循环调用

就是借助mybatis的动态语法规则，循环插入。

mybatis实现：

<update id="batchUpdate" parameterType="java.util.List" >
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update cam_bank_statement_journal
        <set>
            省略
        </set>
        where 
            journal_id = #{journalId}
    </foreach>
</update>

附录

• 方法二 join 在笔公司的分库分表的数据库中不支持（跨分片join — 而拆分后，数据库可能是分布式在不同实例和不同的主机上，join将变得非常麻烦。而且基于架构规范，性能，安全性等方面考虑，一般是禁止跨库join的。），所以还是采用了 方法一去实现。关于他们的性能对比下节会介绍
• inner join 与join 在mysql中是等价的，除了inner join 还有 left join 、outer join等 关于它们的区别见下图：