mysql批量增加/批量修改sql写法

爱做梦的小咸鱼

已于 2023-05-12 11:18:50 修改

阅读量4.1k

点赞数 1

分类专栏： mysql 文章标签： mysql sql 数据库

于 2022-11-16 14:24:14 首次发布

本文链接：https://blog.csdn.net/zjb1697922408/article/details/127884511

版权

mysql 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

1.批量增加

Dao层

void batchAddTimePower(@Param("list") List<FusionDataInfo> fusionDataInfos);

xml

<insert id="batchAddTimePower">
        INSERT into fusionDataInfo (fusionDataInfo.fusionDataInfoId,fusionDataInfo.powerPurchasePlanId,fusionDataInfo.enterpriseId,fusionDataInfo.cycle,fusionDataInfo.totalPurchasedElectricity,fusionDataInfo.totalSoldElectricity,fusionDataInfo.createTime,fusionDataInfo.parentPlanId)
        values
        <foreach collection="list" item="f" separator=",">
            (#{f.fusionDataInfoId},#{f.powerPurchasePlanId},#{f.enterpriseId},#{f.cycle},#{f.totalPurchasedElectricity},#{f.totalSoldElectricity},#{f.createTime},#{f.parentPlanId})
        </foreach>
    </insert>

2.批量修改

Dao层

void updatePowerPurchasPrices(@Param("list") List<PowerPurchasVO> list);

xml

<update id="updatePowerPurchasPrices">
        <foreach collection="list" item="p" index="index" close=";" open="" separator=";">
            update powerPosition
            <trim prefix="set" suffixOverrides=",">
                <if test="p.contractElectricityFees != null and p.contractElectricityFees != ''">
                    powerPosition.contractElectricityFees = #{p.contractElectricityFees},
                </if>
                <if test="p.averageElectricityPrice != null and p.averageElectricityPrice != ''">
                    powerPosition.averageElectricityPrice = #{p.averageElectricityPrice},
                </if>
            </trim>
            <where>
                powerPosition.powerPositionId = #{p.powerPositionId}
            </where>
        </foreach>
    </update>

上述为本人根据需求编写,各位可根据需求不同自行更改!