mysql 关于批量新增/修改的sql写法

前言

对于程序员来说，在开发的过程中，数据库操作是必不可少的，尤其是在一次性新增大量数据，或者更新大量数据，好的sql语句执行起来可以节约数据库的压力。

1. 批量新增

Mapper层

   Integer insertUserList
			@Param(value = "list") List<userInfoDTO> userInfoList);

Xml层

<insert id="insertUserList">
        INSERT INTO user_info(
        user_name,address,age
        )VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.userName},#{item.adress},#{item.age})
        </foreach>
    </insert>

执行的SQL

INSERT INTO user_info ( user_name, adress, age)
VALUES
	( "张三", "浙江杭州", 18),
	( "李四", "浙江广州", 25)

2. 批量修改
   第一种写法方式：一条update
   这种写法只能针对于要修改的字段都是相同的值。

Mapper层

   Integer updateUserList
			@Param(value = "list") List<Integer> userIdList);

Xml层

<update id="updateUserList">
        UPDATE user_info SET age = 18,address = '浙江省杭州市'
        WHERE user_id IN (
        <foreach collection="list" item="item" index="index" open="" close="" separator=",">
            #{item.userId}
        </foreach>
        )
</update >

执行的SQL

UPDATE user_info 
SET age = 18,
address = '浙江省杭州市' 
WHERE
	user_id IN ( 30069, 30068 )

第二种方式：多条update
这种写法是可以达到修改每条记录里不同字段的值，但是比较耗费性能，增加了数据库的压力，本质上来说，就是把在业务层的for循环放到了sql里去执行，实际上还是多条sql语句。
数据库链接还需要加上：&allowMultiQueries=true

jdbc:mysql://127.0.0.1:3306/user?characterEncoding=utf-8&allowMultiQueries=true
Mapper层

   Integer updateUserList
			@Param(value = "list") List<userInfoDTO> userInfoList);

Xml层

<update id="updateUserList">
        UPDATE user_info
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="age=case" suffix="end,">
                <foreach collection="list" item="cus">
                    WHEN user_id =#{cus.userId}   THEN #{cus.age}
                </foreach>
            </trim>
            <trim prefix="address=case" suffix="end,">
                <foreach collection="list" item="cus">
                    WHEN user_id =#{cus.userId}   THEN #{cus.address}
                </foreach>
            </trim>
        </trim>
        <where>
            <foreach collection="list" separator="or" item="cus">
                user_id =#{cus.userId}
            </foreach>
        </where>
    </update>

执行的SQL

UPDATE user_info SET age = 18, address = '浙江省杭州市' 
WHERE user_id = 30069;
UPDATE user_info SET age = 25, address = '浙江省云南市' 
WHERE user_id = 30068;

第三种方式：一条update
可以满足多条记录同时修改，且每条记录字段不同的值

Mapper层

   Integer updateUserList
			@Param(value = "list") List<Integer> userIdList);

Xml层

<update id="updateUserList">
        UPDATE user_info
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="age=case" suffix="end,">
                <foreach collection="list" item="cus">
                    WHEN user_id =#{cus.userId}   THEN #{cus.age}
                </foreach>
            </trim>
            <trim prefix="address=case" suffix="end,">
                <foreach collection="list" item="cus">
                    WHEN user_id =#{cus.userId}   THEN #{cus.address}
                </foreach>
            </trim>
        </trim>
        <where>
            <foreach collection="list" separator="or" item="cus">
                user_id =#{cus.userId}
            </foreach>
        </where>
    </update>

执行的SQL

UPDATE user_info 
SET age =
CASE
	WHEN user_id = 30069 THEN 18 
	WHEN user_id = 30068 THEN 20 
END,
	address =
CASE
	WHEN user_id = 30069 THEN '浙江杭州' 
	WHEN user_id = 30068 THEN '四川成都' 
END
	WHERE user_id = 30069 OR user_id = 30068

总结一下，如果每条记录修改的字段值不同，最好用第三种方式去写sql语句，简单的话用第一种方式，不推荐用第二种方式去循环写多条sql语句去执行。