Insert Conflicts

Conflict handling decides whether an INSERT encountering a primary-key or unique-key conflict should fail, be ignored, or update the existing row. Typical scenarios include replayed jobs, data synchronization, repeated imports, and insert-or-update operations. MySQL performs detection and writing in one statement; a preliminary query is unnecessary.

Strategies

Strategy	MySQL statement	Result
Into	INSERT INTO ... VALUES (...)	The database reports a conflict
Ignore	INSERT IGNORE ... VALUES (...)	The conflicting row is not inserted
Update	INSERT ... ON DUPLICATE KEY UPDATE ...	Insert when no conflict exists; otherwise update

MySQL detects conflicts from the table's primary and unique constraints. Ignore and Update do not require dbVisitor primary-key mapping to select a conflict target.

Usage

Assume UserInfo maps to this table, with id marked as the entity primary key. Alice already exists:

CREATE TABLE user_info (
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR(64),
    age INTEGER
);
INSERT INTO user_info (id, name, age) VALUES (1001, 'Alice', 18);

Submit the same ID with a new name:

UserInfo user = new UserInfo();
user.setId(1001);
user.setName("Bob");
user.setAge(20);

int rows = lambda.insert(UserInfo.class)
        .onDuplicateStrategy(DuplicateKeyStrategy.Update)
        .applyEntity(user)
        .executeSumResult();

With Update, row 1001 becomes Bob, age 20. With Ignore, Alice remains unchanged. Default Into reports the duplicate key.

Use Ignore to skip conflicting rows. Omitting the strategy or selecting Into produces a regular INSERT. To control exactly which columns are updated, write complete SQL with Mapper or JdbcTemplate. See Insert Operations for the common API.

Notes

• Ignore may turn some data errors into warnings; it is not limited to duplicate keys.
• Update assigns every column in the current INSERT, potentially including the primary key. It does not protect business fields automatically.
• Update references incoming values with VALUES(column). Write explicit SQL when the target server does not accept this syntax.
• Inserts, changed rows, and unchanged rows may report different update counts. Do not treat the result as a count of newly inserted rows.