Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

INSERT ... ON DUPLICATE KEY (do nothing)

People also ask

How do I use insert on duplicate key update?

The Insert on Duplicate Key Update statement is the extension of the INSERT statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.

What is insert ignore into?

The INSERT IGNORE command keeps the first set of the duplicated records and discards the remaining. The REPLACE command keeps the last set of duplicates and erases out any earlier ones. Another way to enforce uniqueness is to add a UNIQUE index rather than a PRIMARY KEY to a table.

How does on duplicate key update work?

ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE. The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set.

How do I ignore duplicate entries in MySQL?

You can use INSERT... IGNORE syntax if you want to take no action when there's a duplicate record. You can use REPLACE INTO syntax if you want to overwrite an old record with a new one with the same key.


Yes, use INSERT ... ON DUPLICATE KEY UPDATE id=id (it won't trigger row update even though id is assigned to itself).

If you don't care about errors (conversion errors, foreign key errors) and autoincrement field exhaustion (it's incremented even if the row is not inserted due to duplicate key), then use INSERT IGNORE like this:

INSERT IGNORE INTO <table_name> (...) VALUES (...)

HOW TO IMPLEMENT 'insert if not exist'?

1. REPLACE INTO

pros:

  1. simple.

cons:

  1. too slow.

  2. auto-increment key will CHANGE(increase by 1) if there is entry matches unique key or primary key, because it deletes the old entry then insert new one.

2. INSERT IGNORE

pros:

  1. simple.

cons:

  1. auto-increment key will not change if there is entry matches unique key or primary key but auto-increment index will increase by 1

  2. some other errors/warnings will be ignored such as data conversion error.

3. INSERT ... ON DUPLICATE KEY UPDATE

pros:

  1. you can easily implement 'save or update' function with this

cons:

  1. looks relatively complex if you just want to insert not update.

  2. auto-increment key will not change if there is entry matches unique key or primary key but auto-increment index will increase by 1

4. Any way to stop auto-increment key increasing if there is entry matches unique key or primary key?

As mentioned in the comment below by @toien: "auto-increment column will be effected depends on innodb_autoinc_lock_mode config after version 5.1" if you are using innodb as your engine, but this also effects concurrency, so it needs to be well considered before used. So far I'm not seeing any better solution.


Use ON DUPLICATE KEY UPDATE ...,
Negative : because the UPDATE uses resources for the second action.

Use INSERT IGNORE ...,
Negative : MySQL will not show any errors if something goes wrong, so you cannot handle the errors. Use it only if you don’t care about the query.