How to Insert Non‑Duplicate Data in MySQL Using INSERT IGNORE, ON DUPLICATE KEY UPDATE, and REPLACE INTO

When inserting a large amount of data into MySQL, it is necessary to avoid duplicate rows; this article discusses efficient ways to achieve that, especially for high‑volume scenarios.

INSERT IGNORE skips rows whose primary or unique key already exists, leaving existing rows untouched. Example controller method:

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

Corresponding MyBatis mapper snippet:

INSERT INTO t_employee(id, name, age, salary, department_id)
VALUES (#{id}, #{name}, #{age}, #{salary}, #{departmentId})

Using INSERT IGNORE INTO prevents an error when the same primary‑key value (e.g., id=1 ) is inserted a second time.

ON DUPLICATE KEY UPDATE executes an UPDATE when a primary or unique key conflict occurs. Example:

INSERT INTO t_employee(id, name, age, salary, department_id)
VALUES (#{id}, #{name}, #{age}, #{salary}, #{departmentId})
ON DUPLICATE KEY UPDATE id = id

This statement can also modify other columns, such as age or a timestamp field update_time :

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()

The method requires that the conflict be on a primary‑key or unique‑key column.

REPLACE INTO deletes the existing row that matches the primary/unique key and inserts a new one. Example mapper:

REPLACE INTO t_employee(id, name, age, salary, department_id, update_time)
VALUES (#{id}, #{name}, #{age}, #{salary}, #{departmentId}, NOW())

In practice, the most frequently used approach for batch inserts is the second method (ON DUPLICATE KEY UPDATE). A batch example using MyBatis <foreach> :

<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" separator=",">
        (#{item.id}, #{item.name}, #{item.age}, #{item.salary}, #{item.departmentId}, NOW())
    </foreach>
    ON DUPLICATE KEY UPDATE id = id
</insert>

Corresponding controller for batch insertion:

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

The article concludes that developers should choose the appropriate method based on the specific scenario, with ON DUPLICATE KEY UPDATE being the most versatile for bulk operations.