MySQL 8.0-13.2.6.1 INSERT ... SELECT Statement

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

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

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时，您可以从SELECT语句的结果快速地将许多行插入到表中，该语句可以从一个或多个表中进行选择。例如:

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

Beginning with MySQL 8.0.19, you can use a TABLE statement in place of SELECT, as shown here:

从MySQL 8.0.19开始，你可以使用TABLE语句来代替SELECT，如下所示:

INSERT INTO ta TABLE tb;

TABLE tb is equivalent to SELECT * FROM tb. It can be useful when inserting all columns from the source table into the target table, and no filtering with WHERE is required. In addition, the rows from TABLE can be ordered by one or more columns using ORDER BY, and the number of rows inserted can be limited using a LIMIT clause. For more information, see Section 13.2.12, “TABLE Statement”.

表tb等同于SELECT * FROM tb。当将源表中的所有列插入到目标表中，并且不需要使用WHERE进行过滤时，它会很有用。此外，TABLE中的行可以使用ORDER by按一个或多个列排序，插入的行数可以使用LIMIT子句限制。

The following conditions hold for INSERT ... SELECT statements, and, except where noted, for INSERT ... TABLE as well:

以下条件适用于INSERT…SELECT语句，以及INSERT…TABLE :

• Specify IGNORE to ignore rows that would cause duplicate-key violations.

• 指定IGNORE以忽略可能导致重复键冲突的行。

• The target table of the INSERT statement may appear in the FROM clause of the SELECT part of the query, or as the table named by TABLE. However, you cannot insert into a table and select from the same table in a subquery. INSERT语句的目标表可能出现在查询的SELECT部分的FROM子句中，或者作为table命名的表。但是，不能在子查询中插入表并从同一表中进行选择。

  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 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. For the same reason, you cannot use INSERT INTO t ... TABLE t when t is a temporary table. See Section 8.4.4, “Internal Temporary Table Use in MySQL”, and Section B.3.6.2, “TEMPORARY Table Problems”. 当从同一个表中选择并插入时，MySQL创建一个内部临时表来保存SELECT中的行，然后将这些行插入到目标表中。然而，您不能使用INSERT INTO t…选择……当t是一个临时表时，从t开始，因为临时表不能在同一语句中被引用两次。出于同样的原因，您不能使用INSERT INTO t…当t是一个临时表时。

• AUTO_INCREMENT columns work as usual.

• AUTO_INCREMENT列照常工作。

• To ensure that the binary log can be used to re-create the original tables, MySQL does not permit concurrent inserts for INSERT ... SELECT or INSERT ... TABLE statements (see Section 8.11.3, “Concurrent Inserts”).

• 为了确保二进制日志可以用来重新创建原始表，MySQL不允许并发插入IINSERT ... SELECT或INSERT ... TABLE声明

• 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. 为了避免在SELECT和INSERT引用同一个表时出现不明确的列引用问题，请为SELECT部分中使用的每个表提供唯一的别名，并使用适当的别名限定该部分中的列名。

  The TABLE statement does not support aliases. TABLE语句不支持别名。

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

您可以显式地选择源表或目标表(或两者)的哪个分区或子分区，并在表名后面使用PARTITION子句。当在语句的SELECT部分中将PARTITION与源表的名称一起使用时，仅从其分区列表中命名的分区或子分区中选择行。当PARTITION与目标表的名称一起用于语句的INSERT部分时，必须能够将选择的所有行插入到分区列表中按照该选项命名的分区或子分区中。否则，INSERT…SELECT语句失败。欲了解更多信息和例子，

TABLE does not support a PARTITION clause.

For INSERT ... SELECT statements, see Section 13.2.6.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. This also works for INSERT ... TABLE.

对于插入……SELECT语句，请参见13.2.6.2节“INSERT…”ON DUPLICATE KEY UPDATE语句，用于在此条件下可以在ON DUPLICATE KEY UPDATE子句中引用SELECT列。这也适用于INSERT…表格

The order in which a SELECT or TABLE 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 or INSERT ... TABLE 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 17.5.1.18, “Replication and LIMIT”.

没有order BY子句的SELECT或TABLE语句返回行的顺序是不确定的。这意味着，当使用复制时，不能保证这样的SELECT在源和副本上以相同的顺序返回行，这可能会导致它们之间的不一致。为了防止这种情况发生，总是写入INSERT…SELECT 或INSERT ... TABLE要使用ORDER BY子句复制的语句，该子句在源和副本上生成相同的行顺序。

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 ON DUPLICATE KEY UPDATE and INSERT IGNORE…对于基于语句的复制，SELECT语句被标记为不安全。当使用基于语句的模式时，这些语句会在错误日志中产生警告，当使用MIXED模式时，这些语句会使用基于行的格式写入二进制日志。(Bug #11758262, Bug #50439)

See also Section 17.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.