Syntax
------ 
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE
'file_name'
 [REPLACE | IGNORE]
 INTO TABLE tbl_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,...]
 
Description
----------- 
Reads rows from a text file into the designated table on the
database at a very high speed. The file name must be given
as a literal string. 
 
Files are written to disk using the SELECT INTO OUTFILE
statement. You can then read the files back into a table
using the LOAD DATA INFILE statement. The FIELDS and LINES
clauses are the same in both statements. These clauses are
optional, but if both are specified then the FIELDS clause
must precede LINES.
 
In releases after MariaDB 5.5, LOAD DATA INFILE is unsafe
for statement-based replication.
 
Executing this statement activates INSERT triggers.
 
REPLACE and IGNORE
 
In cases where you load data from a file into a table that
already contains data and has a Primary Key, you may
encounter issues where the statement attempts to insert a
row with a Primary Key that already exists. When this
happens, the statement fails with Error 1064, protecting the
data already on the table. In cases where you want MariaDB
to overwrite duplicates, use the REPLACE keyword.
 
The REPLACE keyword works like the REPLACE statement. Here,
the statement attempts to load the data from the file. If
the row does not exist, it adds it to the table. If the row
contains an existing Primary Key, it replaces the table
data. That is, in the event of a conflict, it assumes the
file contains the desired row. 
 
This operation can cause a degradation in load speed by a
factor of 20 or more if the part that has already been
loaded is larger than the capacity of the InnoDB Buffer
Pool. This happens because it causes a lot of turnaround in
the Buffer Pool.
 
Use the IGNORE keyword when you want to skip any rows that
contain a conflicting Primary Key. Here, the statement
attempts to load the data from the file. If the row does not
exist, it adds it to the table. If the row contains an
existing Primary Key, it ignores the addition request and
moves on to the next. That is, in the event of a conflict,
it assumes the table contains the desired row.
 
LOCAL
 
When you issue this statement, the Server attempts to read
files from the host file system. Using the LOCAL keyword,
the statement instead attempts to read files from the
client. This allows you to insert files from the client's
local file system into the database.
 
In the event that you don't want the server to permit this
operation, (such as for security reasons), you can disable
support using local_infile. When this system variable is set
to 0, MariaDB rejects LOAD DATA LOCAL INFILE statements,
failing with an error message.
 
Character-sets
 
When the statement opens the file, it attempts to read the
contents using the default character-set, as defined by the
character_set_database system variable. 
 
In the cases where the file was written using a
character-set other than the default, you can specify the
character-set to use with the CHARACTER SET clause in the
statement. It ignores character-sets specified by the SET
NAMES statement and by the character_set_client system
variable. Setting the CHARACTER SET clause to a value of
binary indicates "no conversion."
 
The statement interprets all fields in the file as having
the same character-set, regardless of the column data type.
To properly interpret file contents, you must ensure that it
was written with the correct character-set. If you write a
data file with mysqldump -T or with the SELECT INTO OUTFILE
statement with the mysql client, be sure to use the
--default-character-set option, so that the output is
written with the desired character-set.
 
When using mixed character sets, use the CHARACTER SET
clause in both SELECT INTO OUTFILE and LOAD DATA INFILE to
ensure that MariaDB correctly interprets the escape
sequences.
 
The character_set_filesystem system variable controls the
interpretation of the filename.
 
It is currently not possible to load data files that use the
ucs2 character set.
 
Priority and Concurrency
 
In loading data from a file, there's a risk that the
statement will attempt insertions concurrent with reads from
another client, which can result in the read serving a
result-set that contains only part of the update from the
LOAD DATA INFILE statement.
 
Using the LOW_PRIORITY keyword, MariaDB delays insertions
until no other clients are reading from the table.
Alternatively, you can use the CONCURRENT keyword to perform
concurrent insertion.
 
The LOW_PRIORITY and CONCURRENT keywords are mutually
exclusive. They cannot be used in the same statement.
 
Progress Reporting
 
Since MariaDB 5.3, the LOAD DATA INFILE statement supports
progress reporting. You may find this useful when dealing
with long-running operations. Using another client you can
issue a SHOW PROCESSLIST query to check the progress of the
data load.
 
Using mysqlimport
 
MariaDB ships with a separate utility for loading data from
files: mysqlimport. It operates by sending LOAD DATA INFILE
statements to the server.
 
Using mysqlimport you can compress the file using the
--compress option, to get better performance over slow
networks, providing both the client and server support the
compressed protocol. Use the --local option to load from the
local file system.
 
Indexing
 
In cases where the storage engine supports ALTER TABLE...
DISABLE KEYS statements, the LOAD DATA INFILE statement
automatically disables indexes during the execution.
 


URL: https://mariadb.com/kb/en/load-data-infile/