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,...]
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.
LOAD DATA LOCAL INFILE
When you execute the LOAD DATA INFILE
statement, MariaDB Server attempts to read the input file from its own file system. In contrast, when you execute the LOAD DATA LOCAL INFILE
statement, the client attempts to read the input file from its file system, and it sends the contents of the input file to the MariaDB Server. This allows you to load files from the client's local file system into the database.
In the event that you don't want to permit this operation (such as for security reasons), you can disable the LOAD DATA LOCAL INFILE
statement on either the server or the client.
LOAD DATA LOCAL INFILE
statement can be disabled on the server by setting the local_infile
system variable to 0
. LOAD DATA LOCAL INFILE
statement can be disabled on the client. If you are using MariaDB Connector/C, this can be done by unsetting the CLIENT_LOCAL_FILES
capability flag with the mysql_real_connect
function or by unsetting the MYSQL_OPT_LOCAL_INFILE
option with mysql_optionsv
function. If you are using a different client or client library, then see the documentation for your specific client or client library to determine how it handles the LOAD DATA LOCAL INFILE
statement. If the LOAD DATA LOCAL INFILE
statement is disabled by either the server or the client and if the user attempts to execute it, then the server will cause the statement to fail with the following error message:
The used command is not allowed with this MariaDB version
Note that it is not entirely accurate to say that the MariaDB version does not support the command. It would be more accurate to say that the MariaDB configuration does not support the command. See MDEV-20500 for more information.
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.
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.
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.
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.
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.
In cases where the storage engine supports ALTER TABLE... DISABLE KEYS
statements, the LOAD DATA INFILE
statement automatically disables indexes during the execution.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/load-data-infile/