Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Learn to insert and load data into MariaDB Server. This section covers INSERT and LOAD DATA SQL statements, enabling you to efficiently add new records to your databases.
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.
Understand concurrent inserts in MyISAM. This feature allows SELECT statements to run simultaneously with INSERT operations, reducing lock contention and improving performance.
The MyISAM storage engine supports concurrent inserts. This feature allows SELECT statements to be executed during INSERT operations, reducing contention.
Whether concurrent inserts can be used or not depends on the value of the concurrent_insert server system variable:
NEVER (0) disables concurrent inserts.
AUTO (1) allows concurrent inserts only when the target table has no free blocks (no data in the middle of the table has been deleted after the last ). This is the default.
ALWAYS (2) always enables concurrent inserts, in which case new rows are added at the end of a table if the table is being used by another thread.
If the is used, and statements cannot use concurrent inserts. These statements acquire a read lock on the table, so concurrent inserts will need to wait. This way, the log can be safely used to restore data.
Concurrent inserts are not used by replicas with the row-based (see ).
If an statement contains the clause, concurrent inserts cannot be used. is usually unneeded if concurrent inserts are enabled.
uses concurrent inserts if the CONCURRENT keyword is specified and is not NEVER. This makes the statement slower (even if no other sessions access the table) but reduces contention.
allows non-conflicting concurrent inserts if a READ LOCAL lock is used. Concurrent inserts are not allowed if the LOCAL keyword is omitted.
The decision to enable concurrent insert for a table is done when the table is opened. If you change the value of , it will only affect newly opened tables. If you want it to work for also for tables in use or cached, you should do after setting the variable.
This page is licensed: CC BY-SA / Gnu FDL
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
tbl_name can also be specified in the form db_name.tbl_name (see Identifier Qualifiers). This allows to copy rows between different databases.
If the new table has a primary key or UNIQUE indexes, you can use IGNORE to handle duplicate key errors during the query. The newer values will not be inserted if an identical value already exists.
REPLACE can be used instead of INSERT to prevent duplicates on UNIQUE indexes by deleting old values. In that case, ON DUPLICATE KEY UPDATE cannot be used.
INSERT ... SELECT works for tables which already exist. To create a table for a given resultset, you can use CREATE TABLE ... SELECT.
This page is licensed: GPLv2, originally from fill_help_tables.sql
Learn about the IGNORE keyword. This modifier suppresses certain errors during statement execution, downgrading them to warnings to allow the operation to proceed.
The IGNORE option tells the server to ignore some common errors.
IGNORE can be used with the following statements:
INSERT (see also INSERT IGNORE)
The logic used:
Variables out of ranges are replaced with the maximum/minimum value.
STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE are ignored.
Inserting NULL in a
The following errors are ignored:
Ignored errors normally generate a warning.
A property of the IGNORE clause consists in causing transactional engines and non-transactional engines (like InnoDB and Aria) to behave the same way. For example, normally a multi-row insert which tries to violate a UNIQUE contraint is completely rolled back on InnoDB, but might be partially executed on Aria. With the IGNORE clause, the statement will be partially executed in both engines.
Duplicate key errors also generate warnings. The server variable can be used to prevent this.
This page is licensed: CC BY-SA / Gnu FDL
Handle default and duplicate values during insertion. Learn how MariaDB manages missing columns and how to resolve duplicate key conflicts using various strategies.
If the SQL_MODE contains STRICT_TRANS_TABLES and you are inserting into a transactional table (like InnoDB), or if the SQL_MODE contains STRICT_ALL_TABLES, all NOT NULL columns which do not have a DEFAULT value (and are not AUTO_INCREMENT) must be explicitly referenced in INSERT statements. If not, an error like this is produced:
In all other cases, if a NOT NULL column without a DEFAULT value is not referenced, an empty value will be inserted (for example, 0 for INTEGER columns and '' for CHAR columns). See for examples.
If a NOT NULL column having a DEFAULT value is not referenced, NULL will be inserted.
If a NULL column having a DEFAULT value is not referenced, its default value will be inserted. It is also possible to explicitly assign the default value using the DEFAULT keyword or the function.
If the DEFAULT keyword is used but the column does not have a DEFAULT value, an error like this is produced:
By default, if you try to insert a duplicate row and there is a UNIQUE index, INSERT stops and an error like this is produced:
To handle duplicates you can use the clause, or the statement. Note that the IGNORE and DELAYED options are ignored when you use .
This page is licensed: CC BY-SA / Gnu FDL
Insert rows while ignoring specific errors. This statement allows bulk inserts to continue even if some rows fail due to duplicate keys or data conversion issues.
Normally stops and rolls back when it encounters an error.
By using the keyword all errors are converted to warnings, which will not stop inserts of additional rows.
Invalid values are changed to the closest valid value and inserted, with a warning produced.
The IGNORE and DELAYED options are ignored when you use .
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;NOT NULLRows that cause a duplicate key error or break a foreign key constraint are not inserted, updated, or deleted.
1264
ER_WARN_DATA_OUT_OF_RANGE
Out of range value for column '%s' at row %ld
1265
WARN_DATA_TRUNCATED
Data truncated for column '%s' at row %ld
1292
ER_TRUNCATED_WRONG_VALUE
Truncated incorrect %s value: '%s'
1366
ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
Incorrect integer value
1369
ER_VIEW_CHECK_FAILED
CHECK OPTION failed '%s.%s'
1451
ER_ROW_IS_REFERENCED_2
Cannot delete or update a parent row
1452
ER_NO_REFERENCED_ROW_2
Cannot add or update a child row: a foreign key constraint fails (%s)
1526
ER_NO_PARTITION_FOR_GIVEN_VALUE
Table has no partition for value %s
1586
ER_DUP_ENTRY_WITH_KEY_NAME
Duplicate entry '%s' for key '%s'
1591
ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT
Table has no partition for some existing values
1748
ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
Found a row not matching the given partition set
1022
ER_DUP_KEY
Can't write; duplicate key in table '%s'
1048
ER_BAD_NULL_ERROR
Column '%s' cannot be null
1062
ER_DUP_ENTRY
Duplicate entry '%s' for key %d
1242
ER_SUBQUERY_NO_1_ROW
Subquery returns more than 1 row
ERROR 1364 (HY000): Field 'col' doesn't have a default valueERROR 1364 (HY000): Field 'col' doesn't have a default valueERROR 1062 (23000): Duplicate entry 'dup_value' for key 'col'INSERT ... ON DUPLICATE KEY UPDATE (often called "upsert") is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE.
The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set.
If more than one unique index is matched, only the first is updated. It is not recommended to use this statement on tables with more than one unique index.
If the table has an AUTO_INCREMENT primary key and the statement inserts or updates a row, the LAST_INSERT_ID() function returns its AUTO_INCREMENT value.
The VALUES() function can only be used in a ON DUPLICATE KEY UPDATE clause and has no meaning in any other context. It returns the column values from the INSERT portion of the statement. This function is particularly useful for multi-rows inserts.
See Partition Pruning and Selection for details on the PARTITION clause.
This statement activates INSERT and UPDATE triggers. See Trigger Overview for details.
See also a similar statement, REPLACE.
If there is no existing key, the statement runs as a regular INSERT:
A regular INSERT with a primary key value of 1 will fail, due to the existing key:
However, we can use an INSERT ON DUPLICATE KEY UPDATE instead:
Note that there are two rows reported as affected, but this refers only to the UPDATE.
Adding a second unique column:
Where two rows match the unique keys match, only the first is updated. This can be unsafe and is not recommended unless you are certain what you are doing.
Although the third row with an id of 3 has an id2 of 13, which also matched, it was not updated.
Changing id to an auto_increment field. If a new row is added, the auto_increment is moved forward. If the row is updated, it remains the same.
Refering to column values from the INSERT portion of the statement:
See the VALUES() function for more.
This page is licensed: CC BY-SA / Gnu FDL
IGNORE. You can get the old behavior if you set OLD_MODE to NO_DUP_KEY_WARNINGS_WITH_IGNORE.No warnings are issued for duplicate key errors when using IGNORE.
See IGNORE for a full description of effects.
Converting values:
See INSERT ON DUPLICATE KEY UPDATE for further examples using that syntax.
This page is licensed: CC BY-SA / Gnu FDL
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
Queue inserts for later execution. This MyISAM-specific extension returns control immediately to the client while the server inserts rows when the table is free.
The DELAYED option for the statement is a MariaDB/MySQL extension to standard SQL that is very useful if you have clients that cannot or need not wait for the INSERT to complete. This is a common situation when you use MariaDB for logging and you also periodically run SELECT and UPDATE statements that take a long time to complete.
When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.
Another major benefit of using INSERT DELAYED is that inserts from many clients are bundled together and written in one block. This is much faster than performing many separate inserts.
Note that INSERT DELAYED is slower than a normalINSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should useINSERT DELAYED only when you are really sure that you need it.
The queued rows are held only in memory until they are inserted into the table. This means that if you terminate mariadbd forcibly (for example, with kill -9) or if mariadbd dies unexpectedly, any queued rows that have not been written to disk are lost.
The number of concurrent INSERT DELAYED threads is limited by the server system variables. If it is set to 0, INSERT DELAYED is disabled. The session value can be equal to the global value, or 0 to disable this statement for the current session. If this limit has been reached, the DELAYED clause will be silently ignore for subsequent statements (no error will be produced).
There are some limitations on the use of DELAYED:
INSERT DELAYED works only with , , ,
and tables. If you execute INSERT DELAYED with another storage engine, you will get an error like this: ERROR 1616 (HY000): DELAYED option not supported for table 'tab_name'
For MyISAM tables, if there are no free blocks in the middle of the data file, concurrent SELECT and INSERT statements are supported. Under these circumstances, you very seldom need to use INSERT DELAYED with MyISAM.
INSERT DELAYED
This page is licensed: GPLv2, originally from
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col=expr
[, col=expr] ... ]INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [PARTITION (partition_list)]
SET col={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col=expr
[, col=expr] ... ]INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col=expr
[, col=expr] ... ]CREATE TABLE ins_duplicate (id INT PRIMARY KEY, animal VARCHAR(30));
INSERT INTO ins_duplicate VALUES (1,'Aardvark'), (2,'Cheetah'), (3,'Zebra');INSERT INTO ins_duplicate VALUES (4,'Gorilla')
ON DUPLICATE KEY UPDATE animal='Gorilla';
Query OK, 1 row affected (0.07 sec)SELECT * FROM ins_duplicate;
+----+----------+
| id | animal |
+----+----------+
| 1 | Aardvark |
| 2 | Cheetah |
| 3 | Zebra |
| 4 | Gorilla |
+----+----------+INSERT INTO ins_duplicate VALUES (1,'Antelope');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'INSERT INTO ins_duplicate VALUES (1,'Antelope')
ON DUPLICATE KEY UPDATE animal='Antelope';
Query OK, 2 rows affected (0.09 sec)SELECT * FROM ins_duplicate;
+----+----------+
| id | animal |
+----+----------+
| 1 | Antelope |
| 2 | Cheetah |
| 3 | Zebra |
| 4 | Gorilla |
+----+----------+ALTER TABLE ins_duplicate ADD id2 INT;
UPDATE ins_duplicate SET id2=id+10;
ALTER TABLE ins_duplicate ADD UNIQUE KEY(id2);INSERT INTO ins_duplicate VALUES (2,'Lion',13)
ON DUPLICATE KEY UPDATE animal='Lion';
Query OK, 2 rows affected (0.004 sec)
SELECT * FROM ins_duplicate;
+----+----------+------+
| id | animal | id2 |
+----+----------+------+
| 1 | Antelope | 11 |
| 2 | Lion | 12 |
| 3 | Zebra | 13 |
| 4 | Gorilla | 14 |
+----+----------+------+ALTER TABLE `ins_duplicate` CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE ins_duplicate DROP id2;
SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='ins_duplicate';
+----------------+
| Auto_increment |
+----------------+
| 5 |
+----------------+
INSERT INTO ins_duplicate VALUES (2,'Leopard')
ON DUPLICATE KEY UPDATE animal='Leopard';
Query OK, 2 rows affected (0.00 sec)
SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='ins_duplicate';
+----------------+
| Auto_increment |
+----------------+
| 5 |
+----------------+
INSERT INTO ins_duplicate VALUES (5,'Wild Dog')
ON DUPLICATE KEY UPDATE animal='Wild Dog';
Query OK, 1 row affected (0.09 sec)
SELECT * FROM ins_duplicate;
+----+----------+
| id | animal |
+----+----------+
| 1 | Antelope |
| 2 | Leopard |
| 3 | Zebra |
| 4 | Gorilla |
| 5 | Wild Dog |
+----+----------+
SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='ins_duplicate';
+----------------+
| Auto_increment |
+----------------+
| 6 |
+----------------+INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);CREATE TABLE t1 (x INT UNIQUE);
INSERT INTO t1 VALUES(1),(2);
INSERT INTO t1 VALUES(2),(3);
ERROR 1062 (23000): Duplicate entry '2' for key 'x'
SELECT * FROM t1;
+------+
| x |
+------+
| 1 |
| 2 |
+------+
INSERT IGNORE INTO t1 VALUES(2),(3);
Query OK, 1 row affected, 1 warning (0.04 sec)
SHOW WARNINGS;
+---------+------+---------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------+
| Warning | 1062 | Duplicate entry '2' for key 'x' |
+---------+------+---------------------------------+
SELECT * FROM t1;
+------+
| x |
+------+
| 1 |
| 2 |
| 3 |
+------+CREATE OR REPLACE TABLE t2(id INT, t VARCHAR(2) NOT NULL, n INT NOT NULL);
INSERT INTO t2(id) VALUES (1),(2);
ERROR 1364 (HY000): Field 't' doesn't have a default value
INSERT IGNORE INTO t2(id) VALUES (1),(2);
Query OK, 2 rows affected, 2 warnings (0.026 sec)
Records: 2 Duplicates: 0 Warnings: 2
SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1364 | Field 't' doesn't have a default value |
| Warning | 1364 | Field 'n' doesn't have a default value |
+---------+------+----------------------------------------+
SELECT * FROM t2;
+------+---+---+
| id | t | n |
+------+---+---+
| 1 | | 0 |
| 2 | | 0 |
+------+---+---+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,...]INSERT DELAYED ...INSERTDELAYEDINSERT ... SELECTINSERT ... ON DUPLICATE KEY UPDATEBecause the INSERT DELAYED statement returns immediately, before the rows are inserted, you cannot useLAST_INSERT_ID() to get theAUTO_INCREMENT value that the statement might generate.
DELAYED rows are not visible to SELECT statements until they actually have been inserted.
After INSERT DELAYED, ROW_COUNT() returns the number of the rows you tried to insert, not the number of the successful writes.
DELAYED is ignored on slave replication servers, so thatINSERT DELAYED is treated as a normal INSERT on slaves. This is becauseDELAYED could cause the slave to have different data than
the master. INSERT DELAYED statements are not safe for replication.
Pending INSERT DELAYED statements are lost if a table is write locked and ALTER TABLE is used to modify the table structure.
INSERT DELAYED is not supported for views. If you try, you will get an error like this: ERROR 1347 (HY000): 'view_name' is not BASE TABLE
INSERT DELAYED is not supported for partitioned tables.
INSERT DELAYED is not supported within stored programs.
INSERT DELAYED does not work with triggers.
INSERT DELAYED does not work if there is a check constraint in place.
INSERT DELAYED does not work if skip-new mode is active.
CHARACTER SET
(column_or_user_var,...)
SET
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>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 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 triggers.
One must have the FILE 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 secure_file_priv system variable is set (by default it is not), the loaded file must be present in the specified directory.
Note that MariaDB's systemd unit file restricts access to /home, /root, and /run/user by default. See Configuring access to home directories.
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 local_infile 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 statement.
The LOAD DATA LOCAL INFILE strict modes like STRICT_TRANS_TABLES are disabled with keyword "local". ()
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 MDEV-20500 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 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. If 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.
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 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 mariadb-dump -T or with the SELECT INTO OUTFILE statement with the mariadb 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.
col_name_or_user_var can be a column name, or a user variable. In the case of a variable, the SET statement can be used to preprocess the value before loading into the table.
In storage engines that perform table-level locking (MyISAM, MEMORY and MERGE), using the LOW_PRIORITY keyword, MariaDB delays insertions until no other clients are reading from the table. Alternatively, when using the MyISAM storage engine, 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.
The LOAD DATA INFILE statement supports . 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.
MariaDB ships with a separate utility for loading data from files: mariadb-import. 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 mariadb-import 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 (MyISAM and Aria), 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 fill_help_tables.sql
INSERT ... RETURNING returns a resultset of the inserted rows.
It returns the listed columns for all the rows that are inserted, or alternatively, the specified SELECT expression. Any SQL expressions which can be calculated can be used in the select expression for the RETURNING clause, including virtual columns and aliases, expressions which use various operators such as bitwise, logical and arithmetic operators, string functions, date-time functions, numeric functions, control flow functions, secondary functions and stored functions. Along with this, statements which have subqueries and prepared statements can also be used.
Simple INSERT statements:
Using stored functions in RETURNING:
Subqueries in the RETURNING clause that return more than one row or column cannot be used.
Aggregate functions cannot be used in the RETURNING clause. Since aggregate functions work on a set of values, and if the purpose is to get the row count, ROW_COUNT()with SELECT can be used or it can be used in INSERT...SELECT...RETURNING if the table in the RETURNING clause is not the same as the INSERT table.
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,...]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 |
+------+------+INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col=expr
[, col=expr] ... ] [RETURNING select_expr
[, select_expr ...]]INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [PARTITION (partition_list)]
SET col={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col=expr
[, col=expr] ... ] [RETURNING select_expr
[, select_expr ...]]INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col=expr
[, col=expr] ... ] [RETURNING select_expr
[, select_expr ...]]CREATE OR REPLACE TABLE t2 (id INT, animal VARCHAR(20), t TIMESTAMP);
INSERT INTO t2 (id) VALUES (2),(3) RETURNING id,t;
+------+---------------------+
| id | t |
+------+---------------------+
| 2 | 2021-04-28 00:59:32 |
| 3 | 2021-04-28 00:59:32 |
+------+---------------------+INSERT INTO t2(id,animal) VALUES (1,'Dog'),(2,'Lion'),(3,'Tiger'),(4,'Leopard')
RETURNING id,id+id,id&id,id||id;
+------+-------+-------+--------+
| id | id+id | id&id | id||id |
+------+-------+-------+--------+
| 1 | 2 | 1 | 1 |
| 2 | 4 | 2 | 1 |
| 3 | 6 | 3 | 1 |
| 4 | 8 | 4 | 1 |
+------+-------+-------+--------+DELIMITER |
CREATE FUNCTION f(arg INT) RETURNS INT
BEGIN
RETURN (SELECT arg+arg);
END|
DELIMITER ;
PREPARE stmt FROM "INSERT INTO t1 SET id1=1, animal1='Bear' RETURNING f(id1), UPPER(animal1)";
EXECUTE stmt;
+---------+----------------+
| f(id1) | UPPER(animal1) |
+---------+----------------+
| 2 | BEAR |
+---------+----------------+Add new rows to a table. This fundamental SQL command inserts explicit values or query results into a database table, supporting various modifiers for concurrency.
Or:
Or:
The INSERT statement is used to insert new rows into an existing table. The INSERT ... VALUES
and INSERT ... SET forms of the statement insert rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables. INSERT ... SELECT is discussed further in the article.
The table name can be specified in the form db_name.tbl_name or, if a default database is selected, in the form tbl_name (see ). This allows to use to copy rows between different databases.
The PARTITION clause can be used in both the INSERT and the SELECT part. See for details.
The RETURNING clause can be used.
The RETURNING clause is not available.
The columns list is optional. It specifies which values are explicitly inserted, and in which order. If this clause is not specified, all values must be explicitly specified, in the same order they are listed in the table definition.
The list of value follow the VALUES or VALUE keyword (which are interchangeable, regardless how much values you want to insert), and is wrapped by parenthesis. The values must be listed in the same order as the columns list. It is possible to specify more than one list to insert more than one rows with a single statement. If many rows are inserted, this is a speed optimization.
For one-row statements, the SET clause may be more simple, because you don't need to remember the columns order. All values are specified in the form col = expr.
Values can also be specified in the form of a SQL expression or subquery. However, the subquery cannot access the same table that is named in the INTO clause.
If you use the LOW_PRIORITY keyword, execution of the INSERT is delayed until no other clients are reading from the table. If you use the HIGH_PRIORITY keyword, the statement has the same priority as SELECTs. This affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE). However, if one of these keywords is specified, cannot be used. See for details.
For more details on the DELAYED option, see .
See .
See for details..
See .
See .
Specifying the column names:
Inserting more than 1 row at a time:
Using the SET clause:
SELECTing from another table:
See and for further examples.
INSERT ... RETURNING returns a result set of the inserted rows.
It returns the listed columns for all the rows that are inserted, or alternatively, the specified SELECT expression. Any SQL expressions which can be calculated can be used in the select expression for the RETURNING clause, including virtual columns and aliases, expressions which use various operators such as bitwise, logical and arithmetic operators, string functions, date-time functions, numeric functions, control flow functions, secondary functions and stored functions. Along with this, statements which have subqueries and prepared statements can also be used.
Simple INSERT statement:
Using stored functions in RETURNING
Subqueries in the RETURNING clause that return more than one row or column cannot be used.
Aggregate functions cannot be used in the RETURNING clause. Since aggregate functions work on a set of values, and if the purpose is to get the row count, ROW_COUNT() with SELECT can be used or it can be used in INSERT...SELECT...RETURNING if the table in the RETURNING clause is not the same as the INSERT table.
Equivalent to DELETE + INSERT of conflicting row.
This page is licensed: GPLv2, originally from
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col=expr
[, col=expr] ... ] [RETURNING select_expr
[, select_expr ...]]INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [PARTITION (partition_list)]
SET col={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col=expr
[, col=expr] ... ] [RETURNING select_expr
[, select_expr ...]]INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col=expr
[, col=expr] ... ] [RETURNING select_expr
[, select_expr ...]]INSERT INTO person (first_name, last_name) VALUES ('John', 'Doe');INSERT INTO tbl_name VALUES (1, "row 1"), (2, "row 2");INSERT INTO person SET first_name = 'John', last_name = 'Doe';INSERT INTO contractor SELECT * FROM person WHERE status = 'c';INSERT INTO t2 VALUES (1,'Dog'),(2,'Lion'),(3,'Tiger'),(4,'Leopard')
RETURNING id2,id2+id2,id2&id2,id2||id2;
+-----+---------+---------+----------+
| id2 | id2+id2 | id2&id2 | id2||id2 |
+-----+---------+---------+----------+
| 1 | 2 | 1 | 1 |
| 2 | 4 | 2 | 1 |
| 3 | 6 | 3 | 1 |
| 4 | 8 | 4 | 1 |
+-----+---------+---------+----------+DELIMITER |
CREATE FUNCTION f(arg INT) RETURNS INT
BEGIN
RETURN (SELECT arg+arg);
END|
DELIMITER ;
PREPARE stmt FROM "INSERT INTO t1 SET id1=1, animal1='Bear' RETURNING f(id1), UPPER(animal1)";
EXECUTE stmt;
+---------+----------------+
| f(id1) | UPPER(animal1) |
+---------+----------------+
| 2 | BEAR |
+---------+----------------+