Mybatis - 常用 SQL 语句设计思路及具体实现 - 数据存在则更新，不存在则插入、批量更新、批量插入、连表查询 + - 字段加减法

序言

1. 使用 Mybatis，那么在 xml 文件内，最好不要使用任何的注释符号，否则会报错

Could not set parameters for mapping解决方法 xml文件内有注释符号导致的

2. 补充提醒：

因为批量操作会拼接成很长很长的mysql语句，所以mysql server在接收数据包的时候，对这个数据包的大小是有设置项限制的。

如果超过设置的值，就会报错：

Caused by: com.mysql.jdbc.PacketTooBigException: Packet for query is too large
1

那么就需要修改这个设置项，所以推荐提前先把对应的设置值稍微弄大一点

Linux：/etc/my.cnf 配置文件的数据包接送大小配置

3. 关于 Insert （插入）和 Update （更新）操作，其 XXXMapper.java 方法的返回值，如果成功了，则返回影响的行数，一般是 > 0 （大于 0），否则是插入失败或更新失败，即 ==0 （等于 0），当然返回值为 0 也不是那么严重，如果是 ON DUPLICATE KEY UPDATE，则是有重复项了，就不会进行重复项的插入更新操作，所以有可能影响行数为 0，最后还有一个特殊的返回值 -1，这个自行理解，不做解释。

一、数据存在则更新，不存在则插入

网上有些方法说用 replace into，但是 Mybatis 是不支持的。所以，必须使用 ON DUPLICATE KEY UPDATE。

但笔者尝试过， ON DUPLICATE KEY UPDATE 是有部分问题的，所以只能用传统方法，先查询是否存在， if 存在则调用 update 更新方法，else 不存在时调用 insert 插入方法。

1、ON DUPLICATE KEY UPDATE 的具体 xml 用法：（虽然有点问题，但没准以后有用到的时候）

