MySQL - Ignore Insert Error: Duplicate Entry

MySQL - ignore insert error: duplicate entry

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.

Or, you can use INSERT... ON DUPLICATE KEY UPDATE syntax if you want to perform an update to the record instead when you encounter a duplicate.

Edit: Thought I'd add some examples.

Examples

Say you have a table named tbl with two columns, id and value. There is one entry, id=1 and value=1. If you run the following statements:

REPLACE INTO tbl VALUES(1,50);

You still have one record, with id=1 value=50. Note that the whole record was DELETED first however, and then re-inserted. Then:

INSERT IGNORE INTO tbl VALUES (1,10);

The operation executes successfully, but nothing is inserted. You still have id=1 and value=50. Finally:

INSERT INTO tbl VALUES (1,200) ON DUPLICATE KEY UPDATE value=200;

You now have a single record with id=1 and value=200.

INSERT IGNORE vs INSERT ... ON DUPLICATE KEY UPDATE

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

Ignore duplicate key error in MySQL on INSERT

This error occurs because you have specified one of the columns of your table to be UNIQUE. You cannot have 2 rows with the same value for this column. If you want to replace the existing row instead, use REPLACE instead of INSERT. If you really want rows containing the same value for the column, remove the UNIQUE modifier from that column.

Using INSERT IGNORE as described in some of the other answers will prevent the error being issued, but will not update the table.

skipping duplicate values in mysql while inserting to a target table

If you want to avoid duplicate entries, you never EVER query first to see if a record exists. You place a unique constraint and use INSERT IGNORE or INSERT INTO ... ON DUPLICATE KEY UPDATE.

The problem with first approach is that you can (and will) get false positives.

In your particular case, the fix is quite easy. You need to add IGNORE after INSERT. That will skip the record if duplicate and continue onto the next one.

INSERT IGNORE INTO adggtnz.`reg02_maininfo`(farmermobile,farmername,farmergender,origin) 
SELECT mobile_no, name, sex, 'EADD' FROM EADD.farmer

MySql Insert multiple and Ignore duplicate records

To start with, you want to create a unique constraint on categories/product tuples to avoid duplicates:

alter table _categories_products  
    add constraint _categories_products _bk 
    unique (product_id, category_id);

From that point on, an attempt to insert duplicates in the table would, by default, raise an error. You can trap and manage that error with MySQL on duplicate key syntax:

insert into _categories_products (product_id, category_id) 
values (1, 14), (1, 8), (1, 1), (1, 23)
on duplicate key update product_id = values(product_id)

In the case of duplicate, the above query performs a dumb update on product_id, which actually turns the insert to a no-op.

MariaDB: Hide Warning Duplicate entry while commit INSERT IGNORE

Use ON DUPLICATE KEY UPDATE to hide the warnings like:

INSERT INTO `so` (id)
VALUES
    (6)
ON DUPLICATE KEY UPDATE  id = VALUES(id);

sample

mysql> INSERT INTO `so` (id)
    -> VALUES
    ->     (6)
    -> ON DUPLICATE KEY UPDATE  id = VALUES(id);
Query OK, 0 rows affected (0,00 sec)

mysql> INSERT INTO `so` (id) VALUES (6);
ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'
mysql> INSERT IGNORE INTO `so` (id) VALUES (6);
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> INSERT IGNORE INTO `so` (id) VALUES (6);
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> INSERT INTO `so` (id) VALUES (6) ON DUPLICATE KEY UPDATE  id = VALUES(id);
Query OK, 0 rows affected (0,01 sec)

mysql>

Skip insert on duplicate entry in mysql

You can use "INSERT IGNORE INTO".it's return updated row count.

INSERT IGNORE INTO favorite(login_id,driver_id) VALUES (login_id,driver_id) (login_id,driver_id).....

How to avoid duplicate entries in a MySQL database without throwing an error

You can utilize the INSERT IGNORE syntax to suppress this type of error.

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

In your case, the query would become:

INSERT IGNORE INTO `database` (title, introduction) VALUES (%s, %s)

---

Related Topics