LOAD DATA INFILE

The LOAD DATA INFILE syntax is as follows:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE    'file.txt' [REPLACE | IGNORE] INTO TABLE table_name    [FIELDS [TERMINATED BY '\t'] [[OPTIONALLY] ENCLOSED BY    ''] [ESCAPED BY '\\' ]] [LINES TERMINATED BY '\n']    [IGNORE number LINES] [(field_name,...)]

LOAD DATA reads data from a text file and adds it to a table. This is a quicker way of adding high volumes of data than using INSERT.

The LOCAL keyword indicates that the file is on the client machine; otherwise the file is assumed to be on the database server. LOCAL will not work if the server was started with the --local-infile=0 option, or the client has not been enabled to support it.

Files on the server must be readable by all or be in the database directory, and you need the FILE permission to use LOAD DATA for a file on the server.

On the server, the file is assumed to be in the database directory of the current database if no path is given. If the path is relative, it is assumed to be from the data directory. Absolute paths can also be used.

The LOW PRIORITY keyword causes the LOAD DATA to wait until no other clients are reading the table before processing it.

The CONCURRENT keyword allows other threads to access a MyISAM table at the same time as the LOAD DATA is executing (which will slow down the LOAD DATA).

The REPLACE keyword causes MySQL to delete and replace an existing record if it has the same primary or unique key as the record being added. IGNORE causes MySQL to continue with the next record.

If a FIELDS clause is specified, at least one of TERMINATED BY, [OPTIONALLY] ENCLOSED BY, and ESCAPED BY is required. If no FIELDS clause is specified, the defaults are assumed to be FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'. These clauses specify the character at the end of a field (default tab), surrounding a field (default nothing), and the escape character (default backslash). Be careful when using Windows paths to escape the path correctly.

Without a LINES clause, the default is assumed to be LINES TERMINATED BY '\n'. This specifies the character at the end of a record (default newline).

The IGNORE number LINES option ignores a number of lines at the top of the file (which is useful when the file contains a header).

LOAD DATA INFILE is the complement of SELECT...INTO INFILE.