bulk insert mysql - can i use ignore clause? is there a limit to no. of records for bulk insert?

Question

I have a bunch of data that i want to insert and i have decided to use bulk insert for mysql.

insert into friends (requestor, buddy) values (value1, value2), (value2, value1), (value3, value4), (value4, value3), ...

i would like to know the following:

1) can i use ignore? eg

insert ignore into friends (requestor, buddy) values (value1, value2), (value2, value1), (value3, value4), (value4, value3), ...

what happens if i have duplicate? will it a) not insert everything? b) insert the records before the duplicate record and STOP processing the data after that? c) ignore the duplicate and carry on with the rest?

2) is there a limit to the no. of records i can use for a bulk insert like this?

Thank you.

Answer 1

Yes, you can use the "ignore" keyword, and it will simply ignore duplicate errors. It will insert every row that it can, skipping those that would lead to duplicates (and it will not stop processing data.) If you do something like this (where we assume that the first column is a primary key):

insert ignore into c values (1,1), (2,2), (3,3), (3,5), (4,5);

Then that means that (3,3) will be inserted and (3,5) will not. Everything but (3,5) will be inserted (assuming a fresh table.)

There is probably no hard limit, but you might want to do testing to see where you should draw the line based on your needs.

Edit: It does seem that MySQL has a configurable limit on the size of SQL queries, with the default being 1MB. More info: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_query_cache_limit.

Answer 2

There is also a limit of 50,000 rows an INSERT query may have.