Importing data into MySQL

Rather than using a Perl script, for example, data can be imported directly into MySQL using LOAD DATA LOCAL INFILE. Note that if you don't include LOCAL, the file you want to import must be located on the server, rather than a client.

For example, suppose you have an ASCII file containing comma-delimited data that you want to insert into the table my_table. Type:

LOAD DATA LOCAL INFILE 'data.txt'
INTO TABLE my_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, field2, field3, field4, field5);

Here we have specified that the data is comma-delimited, and each line of the input file corresponds to a row in the table. The columns that each piece of data should be inserted into are also specified.

MySQL can read only the lines in a data file containing a specified prefix if necessary. For example, if each line with real data begins with DATA_ENTRY, and you want to ignore all others, add:

LINES STARTING BY 'DATA_ENTRY'

MySQL will skip the prefix, and anything that comes before it. Lines not including the prefix will be completely skipped.