List:General Discussion« Previous MessageNext Message »
From:Eric Bergen Date:September 4 2004 1:42am
Subject:Re: multi inserts and duplicate key handling
View as plain text  
INSERT IGNORE INTO as Paul said will skip the duplicate values an
continue inserting, with a mutli row insert mysql will only skip the
duplicate values as the below example shows.

mysql> create table t (t int not null, unique index (t));
Query OK, 0 rows affected (0.02 sec)

mysql> desc t;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| t     | int(11) |      | PRI | 0       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into t (t) values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> delete from t where t in(2, 3);
Query OK, 2 rows affected (0.01 sec)

mysql> insert ignore into t (t) values (1), (2), (3);
Query OK, 2 rows affected (0.00 sec)
Records: 3  Duplicates: 1  Warnings: 0

mysql> select * from t;
+---+
| t |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

-Eric

On Fri, 3 Sep 2004 19:23:03 -0500, Paul DuBois <paul@stripped> wrote:
> At 17:03 -0700 9/3/04, Mir Islam wrote:
> >Does anyone know mysql handles duplicate key handling during multi inserts?
> >
> >INSERT INTO test values(1,2),(3,4),(4,5),(1,6),(6,7);
> >
> >Now the above statement fails and gives duplicate key error and exits.
> >Is there a way for it to ignore the key violation and continue
> >processing the batch?
> 
> INSERT IGNORE INTO ...
> 
> --
> Paul DuBois, MySQL Documentation Team
> Madison, Wisconsin, USA
> MySQL AB, www.mysql.com
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
> 



-- 
Eric Bergen
eric.bergen@stripped
Thread
multi inserts and duplicate key handlingMir Islam4 Sep
  • Re: multi inserts and duplicate key handlingPaul DuBois4 Sep
Re: multi inserts and duplicate key handlingEric Bergen4 Sep