mysql业务笔记

mysql业务语句的处理

1 技术背景

springBoot3 mybatis mysql 多租户

2 业务案例

2.1 批量更新已有数据

/**
 * 批量更新原始订单详情的退货数量
 * @param salesOrderDetailList
 */
public void batchUpdateOrderDetailsRefundQuantity(@Param("salesOrderDetailList") List<SalesOrderDetail> salesOrderDetailList);

<!-- JOIN 临时表 / 派生表更新 -->
<update id="batchUpdateOrderDetailsRefundQuantity">
    UPDATE erp_sales_order_detail d
    JOIN (
    <foreach collection="salesOrderDetailList" item="detail" separator=" UNION ALL ">
        SELECT #{detail.tenantId} AS tenant_id,
            #{detail.detailId} AS detail_id,
            #{detail.refundedQuantity} AS refunded_quantity,
            #{detail.refundedAvailableQuantity} AS refunded_available_quantity
    </foreach>
    ) t
    ON d.tenant_id = t.tenant_id AND d.detail_id = t.detail_id
    SET d.refunded_quantity = t.refunded_quantity,
        d.refunded_available_quantity = t.refunded_available_quantity
</update>

2.2 批量新增或更新

/**
 * 批量新增/修改库存数量
 * @param subList
 */
public int batchUpsertQuantityByStockInDetail(@Param("subList") List<ProductInventoryAvg> subList);

<!-- INSERT … ON DUPLICATE KEY UPDATE（MySQL 特有）, 需要唯一键"tenant_id + sku_id" -->
<insert id="batchUpsertQuantityByStockInDetail">
    INSERT INTO erp_product_inventory_avg
        (sku_id, total_quantity, free_quantity, average_cost, total_cost, tenant_id)
    VALUES
        <foreach collection="subList" item="item" separator=",">
            ( #{item.skuId}, #{item.totalQuantity},#{item.totalQuantity},
            CASE
                WHEN #{item.totalQuantity} &lt;= 0 THEN 0
                ELSE #{item.totalCost} / #{item.totalQuantity}
            END
            , #{item.totalCost},  #{item.tenantId})
        </foreach>
    ON DUPLICATE KEY UPDATE
        total_cost =
            CASE
                -- 库存从负转正时，直接用新入库成本（忽略历史负库存期间的成本）
                WHEN total_quantity &lt;= 0 AND total_quantity + VALUES(total_quantity) &gt; 0 THEN
                    VALUES(total_cost)
                -- 正常正库存时，直接累加（无需判断total_quantity>0，因为外层条件已保证）
                WHEN total_quantity + VALUES(total_quantity) &gt; 0 THEN
                    total_cost + VALUES(total_cost)
                -- 库存为负时，按冻结成本计算
                ELSE
                    (total_quantity + VALUES(total_quantity)) * average_cost
            END,
        total_quantity = total_quantity + VALUES(total_quantity),
        free_quantity = free_quantity + VALUES(total_quantity)
</insert>

2.3 java分批处理

// 5 分批处理更新
int batchSize = ProductInventoryAvgConstants.BATCH_SIZE;
int total = productInventoryAvgList.size();
log.info("开始更新加权平均库存 ProductInventoryAvg，总数据量：{}", total);
for (int i = 0; i < total; i += batchSize)
{
    int endIndex = Math.min(i + batchSize, total);
    List<ProductInventoryAvg> subList = productInventoryAvgList.subList(i, endIndex);

    // 6 批量更新加权平均库存
    if (stockInByPurchase(stockIn)){
        // 新增 或 (更新库存数量、更新成本)
        int row1 = productInventoryAvgMapper.batchUpsertInventoryAvgByStockInDetail(subList);
        if (row1 < subList.size()){
            throw new ServiceException("入库单 - 批量更新加权平均库存处理异常：更新库存数量、更新成本失败");
        }
        log.info("------------ 采购入库更新库存 刷新skuList缓存 ------------");
        productSkuCacheManager.refreshCacheByTenant(SecurityUtils.getLoginUser().getTenantId());
    } else {
        // 新增 或 只更新数量
        int row2 = productInventoryAvgMapper.batchUpsertQuantityByStockInDetail(subList);
        if (row2 < subList.size()){
            throw new ServiceException("入库单 - 批量更新加权平均库存处理异常：更新库存数量失败");
        }
    }
    
    log.info("批次更新加权平均库存 ProductInventoryAvg，进度：{}/{}", endIndex, total);
}