Difference between CSV import and CSV using LOAD D

问题:

In phpMyAdmin there are two options to import a CSV file.

One is CSV. The other is CSV using LOAD DATA.

What's the difference between these two? Is there an advantage to using one over the other?

回答1:

LOAD DATA INFILE is a MySQL query that works completely independently of PHPMyAdmin.

The CSV import probably involves uploading the file to the PHPMyAdmin server, where it parses the file and builds a series of INSERT statements to be run against the server.

Personally, I wouldn't trust anything PHPMyAdmin does ;-) - however, actual performance will probably depend on your table structure and the data.

I will note, however, that MySQL takes some very efficient shortcuts when inserting data from a LOAD DATA INFILE command.

回答2:

As stated above the LOAD DATA option is actually telling phpMyAdmin to use the MySQL command to let MySQL parse and load the file rather than phpMyAdmin parsing it first.

As also stated above, giving MySQL access to load the file can be dangerous if you don't feel 100% secure about the source and accuracy of the file it's self. It's like using a php form with no sql injection protection to insert data.

However, in some cases phpMyAdmin does not format the data correctly or has trouble parsing it when the regular CSV" option is used. This will cause un-explained errors such as "invalid format on line N" or "incorrect field count on line N" Those might not be exact error messages since I'm not logged into phpMyAdmin at the moment. In these cases the LOAD DATA option can be used to get passed the error. I think the extra option of Use local keyword has to do with making sure the correct commands for that specific version of MySQL on the local server is used. Not sure about the last part though.

Something to keep in mind is also the size of the file (number of lines being imported) I have had to break down a 1600 line file into smaller files even when using the LOAD DATA option in order to get it to go through. It gave no errors but the "affected rows" was incorrect when the file was too big.

回答3:

The first option will have phpMyAdmin parse the CSV file itself and then generate and execute the SQL to insert the data. The second option will let MySQL take care of loading, processing, and inserting the data.

Both options (should) behave the same way, but the LOAD DATA INFILE option is generally much faster, and you don't have to worry about PHP's memory/execution time limits. The only problem is that it isn't supported by all configurations because there are security implications for giving MySQL access to the uploaded files, and as such it is often disabled (ex. shared hosting).

回答4:

CSV and CSV using LOAD DATA. The first method is implemented internally by phpMyAdmin and is the recommended one for its simplicity. With the second method, phpMyAdmin receives the file to be loaded, and passes it to MySQL. In theory, this method should be faster. However, it has more requirements due to MySQL itself

回答5:

To add to the other replies: the "CSV" one insists you have exactly the same amount of columns in the text file and the table. "CSV using LOAD DATA" does not.