這才是批量Update的正確姿勢(shì)！

作者：蘇三 2024-09-25 08:22:06

數(shù)據(jù)庫其他數(shù)據(jù)庫

有位小伙伴問了我一個(gè)問題：批量更新你們一般是使用when case嗎？還是有其他的批量更新方法？

前言

最近我有位小伙伴問我，在實(shí)際工作中，批量更新的代碼要怎么寫。

這個(gè)問題挺有代表性的，今天拿出來給大家一起分享一下，希望對(duì)你會(huì)有所幫助。

1.案發(fā)現(xiàn)場(chǎng)

有一天上午，在我的知識(shí)星球群里，有位小伙伴問了我一個(gè)問題：批量更新你們一般是使用when case嗎？還是有其他的批量更新方法？

我的回答是：咱們星球的商城項(xiàng)目中，有批量更新的代碼可以參考一下，這個(gè)項(xiàng)目中很多代碼，大家平時(shí)可以多看看。

然后我將關(guān)鍵代碼發(fā)到群里了，這是批量重置用戶密碼的業(yè)務(wù)場(chǎng)景：

<update id="updateForBatch" parameterType="cn.net.susan.entity.sys.UserEntity">
    <foreach collection="list" item="entity" separator=";">
        UPDATE sys_user
        SET password = #{entity.password},update_user_id=#{entity.updateUserId},update_user_name=#{entity.updateUserName}
        <where>
            id = #{entity.id}
        </where>
    </foreach>
</update>

有小伙伴說，第一次見到這種寫法，漲知識(shí)了。

還有小伙伴問，上面這種寫法，跟直接for循環(huán)中update有什么區(qū)別？

for(UserEntity userEntity: list) {
   userMapper.update(userEntity);
}

直接for循環(huán)需要多次請(qǐng)求數(shù)據(jù)庫，網(wǎng)絡(luò)有一定的開銷，很顯然沒有批量一次請(qǐng)求數(shù)據(jù)庫的好。

2.其他的批量更新寫法

有小伙說，他之前一直都是用的case when的寫法。

類似下面這樣的：

<update id="updateForBatch" parameterType="cn.net.susan.entity.sys.UserEntity">
  update sys_user
  <trim prefix="set" suffixOverrides=",">
      <trim prefix="password = case id" suffix="end,">
          <foreach collection="list" item="item">
              when #{item.id} then #{item.password}
          </foreach>
      </trim>
      <trim prefix="update_user_id = case id" suffix="end,">
          <foreach collection="list" item="item">
              when #{item.id} then #{item.updateUserId}
          </foreach>
      </trim>
      <trim prefix="update_user_name = case id" suffix="end">
          <foreach collection="list" item="item">
              when #{item.id} then #{item.updateUserName}
          </foreach>
      </trim>
  </trim>
  <where>
      id in (
      <foreach collection="list" separator="," item="item">
          #{item.id}
      </foreach>
      )
  </where>
</update>

但這種寫法顯然需要拼接很多條件，有點(diǎn)復(fù)雜，而且性能也不太好。

還有些文章中介紹，可以使用在insert的時(shí)候，可以在語句最后加上ON DUPLICATE KEY UPDATE關(guān)鍵字。

<update id="updateForBatch" parameterType="cn.net.susan.entity.sys.UserEntity">
    insert into sys_user
    (id,username,password) values
    <foreach collection="list" index="index" item="item" separator=",">
        (#{item.id},
        #{item.username},
        #{item.password})
    </foreach>
    ON DUPLICATE KEY UPDATE
     password=values(password)
</update>

在插入數(shù)據(jù)時(shí)，數(shù)據(jù)庫會(huì)先判斷數(shù)據(jù)是否存在，如果不存在，則執(zhí)行插入操作。如果存在，則執(zhí)行更新操作。

這種方式我之前也用過，一般需要?jiǎng)?chuàng)建唯一索引。

因?yàn)楹芏鄷r(shí)候主鍵id，是自動(dòng)增長(zhǎng)的或者根據(jù)雪花算法生成的，每次都不一樣，沒法區(qū)分多次相同業(yè)務(wù)參數(shù)請(qǐng)求的唯一性。

因此，建議創(chuàng)建一個(gè)唯一索引，來保證業(yè)務(wù)數(shù)據(jù)的唯一性。

