MySQL and mSQL

Name

LOAD

Synopsis

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

Reads a text file that is in a readable format and inserts the 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 just like any other SQL statement, the file referred to in the statement is assumed to be located on the server. If the filename does not have a fully qualified path, MySQL looks under the directory for the current database for the file. As of MySQL 3.22, if the LOCAL modifier is present, the file will be read from the client’s local filesystem.

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

In addition to the default behavior, you may specify your own delimiters using the following keywords:

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