Spec-Zone .ru
спецификации, руководства, описания, API
|
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO]tbl_name
[PARTITION (partition_name
,...)] [(col_name
,...)] SELECT ... [ ON DUPLICATE KEY UPDATEcol_name
=expr
, ... ]
With INSERT ... SELECT
, you can quickly insert many rows into a table from one or many
tables. For example:
INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
The following conditions hold for a INSERT
... SELECT
statements:
Specify IGNORE
to ignore rows that would cause
duplicate-key violations.
DELAYED
is ignored with INSERT ... SELECT
.
The target table of the INSERT
statement may appear in the FROM
clause of the SELECT
part of the query. (This was not possible in some older
versions of MySQL.) However, you cannot insert into a table and select from the same table in a
subquery.
When selecting from and inserting into a table at the same time, MySQL creates a temporary table to
hold the rows from the SELECT
and
then inserts those rows into the target table. However, it remains true that you cannot use INSERT INTO t ... SELECT ... FROM t
when t
is a TEMPORARY
table, because TEMPORARY
tables cannot be referred to twice in the same statement
(see Section C.5.7.2, "TEMPORARY
Table Problems").
AUTO_INCREMENT
columns work as usual.
To ensure that the binary log can be used to re-create the original tables, MySQL
does not permit concurrent inserts for INSERT ... SELECT
statements.
To avoid ambiguous column reference problems when the SELECT
and the INSERT
refer to the same table, provide a unique alias for each table used in the SELECT
part, and qualify column names in that part with the
appropriate alias.
Starting with MySQL 5.6.2, you can explicitly select which partitions or subpartitions (or both) of the source
or target table (or both) are to be used with a PARTITION
option following the name
of the table. When PARTITION
is used with the name of the source table in the SELECT
portion of the statement, rows are selected only from the partitions
or subpartitions named in its partition list. When PARTITION
is used with the name
of the target table for the INSERT
portion of the statement, then it must be possible to insert all rows
selected into the partitions or subpartitions named in the partition list following the option, else the INSERT ... SELECT
statement fails. For more information and examples, see Section
18.5, "Partition Selection".
In the values part of ON DUPLICATE KEY UPDATE
, you can refer to columns in other
tables, as long as you do not use GROUP BY
in the SELECT
part. One side effect is that you must qualify nonunique column names
in the values part.
The order in which rows are returned by a SELECT
statement with no ORDER BY
clause is not
determined. This means that, when using replication, there is no guarantee that such a SELECT
returns rows in the same order on the master and the slave; this can lead to inconsistencies between them. To
prevent this from occurring, you should always write INSERT ... SELECT
statements
that are to be replicated as INSERT ... SELECT ... ORDER BY
.
The choice of column
column
does not matter as long as the same order for
returning the rows is enforced on both the master and the slave. See also Section
16.4.1.16, "Replication and LIMIT
".
Due to this issue, beginning with MySQL 5.6.4, INSERT ... SELECT ON DUPLICATE KEY UPDATE
and INSERT IGNORE ... SELECT
statements are flagged as unsafe for statement-based
replication. With this change, such statements produce a warning in the log when using statement-based mode and
are logged using the row-based format when using MIXED
mode. (Bug #11758262, Bug
#50439)
See also Section 16.1.2.1, "Advantages and Disadvantages of Statement-Based and Row-Based Replication".
Prior to MySQL 5.6.6, an INSERT ... SELECT
statement that acted on partitioned
tables using a storage engine such as MyISAM
that employs table-level locks locked all partitions of the source and
target tables. (This did not and does not occur with tables using storage engines such as InnoDB
that employ row-level locking.) In MySQL 5.6.6 and later, only those
partitions of the source table that are actually read are locked, although all partitions of the target table
are locked. See Section 18.6.4, "Partitioning and Locking", for
more information.