I am uploading csv file contents into mysql database. I am not sure which approach is best & efficient & nice.
1] Using the LOAD DATA INFILE SQL statement
LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);
2] Using a script to parse and import the file for e.g. fgetcsv() function in php
If you can be sure of the content of the cvs, use method 1.
But if you are unsure you may wish the script to do some sanity checking of the contents of the file. In this case use method 2.
Loading directly into the database will likely be more efficient than using a script - you don't have the overhead of running the script, reading/parsing, putting together queries, etc. You just move the data over.
If you need to do this manually 100 times a day, then you're going to want something more automated, like a script. Then you need to consider your personal efficiency and level of effort.
Only reason to not use LOAD DATA INFILE SQL is if you where to process some of the data. For instance if had to convert or join data before insert. But if its a straight import this is by far the fastest.
I personally prefer LOAD DATA INFILE because loading script-wise usually uses multiple insert statements. If you need to modify the CSV it'll be much easier from a script, but in my experience LOAD DATA INFILE is faster.
LOAD DATA LOCAL INFILEfor speed and ease of use.fgetcsv(), you will either need to call multipleINSERTstatements, or write a bunch of code to build one very longINSERTstatement with aVALUES (),(),(),(),()...chain. A native CSV import in MySQL will be far easier.