首页 星云 工具 资源 星选 资讯 热门工具
:

PDF转图片 完全免费 小红书视频下载 无水印 抖音视频下载 无水印 数字星空

这才是批量update的正确姿势!

编程知识
2024年09月25日 10:24

前言

最近我有位小伙伴问我,在实际工作中,批量更新的代码要怎么写。

这个问题挺有代表性的,今天拿出来给大家一起分享一下,希望对你会有所帮助。

1 案发现场

有一天上午,在我的知识星球群里,有位小伙伴问了我一个问题:批量更新你们一般是使用when case吗?还是有其他的批量更新方法?

我的回答是:咱们星球的商城项目中,有批量更新的代码可以参考一下,这个项目中很多代码,大家平时可以多看看。

然后我将关键代码发到群里了,这是批量重置用户密码的业务场景:

<update 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>

有小伙伴说,第一次见到这种写法,涨知识了。

如果对知识星球感兴趣,可以点击这个:星球

还有小伙伴问,上面这种写法,跟直接for循环中update有什么区别?

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

直接for循环需要多次请求数据库,网络有一定的开销,很显然没有批量一次请求数据库的好。

2 其他的批量更新写法

有小伙说,他之前一直都是用的case when的写法。

类似下面这样的:

<update 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>  

但这种写法显然需要拼接很多条件,有点复杂,而且性能也不太好。

还有些文章中介绍,可以使用在insert的时候,可以在语句最后加上ON DUPLICATE KEY UPDATE关键字。

 <update 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>

在插入数据时,数据库会先判断数据是否存在,如果不存在,则执行插入操作。如果存在,则执行更新操作。

这种方式我之前也用过,一般需要创建唯一索引。

因为很多时候主键id,是自动增长的或者根据雪花算法生成的,每次都不一样,没法区分多次相同业务参数请求的唯一性。

因此,建议创建一个唯一索引,来保证业务数据的唯一性。

比如:给username创建唯一索引,在insert的时候,发现username已存在,则执行update操作,更新password。

这种方式批量更新数据,性能比较好,但一般的大公司很少会用,因为非常容易出现死锁的问题。

因此,目前批量更新数据最好的选择,还是我在文章开头介绍的第一种方法。

3 发现了一个问题

群里另外一位小伙伴,按照我的建议,在自己的项目中尝试了一下foreach的这种批量更新操作,但代码报了一个异常:

sql injection violation, multi-statement not allow

这个异常是阿里巴巴druid包的WallFilter中报出来了。

它里面有个checkInternal方法,会对sql语句做一些校验,如果不满足条件,就会抛异常:

而druid默认不支持一条sql语句中包含多个statement语句,例如:我们的批量update数据的场景。

此外,MySQL默认也是关闭批量更新数据的,不过我们可以在jdbc的url要上,添加字符串参数:&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

这个改动非常简单。

但WallFilter中的校验问题如何解决呢?

于是,我上网查了一下,可以通过参数调整druid中的filter的判断逻辑,比如:

spring:
  datasource:
    url: jdbc:xxx&serverTimezone=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

通过设置filter中的multi-statement-allow和none-base-statement-allow为true,这样就能开启批量更新的功能。

4 一直不生效

普通使用druid的datasource配置,通过上面这样调整是OK的。

但有些小伙伴发现,咱们的商城项目中,通过上面的两个地方的修改,还是一直报下面的异常:

sql injection violation, multi-statement not allow

这是怎么回事呢?

答:咱们商城项目中的订单表,使用shardingsphere做了分库分表,并且使用baomidou实现多个数据源动态切换的功能:

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

