INSERT
Syntax
1068
If
INSERT
inserts a row into a table that has an
AUTO_INCREMENT
column, you can find the value
used for that column by using the SQL
LAST_INSERT_ID()
[961]
function. From within the C API,
use the
mysql_insert_id()
function. However, you should note that the two functions do not
always behave identically. The behavior of
INSERT
statements with respect to
AUTO_INCREMENT
columns is discussed further in
Section 12.13, “Information Functions”
, and
Section 20.6.6.37,
“
mysql_insert_id()
”
.
The
INSERT
statement supports the following modifiers:
• If you use the
DELAYED
keyword, the server puts the row or rows to be inserted into a buffer,
and the client issuing the
INSERT DELAYED
statement can then continue immediately. If the
table is in use, the server holds the rows. When the table is free, the server begins inserting rows,
checking periodically to see whether there are any new read requests for the table. If there are, the
delayed row queue is suspended until the table becomes free again. See
Section 13.2.5.2, “
INSERT
DELAYED
Syntax”
.
DELAYED
is ignored with
INSERT ... SELECT
or
INSERT ... ON DUPLICATE KEY UPDATE
.
Beginning with MySQL 5.0.42,
DELAYED
is also disregarded for an
INSERT
that uses functions
accessing tables or triggers, or that is called from a function or a trigger.
• If you use the
LOW_PRIORITY
keyword, execution of the
INSERT
is delayed until no other clients
are reading from the table. This includes other clients that began reading while existing clients are
reading, and while the
INSERT LOW_PRIORITY
statement is waiting. It is possible, therefore, for
a client that issues an
INSERT LOW_PRIORITY
statement to wait for a very long time (or even
forever) in a read-heavy environment. (This is in contrast to
INSERT DELAYED
, which lets the
client continue at once.) Note that
LOW_PRIORITY
should normally not be used with
MyISAM
tables
because doing so disables concurrent inserts. See
Section 8.7.3, “Concurrent Inserts”
.
If you specify
HIGH_PRIORITY
, it overrides the effect of the
--low-priority-updates
[414]
option if the server was started with that option. It also causes concurrent inserts not to be used. See
Section 8.7.3, “Concurrent Inserts”
.
LOW_PRIORITY
and
HIGH_PRIORITY
affect only storage engines that use only table-level locking
(such as
MyISAM
,
MEMORY
, and
MERGE
).
• If you use the
IGNORE
keyword, errors that occur while executing the
INSERT
statement are treated
as warnings instead. For example, without
IGNORE
, a row that duplicates an existing
UNIQUE
index
or
PRIMARY KEY
value in the table causes a duplicate-key error and the statement is aborted. With
IGNORE
, the row still is not inserted, but no error is issued. Data conversions that would trigger
errors abort the statement if
IGNORE
is not specified. With
IGNORE
, invalid values are adjusted to
the closest values and inserted; warnings are produced but the statement does not abort. You can
determine with the
mysql_info()
C API function how many rows were actually inserted into the
table.
• If you specify
ON DUPLICATE KEY UPDATE
, and a row is inserted that would cause a duplicate
value in a
UNIQUE
index or
PRIMARY KEY
, an
UPDATE
of the old row is performed. The affected-
rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated. See
Section 13.2.5.3, “
INSERT ... ON DUPLICATE KEY UPDATE
Syntax”
.
Inserting into a table requires the
INSERT
[577]
privilege for the table. If the
ON DUPLICATE KEY
UPDATE
clause is used and a duplicate key causes an
UPDATE
to be performed instead, the statement
requires the
UPDATE
[578]
privilege for the columns to be updated. For columns that are read but not
modified you need only the
SELECT
[578]
privilege (such as for a column referenced only on the right
hand side of an
col_name
=
expr
assignment in an
ON DUPLICATE KEY UPDATE
clause).
13.2.5.1.
INSERT ... SELECT
Syntax
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO]
tbl_name
[(
col_name
,...)]
SELECT ...
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
