Preload table indexes into the key cache. This command, used for MyISAM tables, loads index blocks into memory to warm up the cache and improve subsequent query performance.
LOAD INDEX INTO CACHE
tbl_index_list [, tbl_index_list] ...
tbl_index_list:
tbl_name
[[INDEX|KEY] (index_name[, index_name] ...)]
[IGNORE LEAVES]The LOAD INDEX INTO CACHE statement preloads a table index into the key cache to which it has been assigned by an explicit statement, or into the default key cache otherwise.LOAD INDEX INTO CACHE is used only for or tables.
The IGNORE LEAVES modifier causes only blocks for the nonleaf nodes of the index to be preloaded.
This page is licensed: GPLv2, originally from
Read rows from a text file into a table. This high-speed data loading command parses structured text files and inserts records, often much faster than INSERT statements.
LOAD DATA INFILE is for statement-based replication.
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 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 and by default fields are expected to be terminated with tabs () and lines with newlines (). These clauses are optional, but if both are specified then the FIELDS clause must precede LINES.
Executing this statement activates INSERT .
One must have the privilege to be able to execute LOAD DATA INFILE. This is to ensure normal users cannot read system files. LOAD DATA LOCAL INFILE does not have this requirement.
If the system variable is set (by default it is not), the loaded file must be present in the specified directory.
Note that MariaDB's unit file restricts access to /home, /root, and /run/user by default. See .
When you execute the LOAD DATA INFILE statement, MariaDB Server attempts to read the input file from its own file system. By 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.
If you don't want to permit this operation (perhaps for security reasons), you can disable the LOAD DATA LOCAL INFILE statement on either the server or the client.
The LOAD DATA LOCAL INFILE statement can be disabled on the server by setting the system variable to 0.
The LOAD DATA LOCAL INFILE statement can be disabled on the client. If you are using , this can be done by unsetting the CLIENT_LOCAL_FILES capability flag with the function or by unsetting the MYSQL_OPT_LOCAL_INFILE option with 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
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:
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 for more information.
From , the error message is more accurate:
If you load data from a file into a table that already contains data and has a , 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. If you want MariaDB to overwrite duplicates, use the REPLACE keyword.
The REPLACE keyword works like the 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 . 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.
IGNORE number {LINES|ROWS}The IGNORE number LINES syntax can be used to ignore a number of rows from the beginning of the file. Most often this is needed when the file starts with one row that includes the column headings.
When the statement opens the file, it attempts to read the contents using the default character-set, as defined by the 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 statement and by the 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 or with the statement with the 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 and LOAD DATA INFILE to ensure that MariaDB correctly interprets the escape sequences.
The system variable controls the interpretation of the filename.
It is currently not possible to load data files that use the ucs2 character set.
col_name_or_user_var can be a column name, or a user variable. In the case of a variable, the statement can be used to preprocess the value before loading into the table.
In storage engines that perform table-level locking (, and ), using the keyword, MariaDB delays insertions until no other clients are reading from the table. Alternatively, when using the storage engine, you can use the keyword to perform concurrent insertion.
The LOW_PRIORITY and CONCURRENT keywords are mutually exclusive. They cannot be used in the same statement.
The LOAD DATA INFILE statement supports . You may find this useful when dealing with long-running operations. Using another client you can issue a query to check the progress of the data load.
MariaDB ships with a separate utility for loading data from files: . It operates by sending LOAD DATA INFILE statements to the server.
MariaDB ships with a separate utility for loading data from files: mysqlimport . It operates by sending LOAD DATA INFILE statements to the server.
Using 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 statements ( and ), the LOAD DATA INFILE statement automatically disables indexes during the execution.
You have a file with this content (note the separator is ',', not tab, which is the default):
Another example, given the following data (the separator is a tab):
The value of the first column is doubled before loading:
This page is licensed: GPLv2, originally from
Read data from an XML file into a table. This command parses XML content, mapping elements and attributes to table columns for direct data import.
The LOAD XML statement reads data from an XML file into a table. Thefile_name must be given as a literal string. The tagname in the optional ROWS IDENTIFIED BY clause must also be given as a literal
string, and must be surrounded by angle brackets (< and >).
LOAD XML acts as the complement of running the in XML output mode (that is, starting the client with the --xml option). To write data from a table to an XML file, use a command such as the following one from the system shell:
To read the file back into a table, use LOAD XML INFILE. By default, the element is considered to be the equivalent of a database table row; this can be changed using the ROWS IDENTIFIED BY clause.
This statement supports three different XML formats:
Column names as attributes and column values as attribute values:
Column names as tags and column values as the content of these tags:
Column names are the name attributes of tags, and values are the contents of these tags:
This is the format used by other tools, such as .
All 3 formats can be used in the same XML file; the import routine automatically detects the format for each row and interprets it correctly. Tags are matched based on the tag or attribute name and the column name.
The following clauses work essentially the same way for LOAD XML as they do for LOAD DATA:
LOW_PRIORITY or CONCURRENT
LOCAL
REPLACE or IGNORE
See for more information about these clauses.
The IGNORE number LINES or IGNORE number ROWS clause causes the first number rows in the XML file to be skipped. It is analogous to the LOAD DATA statement's IGNORE ... LINES clause.
If the keyword is used, insertions are delayed until no other clients are reading from the table. The CONCURRENT keyword allows the use of . These clauses cannot be specified together.
This statement activates INSERT .
The storage engine has an .
This page is licensed: CC BY-SA / Gnu FDL
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|ROWS}]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]LOAD XML [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE [db_name.]tbl_name
[CHARACTER SET charset_name]
[ROWS IDENTIFIED BY '<tagname>']
[IGNORE number {LINES | ROWS}]
[(column_or_user_var,...)]
[SET col_name = expr,...]The LOAD DATA LOCAL INFILE strict modes like STRICT_TRANS_TABLES are disabled with keyword "local". (MDEV-11235)
CHARACTER SET
(column_or_user_var,...)
SET
The used command is not allowed with this MariaDB versionThe used command is not allowed because the MariaDB server or client
has disabled the local infile capability2,2
3,3
4,4
5,5
6,8CREATE TABLE t1 (a INT, b INT, c INT, d INT, PRIMARY KEY (a));
LOAD DATA LOCAL INFILE
'/tmp/loaddata7.dat' INTO TABLE t1 FIELDS TERMINATED BY ',' (a,b) SET c=a+b;
SELECT * FROM t1;
+------+------+------+
| a | b | c |
+------+------+------+
| 2 | 2 | 4 |
| 3 | 3 | 6 |
| 4 | 4 | 8 |
| 5 | 5 | 10 |
| 6 | 8 | 14 |
+------+------+------+1 a
2 bLOAD DATA INFILE 'ld.txt' INTO TABLE ld (@i,v) SET i=@i*2;
SELECT * FROM ld;
+------+------+
| i | v |
+------+------+
| 2 | a |
| 4 | b |
+------+------+shell> mariadb --xml -e 'SELECT * FROM mytable' > file.xml<row column1="value1" column2="value2" .../><row>
<column1>value1</column1>
<column2>value2</column2>
</row><row>
<field name='column1'>value1</field>
<field name='column2'>value2</field>
</row>Bulk load data efficiently. This section covers commands like LOAD DATA INFILE and LOAD XML for high-speed data import from text or XML files.