我们是使用了baomidou包下的数据源配置,这个配置在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";

    /**
     * 必须设置默认的库,默认master
     */
    private String primary = "master";
    /**
     * 是否启用严格模式,默认不启动. 严格模式下未匹配到数据源直接报错, 非严格模式下则使用默认数据源primary所设置的数据源
     */
    private Boolean strict = false;
    /**
     * 是否使用p6spy输出,默认不输出
     */
    private Boolean p6spy = false;
    /**
     * 是否使用seata,默认不使用
     */
    private Boolean seata = false;
    /**
     * 是否使用 spring actuator 监控检查,默认不检查
     */
    private boolean health = false;
    /**
     * 每一个数据源
     */
    private Map<String, DataSourceProperty> datasource = new LinkedHashMap<>();
    /**
     * 多数据源选择算法clazz,默认负载均衡算法
     */
    private Class<? extends DynamicDataSourceStrategy> strategy = LoadBalanceDynamicDataSourceStrategy.class;
    /**
     * aop切面顺序,默认优先级最高
     */
    private Integer order = Ordered.HIGHEST_PRECEDENCE;
    /**
     * Druid全局参数配置
     */
    @NestedConfigurationProperty
    private DruidConfig druid = new DruidConfig();
    /**
     * HikariCp全局参数配置
     */
    @NestedConfigurationProperty
    private HikariCpConfig hikari = new HikariCpConfig();

    /**
     * 全局默认publicKey
     */
    private String publicKey = CryptoUtils.DEFAULT_PUBLIC_KEY_STRING;
}

这个类是数据库的配置类,我们可以看到master和druid的配置是在同一层级的,于是,将application.yml文件中的配置改成下面这样的:

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

这样改动之后,商城项目中使用foreach这种批量更新数据的功能OK了。

5 最后

本文由一位球友的问题开始,讨论了批量更新的四种常见方式:

  1. for循环中一条条更新
  2. foreach拼接update语句后批量更新。
  3. 使用case when的方式做判断。
  4. 使用insert into on duplicate key update语法,批量插入或者批量更新。

虽说有很多种方式,但我个人认为批量update的最佳方式是第2种方式。

但需要需要的地方是,使用foreach做批量更新的时候,一次性更新的数据不宜太多,尽量控制在1000以内,这样更新的性能还是不错的。

如果需要更新的数据超过了1000,则需要分成多批更新。

此外,如果大家遇到执行批量update操作,不支持批量更新问题时:

sql injection violation, multi-statement not allow

首先要在数据库连接的url后面增加&allowMultiQueries=true参数,开启数据的批量更新操作。

如果使用了druid数据库驱动的,可以在配置文件中调整filter的参数。

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

主要是multi-statement-allow设置成true。

如果你还使用了其他第三方的数据库中间件,比如我使用了baomidou实现多个数据源动态切换的功能。

这时候,需要查看它的源码,确认它multi-statement-allow的配置参数是怎么配置的,有可能跟druid不一样。

最后说一句(求关注,别白嫖我)

如果这篇文章对您有所帮助,或者有所启发的话,帮忙扫描下发二维码关注一下,您的支持是我坚持写作最大的动力。
求一键三连:点赞、转发、在看。
关注公众号:【苏三说技术】,在公众号中回复:进大厂,可以免费获取我最近整理的10万字的面试宝典,好多小伙伴靠这个宝典拿到了多家大厂的offer。

