Performatic insert into database to catch duplicate key error-CodePudding

My application can occur to insert duplicate primary key data, my doubt is what would be the most performative way, check before inserting if the key already exists or simply insert and let the bank return an error and then treat it? Which has less impact? I thank

CodePudding user response：

The most performant method would be to use an INSERT query with a ON DUPLICATE KEY UPDATE clause.

This will insert a new record if the primary key doesn't exist and will update only the specified fields for the primary key record if it already exists (it will not throw an error).

For example the following code will create a new user if the the user_id doesn't exist. If the user_id does exist, it will only update the last_visited_date.

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 both create new records and update already existing records with one query, which prevents the need for checking for an already existing primary key.

Note: if the primary key is an auto-incremented index, MySQL treats ON DUPLICATE UPDATE clauses the same as INSERT queries so the next ID will be incremented by 1 anytime the primary key already exists.

If you don't want to use an ON DUPLICATE KEY UPDATE clause, catching the error for already existing primary keys would be the most performant.