mysql 批量更新

最新推荐文章于 2025-06-16 14:24:55 发布

qdqht2009

最新推荐文章于 2025-06-16 14:24:55 发布

阅读量498

点赞数

CC 4.0 BY-SA版权

分类专栏： j2ee

本文链接：https://blog.youkuaiyun.com/qdqht2009/article/details/53895260

j2ee 专栏收录该内容

36 篇文章

订阅专栏

mysql 批量更新如果一条条去更新效率是相当的慢, 循环一条一条的更新记录,一条记录update一次，这样性能很差，也很容易造成阻塞。

mysql 批量更新有以下四种办法

1、.replace into 批量更新

replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');

2、insert into ...on duplicate key update批量更新

insert into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values(dr);

3.创建临时表，先更新临时表，然后从临时表中update

create temporary table tmp(id int(4) primary key,dr varchar(50));
insert into tmp values (0,'gone'), (1,'xx'),...(m,'yy');
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;

注意：这种方法需要用户有temporary 表的create 权限。

4、使用mysql 自带的语句构建批量更新

mysql 实现批量可以用点小技巧来实现:

UPDATE yoiurtable
SET dingdan = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END
WHERE id IN (1,2,3)

这句sql 的意思是，更新dingdan 字段，如果id=1 则dingdan 的值为3，如果id=2 则dingdan 的值为4……
where部分不影响代码的执行，但是会提高sql执行的效率。确保sql语句仅执行需要修改的行数，这里只有3条数据进行更新，而where子句确保只有3行数据执行。

如果更新多个值的话，只需要稍加修改：

UPDATE categories
SET dingdan = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END,
title = CASE id
WHEN 1 THEN 'New Title 1'
WHEN 2 THEN 'New Title 2'
WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3)

到这里，已经完成一条mysql语句更新多条记录了。

mybatis配置如下

 <update id="batchUpdate" parameterType="java.util.List">
        UPDATE camera
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="org_id =case id" suffix="end,">
                <foreach collection="list" item="record" index="index">
                    <if test="record.orgId != null">
                        when  #{record.id,jdbcType=CHAR} then #{record.orgId,jdbcType=CHAR}
                    </if>
                </foreach>
            </trim>
            <trim prefix="name =case id" suffix="end,">
                <foreach collection="list" item="record" index="index">
                    <if test="record.name != null">
                        when #{record.id,jdbcType=CHAR} then  #{record.name,jdbcType=VARCHAR}
                    </if>
                </foreach>
            </trim>
            <trim prefix="pin_yin =case id" suffix="end," >
                <foreach collection="list" item="record" index="index">
                    <if test="record.pinYin!=null">
                        when #{record.id,jdbcType=CHAR} then #{record.pinYin,jdbcType=VARCHAR}
                    </if>
                </foreach>
            </trim>
            <trim prefix="pin_yin_ad =case id" suffix="end," >
                <foreach collection="list" item="record" index="index">
                    <if test="record.pinYinAd!=null">
                        when #{record.id,jdbcType=CHAR} then #{record.pinYinAd,jdbcType=VARCHAR}
                    </if>
                </foreach>
            </trim>
            <trim prefix="kb_num =case id" suffix="end,">
                <foreach collection="list" item="record" index="index">
                    <if test="record.kbNum != null">
                        when #{record.id,jdbcType=CHAR} then  #{record.kbNum,jdbcType=VARCHAR}
                    </if>
                </foreach>
            </trim>

            <trim prefix="ip =case id" suffix="end," >
                <foreach collection="list" item="record" index="index">
                    <if test="record.ip!=null">
                        when #{record.id,jdbcType=CHAR} then #{record.ip,jdbcType=VARCHAR}
                    </if>
                </foreach>
            </trim>

            <trim prefix="node_id =case id" suffix="end," >
                <foreach collection="list" item="record" index="index">
                    <if test="record.nodeId != null">
                        when #{record.id,jdbcType=CHAR} then #{record.nodeId,jdbcType=CHAR}
                    </if>
                </foreach>
            </trim>
            <trim prefix="ext =case id" suffix="end," >
                <foreach collection="list" item="record" index="index">
                    <if test="record.ext != null">
                        when #{record.id,jdbcType=CHAR} then #{record.ext,jdbcType=VARCHAR}
                    </if>
                </foreach>
            </trim>
            <trim prefix="modify_time =case id" suffix="end," >
                <foreach collection="list" item="record" index="index">
                    <if test="record.modifyTime != null">
                        when #{record.id,jdbcType=CHAR} then #{record.modifyTime,jdbcType=TIMESTAMP}
                    </if>
                </foreach>
            </trim>
        </trim>
        where id in
        <foreach collection="list" index="index" item="record" separator="," open="(" close=")">
            #{record.id,jdbcType=CHAR}
        </foreach>
    </update>

经过测试发现方法2性能最好，

但是方法2 有限制，官网描述如下：

If column b is also unique, the INSERT is equivalent to this UPDATE statement instead:

   UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
   If a=1 OR b=2 matches several rows, only one row is updated. In general,

you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.

当存在多个unique列时，更新可能匹配多列，那一行被更新变成不确定，应该避免使用ON DUPLICATE KEY UPDATE

属性判断null方法org_id = IFNULL(VALUES(org_id),org_id) 注：此方法在属性为int并且要求非空时属性值会被设置成0。

mybatis配置如下

<update id="batchUpdate"  parameterType="java.util.List">
    INSERT INTO camera(id, name, pin_yin, pin_yin_ad, kb_num, org_id, node_id, ip,modify_time)
    VALUES
    <foreach collection="list" item="record" index="index" separator=",">
        ( #{record.id,jdbcType=CHAR},
        #{record.name,jdbcType=VARCHAR},
        #{record.pinYin,jdbcType=VARCHAR},
        #{record.pinYinAd,jdbcType=VARCHAR},
        #{record.kbNum,jdbcType=VARCHAR},
        #{record.orgId,jdbcType=CHAR},
        #{record.nodeId,jdbcType=CHAR},
        #{record.ip,jdbcType=VARCHAR},
        #{record.modifyTime,jdbcType=TIMESTAMP}
        )
    </foreach>
    ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    pin_yin = VALUES(pin_yin),
    pin_yin_ad = VALUES(pin_yin_ad),
    kb_num = VALUES(kb_num),
    org_id = VALUES(org_id),
    node_id = VALUES(node_id),
    ip = VALUES(ip),
    modify_time = VALUES(modify_time)
</update>