MySQL 批量更新 mysql 批量更新 null

SQL

批量更新的写法一般有三种.
在更新数量较少的情况下，前两种性能不相上下。但是在更新字段增加，更新条数较多(500以上)，建议使用第三种写法

• 常规写法，拼接多个单条更新语句。

#分批逐条更新
UPDATE t_back_up set name='holy' WHERE id =1;
UPDATE t_back_up set name='shit' WHERE id =2;

• CASE…WHEN… 写法

#CASE WHEN 更新
update t_back_up
SET name =(
CASE 
	WHEN id=1 THEN
		'1111'
	WHEN id=2 THEN
		'22222'
END 
) where id IN(1,2);

注意：CASE WHEN一定要和WHERE语句一起使用，否则UPDATE会遍历和更新数据库中所有的行。会把未出现在WHEN中的数据都更新成null，如果code列设置为NOT NULL则会报错，否则会置为NULL或者默认值。

• JOIN 写法

#使用join更新
UPDATE t_back_up a JOIN 
(
SELECT 1 AS id, 'holy' AS name
UNION 
SELECT 2 AS id,  'shit' AS name
) b USING(id)
SET a.name=b.name;

上述SQL要表达的更新语义是：将id=1的name更新为’holy’，将id=2的name更新为’shit’。
注意，条件字段必须放在USING中

性能对比

RC隔离级别

更新条数小(一般小于500条)，CASE WHEN和JOIN优于UDPATE。
更新条数较大(千级别)，CASE WHEN效率迅速下降，UPDATE居中，推荐使用JOIN写法

RR隔离级别

JOIN性能优于CASE WHEN，CASE WHEN优于UPDATE。

Mybatis数据库操作

数据库配置：
jdbc.url=jdbc:mysql://localhost:3306/db_name？useUnicode=true&characterEncoding=utf8&allowMultiQueries=true

批量插入

#int batchInsert(List<Student> list);
#insert into t_student (id, name, age)values (?,?,?),(?,?,?)
<insert id="batchInsert" parameterType="java.util.List">
        insert into t_student
        (
        <include refid="Base_Column_List"/>
        )
        VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.id},
            #{item.name},
            #{item.age}
            )
        </foreach>
    </insert>

批量删除

#int batchDelete(String[] array);
<delete id="batchDelete" parameterType="java.lang.String">
        delete from t_student
        where id in
        <foreach collection="array" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

批量更新

#int batchUpdate(List<Student> list);
<update id="batchUpdate" parameterType="java.util.List">
    update t_student
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="name =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.name!=null and item.name != ''">
                    when id=#{item.id} then #{item.name}
                </if>
            </foreach>
        </trim>
        <trim prefix=" age =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.age != null and item.age > 0">
                    when id=#{item.id} then #{item.age}
                </if>
            </foreach>
        </trim>
    </trim>
    where id in 
    <foreach collection="list" separator="," item="item" index="index"  open="(" close=")">
        #{item.id}
    </foreach>
</update>

#多条update连接，需要在连接mysql的url上加 &allowMultiQueries=true 这个才可以执行
<update id="updateBatch"  parameterType="java.util.List">  
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update tableName
        <set>
            name=${item.name},
            name2=${item.name2}
        </set>
        where id = ${item.id}
    </foreach>      
</update>