Rechercher dans le manuel MySQL
13.2.6.1 INSERT ... SELECT Syntax
With INSERT ...
SELECT
, you can quickly insert many rows into a table
from the result of a SELECT
statement, which can select from one or many tables. For
example:
- SELECT tbl_temp1.fld_order_id
The following conditions hold for
INSERT ...
SELECT
statements:
Specify
IGNORE
to ignore rows that would cause duplicate-key violations.The target table of the
INSERT
statement may appear in theFROM
clause of theSELECT
part of the query. However, you cannot insert into a table and select from the same table in a subquery.When selecting from and inserting into the same table, MySQL creates an internal temporary table to hold the rows from the
SELECT
and then inserts those rows into the target table. However, you cannot useINSERT INTO t ... SELECT ... FROM t
whent
is aTEMPORARY
table, becauseTEMPORARY
tables cannot be referred to twice in the same statement. See Section 8.4.4, “Internal Temporary Table Use in MySQL”, and Section B.4.6.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 (see Section 8.11.3, “Concurrent Inserts”).To avoid ambiguous column reference problems when the
SELECT
and theINSERT
refer to the same table, provide a unique alias for each table used in theSELECT
part, and qualify column names in that part with the appropriate alias.
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,
it must be possible to insert all rows selected into the
partitions or subpartitions named in the partition list
following the option. Otherwise, the INSERT ...
SELECT
statement fails. For more information and
examples, see Section 23.5, “Partition Selection”.
For INSERT
... SELECT
statements, see
Section 13.2.6.2, “INSERT ... ON DUPLICATE KEY UPDATE Syntax” for conditions under which
the SELECT
columns can be
referred to in an ON DUPLICATE KEY UPDATE
clause.
The order in which a SELECT
statement with no ORDER BY
clause returns
rows is nondeterministic. 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, which can lead to
inconsistencies between them. To prevent this from occurring,
always write INSERT ... SELECT
statements
that are to be replicated using an ORDER BY
clause that produces the same row order on the master and the
slave. See also Section 17.4.1.18, “Replication and LIMIT”.
Due to this issue,
INSERT ...
SELECT ON DUPLICATE KEY UPDATE
and
INSERT IGNORE ...
SELECT
statements are flagged as unsafe for
statement-based replication. Such statements produce a warning
in the error log when using statement-based mode and are written
to the binary log using the row-based format when using
MIXED
mode. (Bug #11758262, Bug #50439)
See also Section 17.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.
An INSERT ... SELECT
statement affecting
partitioned tables using a storage engine such as
MyISAM
that employs table-level
locks locks all partitions of the target table; however, only
those partitions that are actually read from the source table
are locked. (This does not occur with tables using storage
engines such as InnoDB
that employ
row-level locking.) For more information, see
Partitioning and Locking.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-insert-select.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.