<insert id="batchSaveCommissionSummaryList" parameterType="list" >

    insert into commission_summary
      (enterprise_id,enterprise_name,in_province_amount,in_province_rate,in_province_commission,out_province_amount,out_province_rate,
      out_province_commission,total_amount,total_commission,month,create_time)
    values
    <foreach collection="list" item="item" index="index" separator=",">
      (#{item.enterpriseId},#{item.enterpriseName},#{item.inProvinceAmount},#{item.inProvinceRate},#{item.inProvinceCommission},#{item.outProvinceAmount},
      #{item.outProvinceRate},#{item.outProvinceCommission},#{item.totalAmount},#{item.totalCommission},#{item.month},#{item.createTime})
    foreach>
    ON DUPLICATE KEY UPDATE
        enterprise_id = values(enterprise_id),
        enterprise_name = values(enterprise_name),

        in_province_amount = values(in_province_amount),

        in_province_rate = values(in_province_rate),

        in_province_commission = values(in_province_commission),

        out_province_amount = values(out_province_amount),

        out_province_rate =values(out_province_rate),

        out_province_commission = values(out_province_commission),

        total_amount = values(total_amount),

        total_commission = values(total_commission),

        month = values(month),

        create_time = values(create_time)
  insert>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

注意：values()里面要用数据库字段来实现对数据的更新，而不是传入的参数字段

on duplicate key update 用法总结：

1. mysql 的存在就更新不存在就插入可由on duplicate key update语法实现；

2. 不过只会检查添加列中有没有匹配到主键id和唯一索引的重复项；

3. 如果有重复项会在on duplicate key update后进行修改指定的字段和内容；

4. 所涉及的唯一索引也是可以修改的；

在实际开发中插入时可能存在数据重复问题，需要忽略或替换掉重复的数据（依据某个字段，比如 Primary Key（主键索引）或 Unique Key （唯一键索引）来确定是否重复）

二、批量更新

# UpdateEntity.java 实体类
import lombok.Data;

@Data
public class UpdateEntity{
	 /**
     * id 号
     */
    private Integer id;
	 /**
     * 姓名
     */
    private String name;
     /**
     * 年龄
     */
    private String age;
}

# UpdateMapper.java 方法

Integer updateBatchById(@Param("list") List<UpdateEntity> list)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

方法 一：（数据量越多，容易变成慢 SQL，不太推荐）

每一条更新语句以分号 ; 隔开

<update id="updateBatchById">
    <foreach collection="list" item="item" separator=";">
        update
            `t_student`
        set
            `name` = #{item.name},
            `age` = #{item.age}
        where
            id = #{item.id}
    foreach>
update>

1
2
3
4
5
6
7
8
9
10
11
12

方法二

<update id="updateBatchById">
update `t_student`
<trim prefix="set" suffixOverrides=",">
    <trim prefix=" `name` = case " suffix=" end, ">
        <foreach collection="list" item="item">
            <if test="item.name != null and item.name.trim() neq ''">
                when `id` = #{item.id} then #{item.name}
            if>
        foreach>
    trim>
    <trim prefix=" `age` = case " suffix=" end, ">
        <foreach collection="list" item="item">
            <if test="item.age != null and item.age.trim() neq ''">
                when `id` = #{item.id} then #{item.age}
            if>
        foreach>
    trim>
trim>
where
    `id` in
<foreach collection="list" item="item" open="(" close=")" separator=",">
    #{item.id}
foreach>

update>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

输出效果：

UPDATE `t_student` 
SET `name` =
CASE
		
		WHEN `id` = 1 THEN
		'张三' 
		WHEN `id` = 2 THEN
		'李四' 
		WHEN `id` = 3 THEN
		'王五' 
		WHEN `id` = 4 THEN
		'赵六' 
	END,
	`age` =
CASE
		
		WHEN `id` = 1 THEN
		40 
		WHEN `id` = 2 THEN
		34 
		WHEN `id` = 3 THEN
		55 
		WHEN `id` = 4 THEN
		76 
	END 
WHERE
	`id` IN ( 1, 2, 3, 4 )

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

方法三 （推荐）

<update id="updateList" parameterType="java.util.List">
    update agent_apply
    set apply_time=
    <foreach collection="list" item="item" index="index"
             separator=" " open="case" close="end">
      when id=#{item.id} then #{item.applyTime}
    foreach>
    where id in
    <foreach collection="list" index="index" item="item"
             separator="," open="(" close=")">
      #{item.id,jdbcType=INTEGER}
    foreach>
update>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

效果和方法二差不多，不过更加简化。

IN 在数据量不大于1000的数据量情况下，会走索引。
很多时候为了批量更新数据会优先考虑第二种方式，再加上线程池。跑百万级的数据目测不需要几分钟
第一种是多条sql，需要数据库需要执行多次，第二种一条sql，数据库执行一边，数据量允许的情况下第二种比第一种更加的优秀，但是数据量特别大的时候，第二种确实会给数据库带来压力，但是如果你数据量不是特别特别大，你要相信数据库没有那么脆弱

三、批量插入

Integer insertList(@Param("list") List<UpdateEntity> list)
1

  <insert id="insertList" parameterType="java.util.List">
    insert into users(
    id, name
    )
    values
    <foreach collection="list" item="item" index="index" separator=",">
      (
      #{item.id}, #{item.name}
      )
    foreach>
  insert>

1
2
3
4
5
6
7
8
9
10
11
12

四、连表查询 + - 字段加减法

先从理解连表开始：

连表需要时常用到子查询，然后使用 LEFT JOIN、RIGHT JOIN、INNER JOIN 等关键字，进行连表，ON 关键字是连表的关键条件。

基本信息 test_table表字段：id、name、sex、age、phone、address

资金流动 test_other表字段：id、utility_bills（水电费）、salary（工资）、subsidy（补贴）、test_id

test_other 表的 test_id 和 test_table 表的 id 字段是连表关键，可以将 test_id 看做是 test_table 表的外键。

SELECT * FROM (
(SELECT * FROM test_table) tb
LEFT JOIN
(SELECT * FROM test_other) A ON tb.id=A.test_id
) new_table
1
2
3
4
5

1、连表 + - 加减法

多个字段的加减法：（查询所有人的净收入（net_receipt），某个人的工资字段减去所属的水电费字段，加上所属的补贴字段）需要对数字字段进行一个格式化处理 IFNULL(某个字段名, 初始化值为 0)，这样数字类型的数据就能进行加减法。

净收入字段名：net_receipt

(new_table.salary + new_table.subsidy - new_table.utility_bills) AS net_receipt
1

具体净收入 SQL 连表查询语句实现演示：

SELECT 
(new_table.salary + new_table.subsidy - new_table.utility_bills) AS net_receipt,
id, name, sex, age, phone, address, salary, subsidy, utility_bills 
FROM (
(SELECT id,name,sex,age,phone,address FROM test_table) tb
LEFT JOIN
(SELECT IFNULL(utility_bills, 0),IFNULL(salary, 0),IFNULL(subsidy, 0),test_id 
FROM test_other) A 
ON tb.id=A.test_id
) new_table
1
2
3
4
5
6
7
8
9
10

xml 演示：

<select>
SELECT 
(new_table.salary + new_table.subsidy - new_table.utility_bills) AS net_receipt,
id, name, sex, age, phone, address, salary, subsidy, utility_bills 
FROM (
(SELECT id,name,sex,age,phone,address FROM test_table) tb
LEFT JOIN
(SELECT IFNULL(utility_bills, 0),IFNULL(salary, 0),IFNULL(subsidy, 0),test_id 
FROM test_other) A 
ON tb.id=A.test_id
) new_table
select>
1
2
3
4
5
6
7
8
9
10
11
12

同理：可以将 + - 法替换为 乘法 *

---

参考链接

Mybatis新增数据，存在就更新，不存在就添加

Mybatis 有则更新，无则插入的实现

Mybatis使用on duplicate key update操作详解

Mybatis 批量插入操作ON DUPLICATE KEY UPDATE使用

mysql中on duplicate key update用法（批量操作数据、存在更新，不存在则新增），附mybatis配置

mybatis中批量插入，若存在，则更新；不存在，则新增

Mybatis之批量更新数据（批量update）

【MyBatis】关于MyBatis批量更新的几种方式

MySQL的on duplicate key update 的使用

Mysql on duplicate key update用法及优缺点

Mybatis 中传入List实现 批量插入、批量更新、批量删除

Mybatis：不存在则插入，存在则更新或忽略

Mybatis 多参数传递、parameterType=“java.util.List“自动查找实体类参数 - 具体方法

从零搭建 Mybatis - 语法 - 循环操作/批量操作（查询、修改（更新）…）的具体实现方法&配置MyBatis批量更新返回受影响数 | mybatis 更新时为什么返回值是-1

MyBatis学习之路——获取参数值和各种查询功能（查询实体类、List集合、Map集合）

ResultMap详解

mysql怎么实现字段求和

mysql 求多个字段的和

mysql进行sum多个表多个字段的时候数据很大的问题的解决以及6表联合复杂结构查询