Handling Duplicate Inserts in MySQL: Strategies and MyBatis Batch Example

When inserting large volumes of data—potentially from other databases or Excel files—checking for duplicates before each insert can be costly, especially with millions of rows.

Several MySQL techniques can handle duplicate inserts efficiently:

1. INSERT IGNORE

When an error such as a duplicate key occurs, MySQL returns a warning instead of an error, allowing the statement to continue.

INSERT IGNORE INTO user (name) VALUES ('telami');

Note that any error, not just duplicate‑key errors, will be ignored.

2. ON DUPLICATE KEY UPDATE

If a primary or unique key conflict occurs, MySQL executes the UPDATE clause; using a no‑op update (e.g., id=id ) mimics INSERT IGNORE but still reports errors.

INSERT INTO user (name) VALUES ('telami') ON DUPLICATE KEY UPDATE id = id;

This requires the duplicate‑checking column to be defined as a PRIMARY KEY or UNIQUE KEY.

3. INSERT … SELECT … WHERE NOT EXISTS

Insert based on a SELECT that only returns rows when the condition is not met, allowing custom existence checks beyond primary/unique keys.

INSERT INTO user (name) SELECT 'telami' FROM dual WHERE NOT EXISTS (SELECT id FROM user WHERE id = 1);

This method uses a sub‑query and may be slightly less efficient.

4. REPLACE INTO

If a row with the same PRIMARY or UNIQUE key exists, MySQL deletes it and inserts the new row.

REPLACE INTO user SELECT 1, 'telami' FROM books;

It always performs a delete‑then‑insert, regardless of whether a duplicate exists.

Practical Example

The author chose the second method (ON DUPLICATE KEY UPDATE) and implemented it with MyBatis for batch insertion, where the mobile_number column has a UNIQUE constraint.

<insert id="batchSaveUser" parameterType="list">
    INSERT INTO user (id, username, mobile_number)
    VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.id}, #{item.username}, #{item.mobileNumber})
    </foreach>
    ON DUPLICATE KEY UPDATE id = id
</insert>

This MyBatis mapper ensures that rows with duplicate mobile numbers are ignored during batch insertion.