13.2.5.1 INSERT ... SELECT 语句_MySQL 5.7 参考手册

MySQL 8.0 参考手册 / 第 13 章 SQL 语句 / 13.2 数据操作语句 / 13.2.6 插入语句 / 13.2.5.1 INSERT ... SELECT 语句

13.2.5.1 INSERT ... SELECT 语句

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}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

使用INSERT ... SELECT，您可以从一个语句的结果中快速地将许多行插入到一个表中，该SELECT 语句可以从一个或多个表中进行选择。例如：

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

以下条件适用于 INSERT ... SELECT语句：

指定IGNORE忽略会导致重复键违规的行。
语句的目标表 INSERT可能出现在查询部分的FROM子句中。SELECT但是，您不能在子查询中插入表并从同一个表中进行选择。
当从同一个表中进行选择和插入时，MySQL 创建一个内部临时表来保存来自的行SELECT，然后将这些行插入到目标表中。但是，您不能使用 INSERT INTO t ... SELECT ... FROM twhen tis a TEMPORARY table，因为TEMPORARY不能在同一语句中引用两次表。请参阅第 8.4.4 节，“MySQL 中的内部临时表使用”和第 B.3.6.2 节，“临时表问题”。
AUTO_INCREMENT专栏照常工作。
为确保二进制日志可用于重新创建原始表，MySQL 不允许对INSERT ... SELECT语句进行并发插入（请参阅第 8.11.3 节，“并发插入”）。
为避免在 SELECT和 INSERT引用同一个表时出现不明确的列引用问题，请为该部分中使用的每个表提供唯一的别名 SELECT，并使用适当的别名限定该部分中的列名。

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 clause 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 22.5, “Partition Selection”.

For INSERT ... SELECT statements, see Section 13.2.5.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement” 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 source and the replica, 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 source and the replica. See also Section 16.4.1.17, “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)

使用表级锁INSERT ... SELECT的存储引擎影响分区表的语句会锁定目标表的所有分区；MyISAM但是，只有那些实际从源表中读取的分区才会被锁定。（这不会发生在使用存储引擎的表中，例如InnoDB使用行级锁定的表。）有关更多信息，请参阅第 22.6.4 节，“分区和锁定”。