"INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

ThomasGHenry_62701 0
Viewed 978251

While executing an INSERT statement with many rows, I want to skip duplicate entries that would otherwise cause failure. After some research, my options appear to be the use of either:

  • ON DUPLICATE KEY UPDATE which implies an unnecessary update at some cost, or
  • INSERT IGNORE implies an invitation for other kinds of failure to slip in unannounced.

Am I right in these assumptions? What's the best way to simply skip the rows that might cause duplicates and just continue on to the other rows?

insert mysql
12 Answers

I would recommend using INSERT...ON DUPLICATE KEY UPDATE.

If you use INSERT IGNORE, then the row won't actually be inserted if it results in a duplicate key. But the statement won't generate an error. It generates a warning instead. These cases include:

  • Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints.
  • Inserting a NULL into a column with a NOT NULL constraint.
  • Inserting a row to a partitioned table, but the values you insert don't map to a partition.

If you use REPLACE, MySQL actually does a DELETE followed by an INSERT internally, which has some unexpected side effects:

  • A new auto-increment ID is allocated.
  • Dependent rows with foreign keys may be deleted (if you use cascading foreign keys) or else prevent the REPLACE.
  • Triggers that fire on DELETE are executed unnecessarily.
  • Side effects are propagated to replicas too.

correction: both REPLACE and INSERT...ON DUPLICATE KEY UPDATE are non-standard, proprietary inventions specific to MySQL. ANSI SQL 2003 defines a MERGE statement that can solve the same need (and more), but MySQL does not support the MERGE statement.

A user tried to edit this post (the edit was rejected by moderators). The edit tried to add a claim that INSERT...ON DUPLICATE KEY UPDATE causes a new auto-increment id to be allocated. It's true that the new id is generated, but it is not used in the changed row.

See demonstration below, tested with Percona Server 5.5.28. The configuration variable innodb_autoinc_lock_mode=1 (the default):

mysql> create table foo (id serial primary key, u int, unique key (u));
mysql> insert into foo (u) values (10);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   10 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

mysql> insert into foo (u) values (10) on duplicate key update u = 20;
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

The above demonstrates that the IODKU statement detects the duplicate, and invokes the update to change the value of u. Note the AUTO_INCREMENT=3 indicates an id was generated, but not used in the row.

Whereas REPLACE does delete the original row and inserts a new row, generating and storing a new auto-increment id:

mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+
mysql> replace into foo (u) values (20);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  3 |   20 |
+----+------+
Bill_Karwin_208600

I routinely use INSERT IGNORE, and it sounds like exactly the kind of behavior you're looking for as well. As long as you know that rows which would cause index conflicts will not be inserted and you plan your program accordingly, it shouldn't cause any trouble.

user_565410

Replace Into seems like an option. Or you can check with

IF NOT EXISTS(QUERY) Then INSERT

This will insert or delete then insert. I tend to go for a IF NOT EXISTS check first.

user_348190

Potential danger of INSERT IGNORE. If you are trying to insert VARCHAR value longer then column was defined with - the value will be truncated and inserted EVEN IF strict mode is enabled.

user_62328910

ON DUPLICATE KEY UPDATE is not really in the standard. It's about as standard as REPLACE is. See SQL MERGE.

Essentially both commands are alternative-syntax versions of standard commands.

Chris_KL_581100

Adding to this. If you use both INSERT IGNORE and ON DUPLICATE KEY UPDATE in the same statement, the update will still happen if the insert finds a duplicate key. In other words, the update takes precedence over the ignore. However, if the ON DUPLICATE KEY UPDATE clause itself causes a duplicate key error, that error will be ignored.

This can happen if you have more than one unique key, or if your update attempts to violate a foreign key constraint.

CREATE TABLE test 
 (id BIGINT (20) UNSIGNED AUTO_INCREMENT, 
  str VARCHAR(20), 
  PRIMARY KEY(id), 
  UNIQUE(str));

INSERT INTO test (str) VALUES('A'),('B');

/* duplicate key error caused not by the insert, 
but by the update: */
INSERT INTO test (str) VALUES('B') 
 ON DUPLICATE KEY UPDATE str='A'; 

/* duplicate key error is suppressed */
INSERT IGNORE INTO test (str) VALUES('B') 
 ON DUPLICATE KEY UPDATE str='A';
user_17017890

INSERT...ON DUPLICATE KEY UPDATE is prefered to prevent unexpected Exceptions management.

This solution works when you have **1 unique constraint** only

In my case I know that col1 and col2 make a unique composite index.

It keeps track of the error, but does not throw an exception on duplicate. Regarding the performance, the update by the same value is efficient as MySQL notices this and does not update it

INSERT INTO table
  (col1, col2, col3, col4)
VALUES
  (?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
    col1 = VALUES(col1),
    col2 = VALUES(col2)

The idea to use this approach came from the comments at phpdelusions.net/pdo.

user_55195440
Related