INSERT
Syntax
1070
are delayed rows. This means that you should use
INSERT DELAYED
only when you are really sure
that you need it.
The queued rows are held only in memory until they are inserted into the table. This means that if you
terminate
mysqld
forcibly (for example, with
kill -9
) or if
mysqld
dies unexpectedly, any queued
rows that have not been written to disk are lost.
There are some constraints on the use of
DELAYED
:
•
INSERT DELAYED
works only with
MyISAM
,
MEMORY
, and
ARCHIVE
tables. For engines that do not
support
DELAYED
, an error occurs.
• An error occurs for
INSERT DELAYED
if used with a table that has been locked with
LOCK TABLES
because the insert must be handled by a separate thread, not by the session that holds the lock.
• For
MyISAM
tables, if there are no free blocks in the middle of the data file, concurrent
SELECT
and
INSERT
statements are supported. Under these circumstances, you very seldom need to use
INSERT DELAYED
with
MyISAM
.
•
INSERT DELAYED
should be used only for
INSERT
statements that specify value lists. The server
ignores
DELAYED
for
INSERT ... SELECT
or
INSERT ... ON DUPLICATE KEY UPDATE
statements.
• Because the
INSERT DELAYED
statement returns immediately, before the rows are inserted, you
cannot use
LAST_INSERT_ID()
[961]
to get the
AUTO_INCREMENT
value that the statement might
generate.
•
DELAYED
rows are not visible to
SELECT
statements until they actually have been inserted.
•
INSERT DELAYED
is treated as a normal
INSERT
if the statement inserts multiple rows and binary
logging is enabled.
•
DELAYED
is ignored on slave replication servers, so that
INSERT DELAYED
is treated as a normal
INSERT
on slaves. This is because
DELAYED
could cause the slave to have different data than the
master.
• Pending
INSERT DELAYED
statements are lost if a table is write locked and
ALTER TABLE
is used
to modify the table structure.
•
INSERT DELAYED
is not supported for views.
The following describes in detail what happens when you use the
DELAYED
option to
INSERT
or
REPLACE
. In this description, the “thread” is the thread that received an
INSERT DELAYED
statement
and “handler” is the thread that handles all
INSERT DELAYED
statements for a particular table.
• When a thread executes a
DELAYED
statement for a table, a handler thread is created to process all
DELAYED
statements for the table, if no such handler already exists.
• The thread checks whether the handler has previously acquired a
DELAYED
lock; if not, it tells the
handler thread to do so. The
DELAYED
lock can be obtained even if other threads have a
READ
or
WRITE
lock on the table. However, the handler waits for all
ALTER TABLE
locks or
FLUSH TABLES
statements to finish, to ensure that the table structure is up to date.
• The thread executes the
INSERT
statement, but instead of writing the row to the table, it puts a copy
of the final row into a queue that is managed by the handler thread. Any syntax errors are noticed by
the thread and reported to the client program.
• The client cannot obtain from the server the number of duplicate rows or the
AUTO_INCREMENT
value for the resulting row, because the
INSERT
returns before the insert operation has been
completed. (If you use the C API, the
mysql_info()
function does not return anything meaningful,
for the same reason.)
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...