mysql业务笔记

mysql业务语句的处理

1 技术背景

springBoot3 mybatis mysql 多租户

2 业务案例

2.1 批量更新已有数据

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

<!-- JOIN 临时表 / 派生表更新 ，sql较长， 订单明细 > 500 条 推荐-->
<!-- 注意：允许处理重复数据 -->
<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>

<!-- JOIN 临时表 / 派生表更新 ，sql更短，订单明细 ≤ 500 条 推荐 -->
<!-- 注意：不允许处理重复数据，重复数据默认取最后一个WHEN -->
<update id="batchUpdateOrderDetailsPickQuantity">
    UPDATE erp_sales_order_detail
    SET
        pick_quantity = CASE detail_id
        <foreach collection="detailList" item="detail">
            WHEN #{detail.detailId} THEN #{detail.pickQuantity}
        </foreach>
        END,
        check_quantity = CASE detail_id
        <foreach collection="detailList" item="detail">
            WHEN #{detail.detailId} THEN #{detail.checkQuantity}
        </foreach>
        END
    WHERE tenant_id = #{tenantId}
      AND detail_id IN
        <foreach collection="detailList" item="detail" open="(" close=")" separator=",">
            #{detail.detailId}
        </foreach>
</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);
}

2.4 模糊查询忽略大小写

sql:
<if test="skuName != null  and skuName != ''"> 
    AND LOWER(k.sku_name) LIKE CONCAT('%', LOWER(#{skuName}), '%')
</if>

// (?i) 表示忽略大小写，Pattern.quote 防止特殊字符影响
if (productSku.getSkuName() != null && !sku.getSkuName().matches("(?i).*" + Pattern.quote(productSku.getSkuName()) + ".*")) {
    return false;
}

3 MyBatis 多参数传递的问题

<resultMap id="SalesOrderTaskVoSalesOrderTaskDetailVoResult" type="SalesOrderTaskVo" extends="SalesOrderTaskResult">
    <collection property="taskDetailList" ofType="SalesOrderTaskDetailVo" column="{orderId=order_id, tenantId=tenant_id}"  select="selectSalesOrderTaskDetailVoList" />
</resultMap>

<!-- 查询仓库任务队列详情 -->
<select id="selectSalesOrderTaskVoByTask"  resultMap="SalesOrderTaskVoSalesOrderTaskDetailVoResult">
    <include refid="selectSalesOrderTask"/>
    where task_id = #{taskId} and tenant_id = #{tenantId}
</select>

<!-- 任务子查询 -->
<select id="selectSalesOrderTaskDetailVoList" resultMap="SalesOrderTaskDetailVoResult" >
    <include refid="selectSalesOrderTaskDetailVo"/>
    where order_id = #{orderId} and tenant_id = #{tenantId}  order by location_weight desc
</select>