比如：給username創(chuàng)建唯一索引，在insert的時(shí)候，發(fā)現(xiàn)username已存在，則執(zhí)行update操作，更新password。

這種方式批量更新數(shù)據(jù)，性能比較好，但一般的大公司很少會(huì)用，因?yàn)榉浅Ｈ菀壮霈F(xiàn)死鎖的問題。

因此，目前批量更新數(shù)據(jù)最好的選擇，還是我在文章開頭介紹的第一種方法。

3.發(fā)現(xiàn)了一個(gè)問題

群里另外一位小伙伴，按照我的建議，在自己的項(xiàng)目中嘗試了一下foreach的這種批量更新操作，但代碼報(bào)了一個(gè)異常：

sql injection violation, multi-statement not allow

這個(gè)異常是阿里巴巴druid包的WallFilter中報(bào)出來了。

它里面有個(gè)checkInternal方法，會(huì)對(duì)sql語句做一些校驗(yàn)，如果不滿足條件，就會(huì)拋異常：

而druid默認(rèn)不支持一條sql語句中包含多個(gè)statement語句，例如：我們的批量update數(shù)據(jù)的場(chǎng)景。

此外，MySQL默認(rèn)也是關(guān)閉批量更新數(shù)據(jù)的，不過我們可以在jdbc的url要上，添加字符串參數(shù)：&allowMultiQueries=true，開啟批量更新操作。

比如：

datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/console?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true
      username: root
      password: root

這個(gè)改動(dòng)非常簡(jiǎn)單。

但WallFilter中的校驗(yàn)問題如何解決呢？

于是，我上網(wǎng)查了一下，可以通過參數(shù)調(diào)整druid中的filter的判斷邏輯，比如：

spring:
  datasource:
    url: jdbc:xxx&serverTimeznotallow=Asia/Shanghai&rewriteBatchedStatements=true&allowMultiQueries=true
    username: xxx
    password: xxx
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      filter:
        wall:
          config:
            multi-statement-allow: true
            none-base-statement-allow: true

通過設(shè)置filter中的multi-statement-allow和none-base-statement-allow為true，這樣就能開啟批量更新的功能。

4.一直不生效

普通使用druid的datasource配置，通過上面這樣調(diào)整是OK的。

但有些小伙伴發(fā)現(xiàn)，咱們的商城項(xiàng)目中，通過上面的兩個(gè)地方的修改，還是一直報(bào)下面的異常：

sql injection violation, multi-statement not allow

這是怎么回事呢？

答：咱們商城項(xiàng)目中的訂單表，使用shardingsphere做了分庫分表，并且使用baomidou實(shí)現(xiàn)多個(gè)數(shù)據(jù)源動(dòng)態(tài)切換的功能：

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.1.1</version>
</dependency>

我們是使用了baomidou包下的數(shù)據(jù)源配置，這個(gè)配置在DynamicDataSourceProperties類中：

/**
 * Copyright ? 2018 organization baomidou
 * <pre>
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 * <pre/>
 */
package com.baomidou.dynamic.datasource.spring.boot.autoconfigure;

import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.druid.DruidConfig;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.hikari.HikariCpConfig;
import com.baomidou.dynamic.datasource.strategy.DynamicDataSourceStrategy;
import com.baomidou.dynamic.datasource.strategy.LoadBalanceDynamicDataSourceStrategy;
import com.baomidou.dynamic.datasource.toolkit.CryptoUtils;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.context.properties.NestedConfigurationProperty;
import org.springframework.core.Ordered;

import java.util.LinkedHashMap;
import java.util.Map;

/**
 * DynamicDataSourceProperties
 *
 * @author TaoYu Kanyuxia
 * @see DataSourceProperties
 * @since 1.0.0
 */
@Slf4j
@Getter
@Setter
@ConfigurationProperties(prefix = DynamicDataSourceProperties.PREFIX)
public class DynamicDataSourceProperties {

    public static final String PREFIX = "spring.datasource.dynamic";
    public static final String HEALTH = PREFIX + ".health";

