Menu
I actually have a list of 100,000 records which I'd like to insert into the MySQL database.
I have tried to insert them with foreach and simple INSERT INTO however it took a lot of time to insert even 100 row. Like 1 second / row.
IS there any method to insert these rows much faster?
848
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 use the following methods to speed up inserts: If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements.
Insert multiple rows in MySQL with the help of “values”. You can enclose the values with parentheses set with comma separation.
Using INSERT to add multiple rows at onceInserting records one statement at a time is more time consuming and less efficient than inserting multiple rows at once. MySQL allows you to specify multiple rows to add to the same table.
Using one INSERT statement with multiple rows is faster than one INSERT statement per row. This will reduce calls to the database.
Example:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
Make packages:
INSERT INTO `table_name`(`column`) VALUES('value'),VALUES('value1'),VALUES('value2'), ..., VALUES('valuen');
documentation for insert
or export data to csv or other text format and use LOAD DATA, look here: load data by mysql client
29
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
