Best Practice : Import CSV to MYSQL Database using PHP 5.x

Question

Howdy Folks,

what is the best solution to import large amounts of records into an MySQL or Oracle Database. I Think there are two ways :

• Insert every record with an persistent connection
• Create on BIG SQL-Statement and query the Database

Iam not sure wich is performing better ? Is there any Best Practice Solutions for this kind of operation ??

Answer 1

Must admit I tend to use MySQL Administrator for most of my large database restores. Not too sure about Oracle. Also depends on the size and structure of your MySQL.

I'm talking a couple of GBs of SQL data.

Answer 2

There is one other possibility: LOAD DATA INFILE

LOAD DATA LOCAL INFILE 'foobar.csv' 
INTO TABLE foobar
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n';

This requires that the MySQL server has access to the physical file. But moving/copying a file to an import directory should not be hard with PHP.

Answer 3

Import into MySQL you can use LOAD DATA INFILE

http://dev.mysql.com/doc/refman/5.1/en/load-data.html