    /**
     * 必須設(shè)置默認(rèn)的庫,默認(rèn)master
     */
    private String primary = "master";
    /**
     * 是否啟用嚴(yán)格模式,默認(rèn)不啟動(dòng). 嚴(yán)格模式下未匹配到數(shù)據(jù)源直接報(bào)錯(cuò), 非嚴(yán)格模式下則使用默認(rèn)數(shù)據(jù)源primary所設(shè)置的數(shù)據(jù)源
     */
    private Boolean strict = false;
    /**
     * 是否使用p6spy輸出，默認(rèn)不輸出
     */
    private Boolean p6spy = false;
    /**
     * 是否使用seata,默認(rèn)不使用
     */
    private Boolean seata = false;
    /**
     * 是否使用 spring actuator 監(jiān)控檢查，默認(rèn)不檢查
     */
    private boolean health = false;
    /**
     * 每一個(gè)數(shù)據(jù)源
     */
    private Map<String, DataSourceProperty> datasource = new LinkedHashMap<>();
    /**
     * 多數(shù)據(jù)源選擇算法clazz，默認(rèn)負(fù)載均衡算法
     */
    private Class<? extends DynamicDataSourceStrategy> strategy = LoadBalanceDynamicDataSourceStrategy.class;
    /**
     * aop切面順序，默認(rèn)優(yōu)先級(jí)最高
     */
    private Integer order = Ordered.HIGHEST_PRECEDENCE;
    /**
     * Druid全局參數(shù)配置
     */
    @NestedConfigurationProperty
    private DruidConfig druid = new DruidConfig();
    /**
     * HikariCp全局參數(shù)配置
     */
    @NestedConfigurationProperty
    private HikariCpConfig hikari = new HikariCpConfig();

    /**
     * 全局默認(rèn)publicKey
     */
    private String publicKey = CryptoUtils.DEFAULT_PUBLIC_KEY_STRING;
}

這個(gè)類是數(shù)據(jù)庫的配置類，我們可以看到master和druid的配置是在同一層級(jí)的，于是，將application.yml文件中的配置改成下面這樣的：

spring:
  application:
    name: mall-job
  datasource:
    dynamic:
      primary: master
      datasource:
        master:
          username: root
          password: 123456
          url: jdbc:mysql://localhost:3306/susan_mall?serverTimeznotallow=Asia/Shanghai&characterEncoding=utf8&useSSL=false&zeroDateTimeBehavior=convertToNull
          driver-class-name: com.mysql.cj.jdbc.Driver
       druid:
        wall:
          multiStatementAllow: true
          noneBaseStatementAllow: true

這樣改動(dòng)之后，商城項(xiàng)目中使用foreach這種批量更新數(shù)據(jù)的功能OK了。

5.最后

本文由一位球友的問題開始，討論了批量更新的四種常見方式：

for循環(huán)中一條條更新。
foreach拼接update語句后批量更新。
使用case when的方式做判斷。
使用insert into on duplicate key update語法，批量插入或者批量更新。

雖說有很多種方式，但我個(gè)人認(rèn)為批量update的最佳方式是第2種方式。

但需要需要的地方是，使用foreach做批量更新的時(shí)候，一次性更新的數(shù)據(jù)不宜太多，盡量控制在1000以內(nèi)，這樣更新的性能還是不錯(cuò)的。

如果需要更新的數(shù)據(jù)超過了1000，則需要分成多批更新。

此外，如果大家遇到執(zhí)行批量update操作，不支持批量更新問題時(shí)：

sql injection violation, multi-statement not allow

首先要在數(shù)據(jù)庫連接的url后面增加&allowMultiQueries=true參數(shù)，開啟數(shù)據(jù)的批量更新操作。

如果使用了druid數(shù)據(jù)庫驅(qū)動(dòng)的，可以在配置文件中調(diào)整filter的參數(shù)。

spring:
  datasource:
    druid:
      filter:
        wall:
          config:
            multi-statement-allow: true
            none-base-statement-allow: true

主要是multi-statement-allow設(shè)置成true。

如果你還使用了其他第三方的數(shù)據(jù)庫中間件，比如我使用了baomidou實(shí)現(xiàn)多個(gè)數(shù)據(jù)源動(dòng)態(tài)切換的功能。

這時(shí)候，需要查看它的源碼，確認(rèn)它multi-statement-allow的配置參數(shù)是怎么配置的，有可能跟druid不一樣。

責(zé)任編輯：姜華來源：蘇三說技術(shù)