MySQL database load DATA INFILE Syntax

1.LOAD DATA INFILE is used to write the contents of a text file to the MySQL table at high speed , and SELECT ... the action of into FILE is corresponding, an import, an export. Use LOAD DATA INFILE requires MySQL Read access to the file to be imported , with the following syntax:

LOAD DATA [Low_priority | CONCURRENT] [LOCAL] INFILE'file_name'[REPLACE|IGNORE]    Into TABLE tbl_name [PARTITION (Partition_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|ROWS}]    [(Col_name_or_user_var,...)] [SET col_name= Expr,...]

SELECT ... Into OUTFILE'file_name'[CHARACTER SET charset_name] [{ fields|COLUMNS} [TERMINATED by'string'] [[optionally] enclosed by'Char'] [escaped by'Char']] [LINES [Starting by'string'] [TERMINATED by'string']]from table_name;

[Low_priority | CONCURRENT]
Low_priority: When this option is added, the executionLOAD DATAthe command will be delayed until no other client has read the target table. The low_priority option is only available for table-level lock storage engines (MyISAM,memory,MERGE , etc.).
CONCURRENT: Parallel meaning, if this option is specified, means that theLOAD DATAprocess, others can also read the information in the table. If you useConcurrent option, no matter if there are people inLOAD DATAThe target table is read in the process .LOAD DATAhave a certain impact on performance. For row-level-basedReplication,The concurrent optionMySQL 5.5.1Was not previously available.

[LOCAL]
the LOCAL option determines the location of the load data file and how it is handled after an error in the load data process.
If the parameter is --local-infile=0, then the local parameter is not available.
With the LOCAL option, at LOAD DATA , the client reads the file and sends it to the server, which is temporarily stored in the Temp folder on the server (under the system Temp folder, not The Temp folder for the MySQL service, not the folder specified by tmpdir,slave_load_tmpdir .
with the LOCAL option,LOAD DATA will be slightly slower.
If you do not add the local option,LOAD data will be interrupted when there is a data error or the primary key is duplicated, and the unique index is duplicated, plus the local After that, there will be no interruption, as explained in the following IGNORE options for How to handle the above error after adding LOCAL .

about path issues with files:
1.if the local option is specified, the file being imported can be either on the client or on the server side, and if no localis specified, theimported file must be on the server;
2.we try to use an absolute path to identify the file being imported. If we use a relative path:
a)in the case where the LOCAL option is specified, the relative path points to the directory where the client resides;
b)under the option to specify LOCAL , the relative path points to the directory where the data file is located or the default library for the MySQL service;
- If the relative path is'./a.txt', then point to the directory where the data files are located;
- If the relative path is'a.txt', then point to the directory where the MySQL Service Default library is located;

[REPLACE | IGNORE]
REPLACE:duplicate rows encountered during import, duplicate rows with primary key or unique index, will be replaced
IGNORE: Duplicate rows encountered during import, duplicate rows with primary key or unique index, skip and continue import
in cases where REPLACE and IGNORE are not specified , the processing method depends on whether the LOCAL option is specified when the above situation is encountered :
if LOCALis not specified, the import operation is interrupted;
if LOCALis specified, it is handled according to the IGNORE parameter when the above situation is encountered .
If you want to ignore the effect of the foreign key on LOAD DATA , you can set the parameters before importing :Set foreign_key_checks = 0

[{fields | COLUMNS}
[TERMINATED by 'string']
[[Optionally] enclosed by 'char']
[escaped by 'Char']
]

fields to define a split field for a file
TERMINATED by:defining field Separators
Enclosed by:define a field to enclose characters, such as:"Wyzc", then enclosed by' "'
[Optionally]:If this option is selected, the number type is not quoted, and other types of quotation marks
escaped by:Defining escape characters

[LINES
[Starting by ' string ']
[TERMINATED by ' string ']
]

LINES define a split field for each row
starting by : define which field each row starts reading from
TERMINATED by: Defines the Terminator for each line

If you do not specify Fields or LINES , the default is:

Fields TERMINATED by ' \ t ' enclosed by ' \ escaped by ' \ \ '
LINES TERMINATED by ' \ n ' starting by '

[IGNORE Number {LINES | ROWS}]

The IGNORE number lines option can be used to ignore the header of a column name at the beginning of a file:

LOAD DATA INFILE and SELECT ... some Tips for into OUTFILE:
for LOAD DATA INFILE and SELECT ... Into OUTFILE, the Parameters fields and LINES must be the same, if you specify these 2 parameters at the same time, then Fields must precede the LINES .
for a MyISAM Empty table, the non-unique index inside the table is created in batches at the time of import, which speeds up the LOAD DATA .
Sometimes we can disable the index of a table before importing, and then ENABLE The index of the table to speed up the LOAD DATA After import is complete .
starting with MySQL 5.6.2 ,LOAD DATA supports the import of partitioned tables.

When you use the Select ... with load DATA infile. into outfile the fields and processing options for the two commands must match when the data in a database is written into a file and then immediately read back to the database, otherwise, LOAD data infile will not interpret the contents of the file correctly. Assume that you use SELECT ... into OutFile writes a comma-delimited field to a file:

MySQL database load DATA INFILE Syntax