MySQL :: MySQL 5.7 Reference Manual :: 13.2.5 INSERT Statement (original) (raw)

13.2.5 INSERT Statement

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

INSERT inserts new rows into an existing table. The INSERT ... VALUES andINSERT ... 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 with anON DUPLICATE KEY UPDATE clause enables existing rows to be updated if a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY.

For additional information aboutINSERT ... SELECT andINSERT ... ON DUPLICATE KEY UPDATE, seeSection 13.2.5.1, “INSERT ... SELECT Statement”, andSection 13.2.5.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”.

In MySQL 5.7, the DELAYED keyword is accepted but ignored by the server. For the reasons for this, see Section 13.2.5.3, “INSERT DELAYED Statement”,

Inserting into a table requires theINSERT privilege for the table. If the ON DUPLICATE KEY UPDATE clause is used and a duplicate key causes an UPDATE to be performed instead, the statement requires theUPDATE privilege for the columns to be updated. For columns that are read but not modified you need only the SELECT privilege (such as for a column referenced only on the right hand side of an_colname=expr_ assignment in an ON DUPLICATE KEY UPDATE clause).

When inserting into a partitioned table, you can control which partitions and subpartitions accept new rows. ThePARTITION clause takes a list of the comma-separated names of one or more partitions or subpartitions (or both) of the table. If any of the rows to be inserted by a given INSERT statement do not match one of the partitions listed, theINSERT statement fails with the error Found a row not matching the given partition set. For more information and examples, seeSection 22.5, “Partition Selection”.

tblname is the table into which rows should be inserted. Specify the columns for which the statement provides values as follows:

Column values can be given in several ways:

INSERT INTO tbl_name () VALUES();

If strict mode is not enabled, MySQL uses the implicit default value for any column that has no explicitly defined default. If strict mode is enabled, an error occurs if any column has no default value.

INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);

But the following is not legal, because the value forcol1 refers to col2, which is assigned after col1: 

INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);

An exception occurs for columns that containAUTO_INCREMENT values. BecauseAUTO_INCREMENT values are generated after other value assignments, any reference to anAUTO_INCREMENT column in the assignment returns a 0.

INSERT statements that useVALUES syntax can insert multiple rows. To do this, include multiple lists of comma-separated column values, with lists enclosed within parentheses and separated by commas. Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Each values list must contain exactly as many values as are to be inserted per row. The following statement is invalid because it contains one list of nine values, rather than three lists of three values each:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);

VALUE is a synonym forVALUES in this context. Neither implies anything about the number of values lists, nor about the number of values per list. Either may be used whether there is a single values list or multiple lists, and regardless of the number of values per list.

The affected-rows value for anINSERT can be obtained using theROW_COUNT() SQL function or themysql_affected_rows() C API function. See Section 12.15, “Information Functions”, andmysql_affected_rows().

If you use an INSERT ... VALUES statement with multiple value lists orINSERT ... SELECT, the statement returns an information string in this format:

Records: N1 Duplicates: N2 Warnings: N3

If you are using the C API, the information string can be obtained by invoking the mysql_info() function. See mysql_info().

Records indicates the number of rows processed by the statement. (This is not necessarily the number of rows actually inserted because Duplicates can be nonzero.) Duplicates indicates the number of rows that could not be inserted because they would duplicate some existing unique index value. Warnings indicates the number of attempts to insert column values that were problematic in some way. Warnings can occur under any of the following conditions:

The INSERT statement supports the following modifiers:

An INSERT statement affecting a partitioned table using a storage engine such asMyISAM that employs table-level locks locks only those partitions into which rows are actually inserted. (For storage engines such as InnoDB that employ row-level locking, no locking of partitions takes place.) For more information, seeSection 22.6.4, “Partitioning and Locking”.