Mysql: “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, or
• INSERT 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?

 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 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 |
+----+------+

---

Something important to add: When using INSERT IGNORE and you do have key violations, MySQL does NOT raise a warning!

If you try for instance to insert 100 records at a time, with one faulty one, you would get in interactive mode:

Query OK, 99 rows affected (0.04 sec)

Records: 100 Duplicates: 1 Warnings: 0

As you see: No Warnings! This behavior is even wrongly described in the official Mysql Documentation.

If your script needs to be informed, if some records have not been added (due to key violations) you have to call mysql_info() and parse it for the "Duplicates" value.

---

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.

---

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.

---

If using insert ignore having a SHOW WARNINGS; statement at the end of your query set will show a table with all the warnings, including which IDs were the duplicates.

Using INSERT IGNORE with MySQL to prevent duplicate key errors

An error will occur when inserting a new record in MySQL if the primary key specified in the insert query already exists. Using the "IGNORE" keyword prevents errors from occuring and other queries are still able to be run.

Why?

Although you shouldn't normally attempt to insert a record without first checking if the primary key you want to use has already been used, there may be times when this is required, such as when multiple developers need to update their own copies of a database, and a particular record may already exist in one or other of the databases.

Inserting a single record

The syntax is simple - just add "IGNORE" after "INSERT" like so:

INSERT IGNORE INTO mytable
    (primaryKey, field1, field2)
VALUES
    ('abc', 1, 2);

Inserting multiple records

When inserting mutiple records at once, any that cannot be inserting will not be, but any that can will be:

INSERT IGNORE INTO mytable
    (primaryKey, field1, field2)
VALUES
    ('abc', 1, 2),
    ('def', 3, 4),
    ('ghi', 5, 6);

In the second example above, if records already existed for 'abc' and 'def' then those two records would not be inserted, but the record for 'ghi' would be.
 

Related posts:

• MySQL: Find records in one table that are not in another - revised
• Using SELECT REPLACE with MySQL
• Insert multiple records into MySQL with a single query

Query Mysql in the for loop for not inserting all values

I have the following for loops, which should insert values into a mysql table. There is a users array and it is looping over the array and calculation statistics, and it should be inserting all the values into the mysql table, but it only inserts the operation done on the first element of the users array and not the rest. It is ignoring the first for loop, and only inserts for the second for loop, even though, I can see that the loops are doing what they are supposed to do, it is just not being inserted into mysql.

for($i=0; $i<count($users); $i++)
{
    for($j=$i+1; $j<count($users); $j++)
    {
        $user1=$users[$i];
        $user2=$users[$j];
        $dat1=getData($user1);
        $dat2=getData($user2);
        $union=count(array_unique(array_merge($dat1, $dat2)));
        $intersect=count(array_intersect($dat1, $dat2));
        $rate=$intersect/$union;
        $sql = "INSERT IGNORE into dat_net VALUES ($user1,$user2,$union,$intersect,$rate)";
        $sqlresults = mysql_query($sql);
        echo " ".$user1." ".$user2." \n";
        if ($sqlresults === false) {
    // An error has occured...
    echo mysql_error();
}

    }

}

Solved due to tips in the comments: I had inadvertently set up keys for the columns, so it wasn't inserting due to duplication.

---

First of all insert all data using one query. Generate it in for loop and call mysql query once.

You can check if your generated query works simply by echo it before execute and try to run it using some sql client.