MySQL batch insertion/update data

In our daily development, batch insert/update statements are often used to implement related business functions. If the data volume is relatively large, it will cause SQL statement update failure and exceptions to be thrown.

At this time, we can execute SQL statements in batches and batches.

For example, there is a method that requires batch modification of products

We can transform it like this:

public void batchUpdateById(List<Product> productList) {
    if ((productList)) {
        return;
    }

    if (() > CommonUtils.BATCH_NUMBER) {
        int sizeNum = ();
        int startNum = 0;
        int endNum = CommonUtils.BATCH_NUMBER - 1;
        while (startNum < endNum) {
            ((startNum, endNum));
            startNum += CommonUtils.BATCH_NUMBER - 1;
            endNum += CommonUtils.BATCH_NUMBER - 1;
            if (endNum > sizeNum - 1) {
                endNum = sizeNum;
            }
        }
    } else {
        (productList);
    }
}

The above value of BATCH_NUMBER is 50, which means that when the number of modified products is greater than 50, 50 data will be used as batches and batches; and if the number of modified products is not greater than 50, it is enough to execute it directly at one time.

The above is a batch code that we handwritten by ourselves, and if each method is written like this, it would be too cumbersome.

At this time, we can use the partition grouping method about collections in the guava library to simplify:

@Override
public void batchUpdateById(List<GoodsSkuBO> list) {
    if ((list)) {
        return;
    }

    List<MerchantGoodsSkuDO> merchantGoodsSkuDOS = .goodsSkuBO2MerchantDOList(list);
    List<List<MerchantGoodsSkuDO>> groupMerchantGoodsSkuDOS = (merchantGoodsSkuDOS, CommonUtils.BATCH_NUMBER);
    (goodsSkuMasterMapper::batchUpdateById);
}

As can be seen above, the code has simplified a lot

(The above goodsSkuBO2MerchantDOList method just converts BO to DO, and has nothing to do with grouping logic).

For batch query methods, we can also use the partition method for group query to prevent the occurrence of SQL errors caused by too much data splicing in the in condition:

@Override
public List&lt;GoodsSkuBO&gt; listBySpuIdsSimple(List&lt;Long&gt; spuIds) {
    if ((spuIds)) {
        return ();
    }

    //Do not    spuIds = ().distinct().collect(());
    List&lt;List&lt;Long&gt;&gt; groupSpuIds = (spuIds, CommonUtils.BATCH_NUMBER);
    List&lt;MerchantGoodsSkuDO&gt; spuIdList = ().map(goodsSkuMasterMapper::listBySpuIds).flatMap(Collection::stream)
            .collect(());
    return .merchantGoodsSkuDO2GoodsSkuBOList(spuIdList);
}

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.