Usage of loaddatainfile in mysql

Usage of loaddatainfile in mysql

The load data infile statement reads a table from a text file at a high speed.

1. Basic syntax

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, MySQL inserts data only when no one else reads the table. 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 on the server.

CONCURRENT: when load data is being executed, other threads will obtain DATA from the table again. Even if no other thread is using this table at the same time, using this option will slightly affect the performance of load data.

EPLACE and IGNORE: keywords are used to process input records that are repeated with existing primary key values. If REPLACE is specified, the input row replaces the existing row (that is, the row with the same primary index value as the existing row ). Refer to the REPLACE syntax. If IGNORE is specified, the input row that already has a primary key value is skipped. If either of them is not specified, the operation behavior depends on whether the LOCAL keyword is specified. If no LOCAL is specified, if duplicate key values are found, an error is generated and the rest of the text file is ignored.
FIELDS: Specifies the format of the delimiter. If this keyword is used.

Terminated by: Which character is used as the separator. BY default, it is a tab character (\ t), for example, TERMINATED by '\ t'
Enclosed by: End symbol of a field.
Escaped by: Start symbol of the field. The default value is a backslash (backslash: \), for example, escaped '\\'.

Load Data InFile 'C:/Data.txt' Into Table `TableTest` Fields Terminated By ',' Enclosed By '"' Escaped By '"' Lines Terminated By '\r\n';

Fields Terminated By ', 'enabledby' "'escaped '"'
Each field is separated by a comma. The content is included in double quotation marks.

Lines Terminated By '\ r \ n ';
Indicates that each piece of data is separated by a line break.

Lines terminated: Specifies the delimiter for each record. The default value is '\ n', which is a line break.

IGNORE number LINES: This option can be used to IGNORE the line at the beginning of the file. For example, you can use IGNORE 1 LINES to skip the first line containing the column Name:

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

Col_name_or_user_var: indicates the data table field:

SET col_name = expr: Provides values that are not from the input file.

LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, column2) SET column3 = CURRENT_TIMESTAMP;

3. Other Instructions
If you want to ignore the foreign key constraints in the LOAD operation, you can execute the SET FOREIGN_KEY_CHECKS = 0 statement before running load data.
If you use load data infile on an empty ceshi table, all non-unique indexes will be created in batches (like REPAIR ). When there are many indexes, this usually makes load data infile faster.