Managing & Using MySQL, 2nd Edition

Name

LOAD

Syntax

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE file [REPLACE|IGNORE]
INTO TABLE table [delimiters] [(columns)]

Description

Reads a text file and inserts its data into a database table. This method of inserting data is much quicker than using multiple INSERT statements. Although the statement may be sent from all clients like any other SQL statement, the file referred to in the statement is assumed to be located on the server unless the LOCAL keyword is used. If the filename does not have a fully qualified path, MySQL looks under the directory for the current database of the file.

With no delimiters specified, LOAD DATA INFILE will assume that the file is tab delimited with character fields, special characters escaped with backslashes (\), and lines terminated with newline characters.

In addition to the default behavior, you may specify your own delimiters using the following keywords. Delimiters apply to all tables in the statement.

FIELDS TERMINATED BY 'c'

Specifies the character used to delimit the fields. Standard C language escape codes can be used to designate special characters. This value may contain more than one character. For example, FIELDS TERMINATED BY ',' denotes a comma-delimited file and FIELDS TERMINATED BY '\t' denotes tab delimited. The default value is tab delimited.

FIELDS ENCLOSED BY 'c'

Specifies the character used to enclose character strings. For example, FIELD ENCLOSED BY '"' would mean that a line containing "one, two", "other",