SQL Basic Statement (3) LOAD DATA INFILE

Use LOAD Statement Bulk Data Entry

Grammar:

LOADDATA[low_priority | CONCURRENT] [LOCAL]INFILE'file_name'    [REPLACE | IGNORE]     into TABLETbl_name[CHARACTER SET charset_name]    [{fields | COLUMNS} [TERMINATED by ' string ']        [[Optionally]Enclosed by 'Char']        [escaped by ' char ']    ]    [LINES [Starting by ' string ']        [TERMINATED by ' string ']    ]    [IGNORE number LINES]    [(Col_name_or_user_var,...)]    [SET col_name = expr,...]

The LOAD DATA infile statement reads from a text file into a table at a high speed. If you specify the local keyword, read the file from the client host. If local is not specified, the file must be located on the server.

For security reasons, when reading a text file located on the server, the file must be in the database directory or read by everyone. Also, in order to use the load DATA INFILE on the files on the server, you must have file permissions on the server host.

The Replace and Ignore keywords control the repetitive processing of existing unique key records. If you specify replace, the new row replaces the existing rows that have the same unique key value. If you specify ignore, skip the input of the duplicate rows of existing rows that have unique keys. If you do not specify any of the options, an error occurs when a duplicate key is found, and the remainder of the text file is ignored.

If you use the local keyword to load data from a native file, the server has no way to stop the transfer of the file during the operation, so the default behavior is as if ignore were specified.

The search for documents

• If an absolute pathname is given, the server uses that path name.
• If you give a relative pathname to one or more predecessor parts, the server searches for files relative to the server's data directory.
• If you give a file name without a predecessor, the server looks for the file in the database directory of the current database.

Fields and the LINES syntax for clauses

If you specify a fields clause, each of its clauses (TERMINATED by, [optionally] enclosed by and escaped by) is also optional, except that you must specify at least one of them. If you do not specify a fields clause, the default value is the same as you would write:

Fields TERMINATED by ' \ t ' enclosed by ' \ escaped by ' \ \ '

If you do not specify a lines clause, the default value is the same as you write:

LINES TERMINATED by ' \ n '

In other words, the default value causes the input to be read, and the LOAD DATA infile behaves as follows:

• Look for row boundaries at line breaks
• To divide rows into fields at a locator
• Do not expect fields to be encapsulated by any quotation mark characters
• A locator, line feed, or "\" that starts with "\" is interpreted as part of the literal character of the field value

SQL Basic Statement (3) LOAD DATA INFILE