MySQL-重复插入处理

实际开发中常见的重复插入场景：

  • 前端未做防抖，重复调用insert方法
  • 业务内部多次调用insert方法
  • 高并发

解决方案:

  1. Java业务代码层面 使用唯一性索引 和 try...catch...
  2. SQL脚本层面 使用唯一性索引/主键索引 和 on duplicate update key ...
  3. 幂等校验 在执行insert之前，先查询数据记录是否已存在

使用唯一性索引 和 try…catch…

选取数据表中合适的字段，创建唯一性索引，一般都是联合唯一索引。这样当出现两次插入相同的数据时就会抛出异常，然后在调用方法中使用try...catch...对异常进行捕获，并在catch中进行异常对应的处理。

异常类型：org.springframework.dao.DuplicateKeyException
示例如下：

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
@RestController
public class TestInsertController {
    @Autowired
    CPConfigEntityMapper cpConfigEntityMapper;

    @GetMapping("/insert")
    public String insert() {
        CPConfigEntity cpConfigEntity = new CPConfigEntity();
        cpConfigEntity.setActiveTime(new Date());
        cpConfigEntity.setAuditing((byte) 2);
        cpConfigEntity.setContent("测试insert");
        cpConfigEntity.setExpireTime(new Date());
        cpConfigEntity.setModule("module");
        cpConfigEntity.setStatus((byte) 1);
        cpConfigEntity.setId(66L);
        cpConfigEntity.setCustomerNo(888L);
       try {
            int i = cpConfigEntityMapper.insertSelective(cpConfigEntity);
            System.out.println("i = " + i);
        } catch (DuplicateKeyException e) {
            System.out.println("MySQL重复插入异常");
        }
        return "Success";
    }
}

使用主键索引（or 唯一索引）+ on duplicate update key

这是利用MySQL的特性进行自动收敛重复插入异常，该语句的意思是：当 主键 or 唯一性索引 出现相同的内容时，自动触发后续的更新操作以收敛异常。
位置 ：在insert语句的最后面添加下面的语句。
语法:

1
on duplicate update column1=xxx[, column2 = xxx, ...];

功能相当于将插入的重复语句变成了update语句，具体update的就是： column1=xxx[,column2=xxx,...]; 指定的字段。
示例:

1
ON DUPLICATE KEY UPDATE update_time = now();

完整SQL代码:

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
<insert id="insertSelective" parameterType="com.maple.model.UicAuthorization">
    INSERT INTO uic_authorization
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="id != null">
            id,
        </if>
        <if test="uid != null">
            uid,
        </if>
        <if test="parentAuthType != null">
            parent_auth_type,
        </if>
        <if test="parentAuthValue != null">
            parent_auth_value,
        </if>
        <if test="authType != null">
            auth_type,
        </if>
        <if test="authValue != null">
            auth_value,
        </if>
        <if test="createTime != null">
            create_time,
        </if>
        <if test="updateTime != null">
            update_time,
        </if>
    </trim>
    VALUES
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="id != null">
            #{id},
        </if>
        <if test="uid != null">
            #{uid},
        </if>
        <if test="parentAuthType != null">
            #{parentAuthType},
        </if>
        <if test="parentAuthValue != null">
            #{parentAuthValue},
        </if>
        <if test="authType != null">
            #{authType},
        </if>
        <if test="authValue != null">
            #{authValue},
        </if>
        <if test="createTime != null">
            #{createTime},
        </if>
        <if test="updateTime != null">
            #{updateTime},
        </if>
    </trim>
    ON DUPLICATE KEY UPDATE update_time = now();
</insert>