How can I insert a lot of data into mySQL database at once?

Question

I insert data from a text file into mySQL database and there will be inserted around 6000 or more entries at once.

try {
   $sql = "INSERT INTO data (id,name,date,place) values(?,?,?,?) ";
   $q = $pdo->prepare($sql);
   foreach($data as $row) {
      $q->execute(array($row['id'], $row['name'], $row['date'], $row['place']));
   }
}
catch (PDOException $pe) {
   echo $pe->getMessage();
}
catch (Exception $e ) {
   echo $e->getMessage();
}

I tried it with 3000 entries and everything works fine. But if I have more data to be inserted it happens that my page is blank and nothing is inserted into my database.

What could cause this problem and how can I solve it?

The page should not be blank. Turn on error reporting and see what kind of error are you getting. — Andrius, Nov 19 '15 at 09:25
Thank you for your answers. Should't `catch (PDOException $pe) { echo $pe->getMessage();}` give me an error message? — peace_love, Nov 19 '15 at 09:26
@madforstrength: I wrote `set_time_limit(0);` after `try {` but it is still not working` — peace_love, Nov 19 '15 at 09:35
Try using LOAD DATA INFILE 'file.txt' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; If you are facing difficulty in the syntax, better try importing data using a tool,some like [SQLyog](https://www.webyog.com/product/sqlyog) — Mathew, Nov 19 '15 at 10:28

Anand G · Accepted Answer · 2015-11-20T09:11:47.283

5

Since you already have text file then use below query

LOAD DATA INFILE 'file/path/to/file.txt' 
INTO TABLE table-name 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Make sure your data in file is in sequence with your table rows.

Read more here https://dev.mysql.com/doc/refman/5.6/en/load-data.html

edited Nov 20 '15 at 09:11

answered Nov 19 '15 at 09:29

Anand G

3,130
1
22
28

score 1 · Answer 2 · edited May 23 '17 at 11:44

1

NSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

From How to do a batch insert in MySQL

edited May 23 '17 at 11:44

Community

1
1

answered Nov 19 '15 at 09:28

Tangoo

1,329
4
14
34

score 1 · Answer 3 · answered Nov 19 '15 at 09:28

1

Instead of running multiple queries inside a loop, you should use the multi-insert syntax. Example:

INSERT INTO data (id,name,date,place) VALUES (:id1,:name1,:date1,:place1), (:id2,:name2,:date2,:place2), (:id3,:name3,:date3,:place3);

Check out this article on doing multi-inserts with prepared statements.

answered Nov 19 '15 at 09:28

Wayne Whitty

19,513
7
44
66

Thank you, I am not sure how exactly you mean it, I cannot write name1,name2,name3 etc. because I have for example 5000 names and do not know exactly how many. – peace_love Nov 19 '15 at 10:00

score 1 · Answer 4 · edited May 23 '17 at 12:30

1

Use mysql LOAD DATA INFILE

refer to This Answer

If you use PDO or the like with looping, you are doing it wrong, and slow.

edited May 23 '17 at 12:30

Community

1
1

answered Nov 19 '15 at 09:34

Drew

24,851
10
43
78

1

Thank you for your answer, this seem to be a very nice solution, but it seems to be very difficult, I still do not understand how it works. I am parsing the text file and put everything into variables to make a check before I am inserting the data into the database. Do I have to write my code totally new? – peace_love Nov 19 '15 at 09:52
The point is to let mysql and load data infile handle it for you versus you in slow loops. They built that functionality in for performance, and to keep people from sweating the loops, whether in PHP, Java, you name it. Plus it outperforms by factors of 10 – Drew Nov 19 '15 at 09:59

How can I insert a lot of data into mySQL database at once?

4 Answers4