mysql multiple insert - what happens on error?

Question

What happens in mysql multiple records insert during an error. I have a table:

id  | value
2   | 100
UNIQUE(id)

Now i try to execute the query:

INSERT INTO table(id, value) VALUES (1,10),(2,20),(3,30)

I will get a duplicate-key error for the (2,20) BUT... Will the (1,10) get into the database? Will the (3,30) get into the database?

Answer 1

in your case the whole query will fail and you won't have any of the rows you've tried to insert into your table

Answer 2

as PierrOz pointed out in your case nothing will be inserted,

but you might want to look into the 'on duplicate key update' clause of the insert statement:

• http://feedblog.org/2006/07/27/using-on-duplicate-key-update-to-improve-mysql-replication-performance/
• http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html