Menu
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.
986
Introduction to MySQL INSERT IGNORE statement When you use the INSERT statement to add multiple rows to a table and if an error occurs during the processing, MySQL terminates the statement and returns an error. As the result, no rows are inserted into the table.
How can insert 1000 records at a time in MySQL? MySQL INSERT multiple rows statement In this syntax: First, specify the name of table that you want to insert after the INSERT INTO keywords. Second, specify a comma-separated column list inside parentheses after the table name.
You can insert an infinite number of rows with one INSERT statement. For example, you could execute a stored procedure that has a loop executed a thousand times, each time running an INSERT query. Or your INSERT could trip a trigger which itself performs an INSERT. Which trips another trigger.
Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.
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.
97
There is also a limit of 50,000 rows an INSERT query may have.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
