Oracle 5.0, Справочное руководство

Страница: 1089 / 3232

INSERT
Syntax
1069
[ 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:
• 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.
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.9, “Replication and
LIMIT
” .
13.2.5.2.
INSERT DELAYED
Syntax
INSERT DELAYED ...
The
DELAYED
option for the
INSERT
statement is a MySQL extension to standard SQL that is very
useful if you have clients that cannot or need not wait for the
INSERT
to complete. This is a common
situation when you use MySQL for logging and you also periodically run
SELECT
and
UPDATE
statements that take a long time to complete.
When a client uses
INSERT DELAYED
, it gets an okay from the server at once, and the row is queued
to be inserted when the table is not in use by any other thread.
Another major benefit of using
INSERT DELAYED
is that inserts from many clients are bundled
together and written in one block. This is much faster than performing many separate inserts.
Note that
INSERT DELAYED
is slower than a normal
INSERT
if the table is not otherwise in use. There
is also the additional overhead for the server to handle a separate thread for each table for which there