From:https://www.cnblogs.com/12lisu/p/18430948
本文地址: http://www.shuzixingkong.net/article/2293
0评论
提交 加载更多评论
其他文章 SelMatch:最新数据集蒸馏,仅用5%训练数据也是可以的 | ICML'24
数据集蒸馏旨在从大型数据集中合成每类(IPC)少量图像,以在最小性能损失的情况下近似完整数据集训练。尽管在非常小的IPC范围内有效,但随着IPC增加,许多蒸馏方法变得不太有效甚至性能不如随机样本选择。论文对各种IPC范围下的最先进的基于轨迹匹配的蒸馏方法进行了研究,发现这些方法在增加IPC的情况下很
SelMatch:最新数据集蒸馏,仅用5%训练数据也是可以的 | ICML'24 SelMatch:最新数据集蒸馏,仅用5%训练数据也是可以的 | ICML'24 SelMatch:最新数据集蒸馏,仅用5%训练数据也是可以的 | ICML'24
keycloak~关于授权码认证中的scope的实践
前言 1. scope 参数的作用 定义权限:scope 用于声明请求访问的资源和权限。常见的值包括 openid、profile、email 等。 影响返回的数据:如果你在授权请求中指定了某些 scope,在后续的 token 请求中,Keycloak 会根据这些 scope 返回相应的信息。 o
keycloak~关于授权码认证中的scope的实践
使用.NET并行任务库(TPL)与并行Linq(PLINQ)充分利用多核性能
前言 最近比较闲,(项目要转Java被分到架构组,边缘化人员,无所事事 哈哈哈哈) 记录一下前段时间用到的.NET框架下采用并行策略充分利用多核CPU进行优化的一个方法 起因是项目中有个结算的方法,需要汇总一个月的数据在内存中进行计算,统计,分组 ,然后产生新的数据 在某个客户那部署后发现,这个方法
使用.NET并行任务库(TPL)与并行Linq(PLINQ)充分利用多核性能 使用.NET并行任务库(TPL)与并行Linq(PLINQ)充分利用多核性能 使用.NET并行任务库(TPL)与并行Linq(PLINQ)充分利用多核性能
C# 开源浏览器性能提升,体验Chrome级速度
前言 使用 C# 和 CefSharp 开发的全功能网页浏览器。 项目介绍 SharpBrowser 是目前最快的开源 C# 网页浏览器! 采用了轻量级的 CEF 渲染器,在呈现网页时甚至比 Google Chrome 更快。 我们对比了所有可用的.NET 浏览器引擎,最终选择了高性能的 CefSh
C# 开源浏览器性能提升,体验Chrome级速度 C# 开源浏览器性能提升,体验Chrome级速度 C# 开源浏览器性能提升,体验Chrome级速度
SimpleAIAgent:使用免费的glm-4-flash即可开始构建简单的AI Agent应用
SimpleAIAgent是基于C# Semantic Kernel 与 WPF构建的一款AI Agent探索应用。主要用于使用国产大语言模型或开源大语言模型构建AI Agent应用的探索学习,希望能够帮助到感兴趣的朋友。 接下来我想分享一下我的AI Agent应用实践。 翻译文本并将文本存入文件
SimpleAIAgent:使用免费的glm-4-flash即可开始构建简单的AI Agent应用 SimpleAIAgent:使用免费的glm-4-flash即可开始构建简单的AI Agent应用 SimpleAIAgent:使用免费的glm-4-flash即可开始构建简单的AI Agent应用
.net 到底行不行!2000 人在线的客服系统真实屏录演示(附技术详解) 📹
时常有朋友问我性能方面的问题,正好有一个真实客户,在线的访客数量达到了 2000 人。在争得客户同意后,我录了一个视频。升讯威在线客服系统可以在极低配置的服务器环境下,轻松应对这种情况,依然可以做到消息毫秒级送达,操作毫秒级响应。
.net 到底行不行!2000 人在线的客服系统真实屏录演示(附技术详解) 📹 .net 到底行不行!2000 人在线的客服系统真实屏录演示(附技术详解) 📹 .net 到底行不行!2000 人在线的客服系统真实屏录演示(附技术详解) 📹
Python计算傅里叶变换
本文介绍了离散傅里叶变换和快速傅里叶变换的基本原理及其对应的Python代码实现,并将计算结果与numpy所集成的fft函数进行对比。其实现在FFT计算的成熟工具已经有很多了,不论是CPU上scipy的fft模块还是GPU上的cufft动态链接库,都有非常好的性能。但还是得真正去了解计算背后的原理,
Python计算傅里叶变换 Python计算傅里叶变换
大模型训练:K8s 环境中数千节点存储最佳实践
今天这篇博客来自全栈工程师朱唯唯,她在前不久举办的 KubeCon 中国大会上进行了该主题分享。 Kubernetes 已经成为事实的应用编排标准,越来越多的应用在不断的向云原生靠拢。与此同时,人工智能技术的迅速发展,尤其是大型语言模型(LLM)的推进,导致企业需要处理的数据量急剧增加,例如,Lla
大模型训练:K8s 环境中数千节点存储最佳实践 大模型训练:K8s 环境中数千节点存储最佳实践 大模型训练:K8s 环境中数千节点存储最佳实践