The usage of the MySQL load data infile

The LOAD DATA infile statement reads from a text file into a table at a high speed.

1. Basic grammar

LOAD DATA [Low_priority | CONCURRENT] [LOCAL] INFILE ' file_name.txt '   [REPLACE | IGNORE] into   TABLE tbl_name   [Fields     [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,...]]

2. Parameter Description

Ow_priority: If you specify the keyword low_priority, then MySQL will wait until no one else reads the table before inserting the data. Case:

Load data  low_priority infile "/home/mark/data sql" into table Orders;

Local: If the local keyword is specified, the file is read from the client host. If local is not specified, the file must be located on the server.

CONCURRENT: When load data is executing, other threads retrieve the data from the table. Even if no other thread is using this table at the same time, using this option will slightly affect the performance of the load DATA.

Eplace and ignore: keywords handle input records that repeat with the primary key value that already exists. If replace is specified, the input row replaces the existing row (that is, the row with the same primary index value as the existing line). Refer to replace syntax. If ignore is specified, the input rows that are duplicated with the existing row primary key values are skipped. If you do not specify either of these, the action behavior depends on whether the local keyword is specified. No local is specified, and if duplicate key values are found, an error is generated and the remainder of the text file is ignored.
Fields: Specifies the split format of the file segment, if this keyword is used.

Terminated by: What character is used as the delimiter, and by default is the tab character (\ t), for example: terminated by ' \ t '
Enclosed by: Field Terminator number .
Escaped by: field start symbol , default backslash (backslash:\), for example: escaped by ' \ \ '.

Load Data InFile ' c:/data.txt ' into Table ' tabletest ' fields Terminated by ', ' enclosed by ' "' escaped by '" ' Lines termin Ated by ' \ r \ n ';

Fields Terminated by ', ' enclosed by ' "' escaped by '" '
Indicates that each field is separated by commas and the contents are enclosed in double quotation marks

Lines Terminated by ' \ r \ n ';
Indicates that each piece of data is separated by a newline character.

LINES TERMINATED: Specifies the delimiter for each record, which defaults to ' \ n ' as a newline character.

IGNORE number LINES: This option can be used to omit lines from the beginning of the file. For example, you can use ignore 1 LINES to skip the header row that contains the column name:

LOAD DATA INFILE '/tmp/test.txt ' into TABLE test IGNORE 1 LINES;

Col_name_or_user_var: Represents a data table field:

SET col_name = expr: Provides a value that is not derived from the input file.

LOAD DATA INFILE ' file.txt ' into TABLE T1 (column1, column2) SET column3 = Current_timestamp;

3. Other Instructions
If you want the foreign KEY constraint to be ignored in the mount operation, you can execute the SET FOREIGN_KEY_CHECKS=0 statement before you execute load DATA.
If the user uses load DATA INFILE on an empty Ceshi table, all non-unique indexes are created in batches (like repair). This can usually make the load DATA INFILE faster when there are many indexes.

The usage of the MySQL load data infile