LOAD DATA INFILE

Syntax:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL]
  INFILE 'filename'
  [REPLACE | IGNORE]
  INTO TABLE table_name
  [FIELDS [TERMINATED BY 'char']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char' ]]
  [LINES TERMINATED BY 'char']
  [IGNORE num LINES]
  [(column_list)]

Description: Reads the text file given by filename and imports data into the table given by table_name. Works the same as mysqlimport.

By specifying LOW_PRIORITY, execution waits until no other clients are reading from the table. By specifying CONCURRENT with a MyISAM table, other clients can read from the table while LOAD DATA INFILE is executing.

LOCAL means that a local file on the client, rather than the server, will be used for reading.

The REPLACE and IGNORE keywords cause rows with duplicate primary or unique keys to be replaced by those in the new file, or ignored, respectively.

The optional FIELDS keyword can be followed by a number of keywords:

• TERMINATED BY— Specifies the character used to separate the fields (default ).

• ENCLOSED BY— The characters enclosing each field (default nothing); if you specify OPTIONALLY ENCLOSED BY, the character is expected to enclose CHAR and VARCHAR fields only.

• ESCAPED BY— Specifies the escape character for special characters (default ).

• LINES TERMINATED BY— Specifies the termination of each row (default ).

The IGNORE num LINES causes the first num lines at the start of the file to be skipped.

To load only some of a table's columns, specify column_list, a list of column names separated by commas. Data in the file that corresponds with columns not listed will be ignored.

Description in: Day 7