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


INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]    [INTO] tbl_name    [PARTITION (partition_name,...)]    SET col_name={expr | DEFAULT}, ...    [ ON DUPLICATE KEY UPDATE      col_name=expr        [, col_name=expr] ... ]


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

INSERT inserts 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 Section, "INSERT ... SELECT Syntax".

When inserting into a partitioned table, you can control which partitions and subpartitions accept new rows. The PARTITION option takes a comma-separated list of the 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, the INSERT statement fails with the error Found a row not matching the given partition set. See Section 17.5, "Partition Selection", for more information and examples.

You can use REPLACE instead of INSERT to overwrite old rows. REPLACE is the counterpart to INSERT IGNORE in the treatment of new rows that contain unique key values that duplicate old rows: The new rows are used to replace the old rows rather than being discarded. See Section 13.2.8, "REPLACE Syntax".

tbl_name is the table into which rows should be inserted. The columns for which the statement provides values can be specified as follows:

Column values can be given in several ways:

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each 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);

The values list for each row must be enclosed within parentheses. The following statement is illegal because the number of values in the list does not match the number of column names:

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

VALUE is a synonym for VALUES in this context. Neither implies anything about the number of values lists, and either may be used whether there is a single values list or multiple lists.

The affected-rows value for an INSERT can be obtained using the ROW_COUNT() function (see Section 12.14, "Information Functions"), or the mysql_affected_rows() C API function (see Section, "mysql_affected_rows()").

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

Records: 100 Duplicates: 0 Warnings: 0

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:

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

If INSERT inserts a row into a table that has an AUTO_INCREMENT column, you can find the value used for that column by using the SQL LAST_INSERT_ID() function. From within the C API, use the mysql_insert_id() function. However, you should note that the two functions do not always behave identically. The behavior of INSERT statements with respect to AUTO_INCREMENT columns is discussed further in Section 12.14, "Information Functions", and Section, "mysql_insert_id()".

The INSERT statement supports the following modifiers:

Inserting into a table requires the INSERT 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 the UPDATE 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 col_name=expr assignment in an ON DUPLICATE KEY UPDATE clause).

In MySQL 5.7, an INSERT statement affecting a partitioned table using a storage engine such as MyISAM 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, see Section 17.6.4, "Partitioning and Locking".

Spec-Zone.ru - all specs in one place