INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE
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, orINSERT IGNORE
which 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?
3Answer
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
orUNIQUE
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 replication slaves 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 |
+----+------+
- answered 8 years ago
- Gul Hafiz
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
orUNIQUE
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 replication slaves 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 |
+----+------+
- answered 8 years ago
- Sunny Solu
In case you want to see what this all means, here is a blow-by-blow of everything:
CREATE TABLE `users_partners` (
`uid` int(11) NOT NULL DEFAULT '0',
`pid` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`uid`,`pid`),
KEY `partner_user` (`pid`,`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
Primary key is based on both columns of this quick reference table. A Primary key requires unique values.
Let's begin:
INSERT INTO users_partners (uid,pid) VALUES (1,1);
...1 row(s) affected
INSERT INTO users_partners (uid,pid) VALUES (1,1);
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'
INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1);
...0 row(s) affected
INSERT INTO users_partners (uid,pid) VALUES (1,1) ON DUPLICATE KEY UPDATE uid=uid
...0 row(s) affected
note, the above saved too much extra work by setting the column equal to itself, no update actually needed
REPLACE INTO users_partners (uid,pid) VALUES (1,1)
...2 row(s) affected
and now some multiple row tests:
INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'
INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...3 row(s) affected
no other messages were generated in console, and it now has those 4 values in the table data. I deleted everything except (1,1) so I could test from the same playing field
INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4) ON DUPLICATE KEY UPDATE uid=uid
...3 row(s) affected
REPLACE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...5 row(s) affected
So there you have it. Since this was all performed on a fresh table with nearly no data and not in production, the times for execution were microscopic and irrelevant. Anyone with real-world data would be more than welcome to contribute it.
- answered 8 years ago
- Sunny Solu
Your Answer