How to avoid duplication of data when inserting in MySql batch

Preface

Mysql inserts non-duplicate data. When a large amount of data needs to be inserted, it is necessary to determine whether the insertion is repeated and then insert it. So how to improve efficiency? There are many ways to solve the problem, and the solutions are different for different scenarios. When the amount of data is small, any solution is fine, but when the amount of data is large, it is not a simple problem.

1. insert ignore into

The data that already exists in the database will be ignored. If there is no data in the database, new data will be inserted. If there is data, the currently inserted data will be skipped. In this way, the existing data in the database can be retained, achieving the purpose of inserting data in the gap.

Controller method:

/**
 *Insert employee data*/
@PostMapping("save")
@ResponseBody
public CommonResult<Employee> save(@RequestBody Employee employee) {
    return employeeService.saveEmp(employee);
}

INSERT INTO

<!--Insert employee data-->
<insert id="saveEmp" parameterType="com.dt.springbootdemo.entity.Employee">
    INSERT INTO t_employee(id, name, age, salary, department_id)
    VALUES (#{id},#{name},#{age},#{salary},#{departmentId})
</insert> 

We add a new employee record with primary key ID 1.

When I send the request again, an SQL statement execution error will be reported because the primary key is unique and the record with ID=1 already exists.

Add ignore and add another employee record with ID=1

INSERT IGNORE INTO 

There was no error, but the addition was not successful, and the addition of duplicate data was ignored.

2. on duplicate key update

When the primary key or unique key is repeated, an update statement is executed.

 ON DUPLICATE KEY UPDATE id = id

We still insert the employee record with ID=1 and modify other fields (age=25):

View database records:

You can see that there is no change, there is only one piece of data, and a successful prompt is returned.

This method has a prerequisite, that is, the constraint to be inserted must be a primary key or a unique constraint (in your business, the field that needs to be used as the only judgment should be set as a unique constraint, that is, a unique key).

Extension: This method also has the needs of other business scenarios ->>>Update other fields regularly.

We add a time field to the employee table:

private Date updateTime; 

Then we insert data according to the updateTime field:

<insert id="saveEmp" parameterType="com.dt.springbootdemo.entity.Employee">
    INSERT INTO t_employee(id, name, age, salary, department_id,update_time)
    VALUES (#{id},#{name},#{age},#{salary},#{departmentId},now())
    ON DUPLICATE KEY UPDATE update_time = now()
</insert>

What should I do if I need to update other fields (such as age) when inserting?

3. replace into

If there are records with the same primary or unique, delete them first. Insert new records again.

REPLACE INTO

 <!--Insert employee data-->
<insert id="saveEmp" parameterType="com.dt.springbootdemo.entity.Employee">
    REPLACE INTO t_employee(id, name, age, salary, department_id,update_time)
    VALUES (#{id},#{name},#{age},#{salary},#{departmentId},now())
</insert>

Summary: In actual development, the second method is most commonly used, which is batch addition.

<!--Insert employee data-->
<insert id="saveEmp" parameterType="java.util.List">
    INSERT INTO t_employee(id, name, age, salary, department_id,update_time)
    VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.id},#{item.name},#{item.age},#{item.salary},#{item.departmentId},now())
    </foreach>
    ON DUPLICATE KEY UPDATE id = id
</insert>

Controller:

@PostMapping("save")
@ResponseBody
public CommonResult<Employee> save(@RequestBody List<Employee> employeeList) {
    return employeeService.saveEmp(employeeList);
}

If the same ID exists, it will not be added repeatedly.

Summarize

In actual work, method 2 is the most commonly used method, and different methods are used according to different scenarios.

This is the end of this article about how to insert data without duplication during MySql batch insertion. For more relevant MySql non-duplicate insertion content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!