13.2.5.1 INSERT ... SELECT Syntax

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name 
    [PARTITION (partition_name,...)]
    [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_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:

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 column. The choice of 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.