Table of Contents for

MySQL in a Nutshell, 2nd Edition

You can use this statement to import organized data from a text file into a table in MySQL. The file can be either on the server or on the client.

For a file on the server, if you use a bare filename (such as input.txt) or a relative path (such as ../), the file is found relative to the directory of the database into which the data is to be imported. If the file is not located in the directory’s database, the file permissions must be set so it can be read for all filesystem users.

For a file on the client, the LOCAL keyword must be given. This feature must be enabled on both the client and the server by using the startup option of --local-infile=1. See Chapter 15 for more information on server and client settings.

If a data text file contains rows of data duplicating some of the rows in the table into which it’s being imported, an error will occur and the import may end without importing the remaining data. Duplicate rows are those that have the same values for key columns or other unique columns. To instruct the server to ignore any errors encountered and to continue loading other rows, use the IGNORE keyword. Use the SHOW WARNINGS statement to retrieve the error messages that would have been displayed. To instruct the server to replace any duplicate rows with the ones being imported, use the REPLACE keyword. This will completely replace the values of all columns in the row, even when the new record contains no data for a column and the existing one does.

Here is a basic example of LOAD DATA INFILE:

LOAD DATA INFILE '/tmp/catalog.txt'
INTO TABLE catalog
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n';

In this example, the file to be loaded is in the /tmp directory and is called catalog.txt. The data contained in the file is to be inserted into the catalog table in the current database in use. Each field in the text file is terminated with a vertical bar character. The rows of data in the text file are on separate lines. They are separated by a newline character (\n). This is the default for a Unix text file. For DOS or Windows systems, lines are usually terminated with \n\r, signifying a newline and a Return character. If the rows start with a special character, you can identify that character with the LINES STARTED BY clause.

This statement also offers the ENCLOSED BY clause to specify a character that can start and terminate a field, such as a quotation mark. You can use the OPTIONALLY keyword to indicate that the character is used for enclosing columns containing string data, but optional for numeric data. Numeric fields may then include or omit the given character. For example, if the optional character is an apostrophe (single quote), a numeric value for a field may be given as '1234' or 1234, so MySQL should expect and accept both.

The ESCAPED BY clause indicates the character used in the input file to escape special characters. The backslash (\) is the default value.

Some data text files contain one or more lines of column headings that should not be imported. To omit these initial lines from the import, use the IGNORE count LINES clause, where count is the number of lines to ignore.

For some data text files, the fields of data are not in the same order as the columns of the receiving table. Sometimes there are fewer fields in the text file than in the table. For both of these situations, to change the order and number of columns, add a list of columns and their order in the text file to the end of the statement within parentheses. Here is an example of such a scenario:

LOAD DATA LOW_PRIORITY INFILE '/tmp/catalog.txt' IGNORE
INTO TABLE catalog
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(cat_id, description, price);

The first line of the text file contains column headings describing the data, but that line will not be imported because of the IGNORE 1 LINES clause here. The catalog table has several more columns than the three that are being imported, and they are in a different order. Finally, because this import is not critical, the LOW_PRIORITY keyword near the beginning of the statement instructs the server to handle other queries on the catalog table before running this statement. If this was replaced with CONCURRENT, the import would be performed even if other clients were querying the same table.

As of version 5.0.3 of MySQL, the list of fields can contain column names and user variables. Also, SET may be added to set or change the value to be imported. Here is an example:

LOAD DATA LOW_PRIORITY INFILE '/tmp/catalog.txt' IGNORE
INTO TABLE catalog
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(cat_id, @discarded, description, @mfg_price)
SET price = @mfg_price * .9;

In this example, the table receiving the data has five columns. The second one is to be ignored and stored in a discarded user variable. The third column is the price. Since the company sells the manufacturer’s products at ten percent less than the manufacturer’s suggested retail price, the statement receives the raw value in the user variable @mfg_price and then we use SET to adjust that value for the column when loaded.