這才是批量Update的正確姿勢(shì)！
前言
最近我有位小伙伴問(wèn)我，在實(shí)際工作中，批量更新的代碼要怎么寫(xiě)。
這個(gè)問(wèn)題挺有代表性的，今天拿出來(lái)給大家一起分享一下，希望對(duì)你會(huì)有所幫助。
1.案發(fā)現(xiàn)場(chǎng)
有一天上午，在我的知識(shí)星球群里，有位小伙伴問(wèn)了我一個(gè)問(wèn)題：批量更新你們一般是使用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>有小伙伴說(shuō)，第一次見(jiàn)到這種寫(xiě)法，漲知識(shí)了。
還有小伙伴問(wèn)，上面這種寫(xiě)法，跟直接for循環(huán)中update有什么區(qū)別？
for(UserEntity userEntity: list) {
userMapper.update(userEntity);
}直接for循環(huán)需要多次請(qǐng)求數(shù)據(jù)庫(kù)，網(wǎng)絡(luò)有一定的開(kāi)銷，很顯然沒(méi)有批量一次請(qǐng)求數(shù)據(jù)庫(kù)的好。
2.其他的批量更新寫(xiě)法
有小伙說(shuō)，他之前一直都是用的case when的寫(xiě)法。
類似下面這樣的：
<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>但這種寫(xiě)法顯然需要拼接很多條件，有點(diǎn)復(fù)雜，而且性能也不太好。
還有些文章中介紹，可以使用在insert的時(shí)候，可以在語(yǔ)句最后加上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ù)庫(kù)會(huì)先判斷數(shù)據(jù)是否存在，如果不存在，則執(zhí)行插入操作。如果存在，則執(zhí)行更新操作。
這種方式我之前也用過(guò)，一般需要?jiǎng)?chuàng)建唯一索引。
因?yàn)楹芏鄷r(shí)候主鍵id，是自動(dòng)增長(zhǎng)的或者根據(jù)雪花算法生成的，每次都不一樣，沒(méi)法區(qū)分多次相同業(yè)務(wù)參數(shù)請(qǐng)求的唯一性。
因此，建議創(chuàng)建一個(gè)唯一索引，來(lái)保證業(yè)務(wù)數(shù)據(jù)的唯一性。
比如：給username創(chuàng)建唯一索引，在insert的時(shí)候，發(fā)現(xiàn)username已存在，則執(zhí)行update操作，更新password。
這種方式批量更新數(shù)據(jù)，性能比較好，但一般的大公司很少會(huì)用，因?yàn)榉浅Ｈ菀壮霈F(xiàn)死鎖的問(wèn)題。
因此，目前批量更新數(shù)據(jù)最好的選擇，還是我在文章開(kāi)頭介紹的第一種方法。
3.發(fā)現(xiàn)了一個(gè)問(wèn)題
群里另外一位小伙伴，按照我的建議，在自己的項(xiàng)目中嘗試了一下foreach的這種批量更新操作，但代碼報(bào)了一個(gè)異常：
sql injection violation, multi-statement not allow這個(gè)異常是阿里巴巴druid包的WallFilter中報(bào)出來(lái)了。
它里面有個(gè)checkInternal方法，會(huì)對(duì)sql語(yǔ)句做一些校驗(yàn)，如果不滿足條件，就會(huì)拋異常：
而druid默認(rèn)不支持一條sql語(yǔ)句中包含多個(gè)statement語(yǔ)句，例如：我們的批量update數(shù)據(jù)的場(chǎng)景。
此外，MySQL默認(rèn)也是關(guān)閉批量更新數(shù)據(jù)的，不過(guò)我們可以在jdbc的url要上，添加字符串參數(shù)：&allowMultiQueries=true，開(kāi)啟批量更新操作。
比如：
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èn)題如何解決呢？
于是，我上網(wǎng)查了一下，可以通過(guò)參數(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通過(guò)設(shè)置filter中的multi-statement-allow和none-base-statement-allow為true，這樣就能開(kāi)啟批量更新的功能。
4.一直不生效
普通使用druid的datasource配置，通過(guò)上面這樣調(diào)整是OK的。
但有些小伙伴發(fā)現(xiàn)，咱們的商城項(xiàng)目中，通過(guò)上面的兩個(gè)地方的修改，還是一直報(bào)下面的異常：
sql injection violation, multi-statement not allow這是怎么回事呢？
答：咱們商城項(xiàng)目中的訂單表，使用shardingsphere做了分庫(kù)分表，并且使用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)的庫(kù),默認(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ù)庫(kù)的配置類，我們可以看到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.最后
本文由一位球友的問(wèn)題開(kāi)始，討論了批量更新的四種常見(jiàn)方式：
- for循環(huán)中一條條更新。
- foreach拼接update語(yǔ)句后批量更新。
- 使用case when的方式做判斷。
- 使用insert into on duplicate key update語(yǔ)法，批量插入或者批量更新。
雖說(shuō)有很多種方式，但我個(gè)人認(rèn)為批量update的最佳方式是第2種方式。
但需要需要的地方是，使用foreach做批量更新的時(shí)候，一次性更新的數(shù)據(jù)不宜太多，盡量控制在1000以內(nèi)，這樣更新的性能還是不錯(cuò)的。
如果需要更新的數(shù)據(jù)超過(guò)了1000，則需要分成多批更新。
此外，如果大家遇到執(zhí)行批量update操作，不支持批量更新問(wèn)題時(shí)：
sql injection violation, multi-statement not allow首先要在數(shù)據(jù)庫(kù)連接的url后面增加&allowMultiQueries=true參數(shù)，開(kāi)啟數(shù)據(jù)的批量更新操作。
如果使用了druid數(shù)據(jù)庫(kù)驅(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ù)庫(kù)中間件，比如我使用了baomidou實(shí)現(xiàn)多個(gè)數(shù)據(jù)源動(dòng)態(tài)切換的功能。
這時(shí)候，需要查看它的源碼，確認(rèn)它multi-statement-allow的配置參數(shù)是怎么配置的，有可能跟druid不一樣。
