preface

Mysql insert data that is not repeated. When a large amount of data needs to be inserted, it is necessary to determine whether the insert is repeated, and then insert again. How to improve efficiency? There are many ways to solve the problem, and different scenarios have different solutions. In the case of a small amount of data, how to do it is ok, but when the amount of data is large, this is not a simple problem.

Insert ignore into

Data already in the database is ignored, new data is inserted if there is no data in the database, and the current data is skipped if there is data. In this way, data that already exists in the database can be preserved, so that data can be inserted in the gap.

Controller method:

/** * Insert employee data */
@PostMapping("save")
@ResponseBody
public CommonResult<Employee> save(@RequestBody Employee employee){
    return employeeService.saveEmp(employee);
}
Copy the code
INSERT INTO INSERT dataCopy the code
<! 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>
Copy the code

Let’s add an employee record with primary key ID 1.

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

Add another employee record with ID=1 with ignore

INSERT IGNORE INTO
Copy the code

No error was reported, but the add was not successful, and the duplicate data was ignored.

On duplicate key Update

The UPDATE statement is executed when the primary key or unique key is duplicate.

 ON DUPLICATE KEY UPDATE id = id
Copy the code

Insert employee (ID=1) and change other fields (age=25) :

View database records:

As you can see, there is no change, only one piece of data, and a success message is returned.

This method requires that the constraint to be inserted must be either a primary key or a unique key. (In your business, to be used as a unique constraint, set that field to unique key.)

Extension: This approach also requires other business scenarios ->>> regularly update other fields.

Let’s add a time field to the employees table:

 private Date updateTime;
Copy the code

Then we insert data based on 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>
Copy the code

What if I need to update other fields (such as age) while inserting?

Third, the replace into

If there are records with the same primary or UNIQUE, delete them first. Insert a new record.

REPLACE INTO
Copy the code
<! 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>
Copy the code

Summary: In the actual development, the most used is the second way, batch add.

<! 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>
Copy the code

Controller:

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

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

conclusion

In actual work, method 2 is the most commonly used method, which can be used in different ways according to different scenarios.

Like is a kind of attitude, persistence is the embodiment of attitude.