Perform insert into database to catch duplicate key errors

Question

My application may insert duplicate primary key data, my doubt is what is the most efficient way, check if the key already exists before inserting, or just insert and let the bank return an error and then handle it? Which one has less impact? Thanks

Answer 1

The most efficient way is to use an INSERT query with the ON DUPLICATE KEY UPDATE clause.

If the primary key does not exist, this will insert a new record; if the primary key already exists, only the specified field of the primary key record will be updated (no error will be thrown).

For example, if user_id does not exist, the following code will create a new user. It will only update last_visited_date if user_id does exist.

INSERT INTO users
    (user_id, last_visited_date)
VALUES
    ({user_id}, UTC_TIMESTAMP())
ON DUPLICATE KEY UPDATE
    last_visited_date= VALUES(last_visited_date)

This approach allows you to create new records and update existing records with a single query, eliminating the need to check for existing primary keys.

Note: If the primary key is an auto-incrementing index, MySQL treats the ON DUPLICATE UPDATE clause as the same as the INSERT query, so the next ID will be incremented by 1 As long as the primary key already exists.

If you do not want to use the ON DUPLICATE KEY UPDATE clause, catching errors for existing primary keys will be the most performant approach.