DELETE
Syntax
1061
FROM
table_references
[WHERE
where_condition
]
Or:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM
tbl_name
[.*] [,
tbl_name
[.*]] ...
USING
table_references
[WHERE
where_condition
]
For the single-table syntax, the
DELETE
statement deletes rows from
tbl_name
and returns a count
of the number of deleted rows. This count can be obtained by calling the
ROW_COUNT()
[963]
function
(see
Section 12.13, “Information Functions”
). The
WHERE
clause, if given, specifies the conditions that
identify which rows to delete. With no
WHERE
clause, all rows are deleted. If the
ORDER BY
clause is
specified, the rows are deleted in the order that is specified. The
LIMIT
clause places a limit on the
number of rows that can be deleted.
For the multiple-table syntax,
DELETE
deletes from each
tbl_name
the rows that satisfy the
conditions. In this case,
ORDER BY
and
LIMIT
cannot be used.
where_condition
is an expression that evaluates to true for each row to be deleted. It is specified as
described in
Section 13.2.8, “
SELECT
Syntax”
.
Currently, you cannot delete from a table and select from the same table in a subquery.
You need the
DELETE
[577]
privilege on a table to delete rows from it. You need only the
SELECT
[578]
privilege for any columns that are only read, such as those named in the
WHERE
clause.
As stated, a
DELETE
statement with no
WHERE
clause deletes all rows. A faster way to do this, when
you do not need to know the number of deleted rows, is to use
TRUNCATE TABLE
. However, within
a transaction or if you have a lock on the table,
TRUNCATE TABLE
cannot be used whereas
DELETE
can. See
Section 13.1.21, “
TRUNCATE TABLE
Syntax”
, and
Section 13.3.5, “
LOCK TABLES
and
UNLOCK TABLES
Syntax”
.
If you delete the row containing the maximum value for an
AUTO_INCREMENT
column, the value
is reused later for a
BDB
table, but not for a
MyISAM
or
InnoDB
table. If you delete all rows in the
table with
DELETE FROM tbl_name
(without a
WHERE
clause) in
autocommit
[436]
mode, the
sequence starts over for all storage engines except
InnoDB
and
MyISAM
. There are some exceptions
to this behavior for
InnoDB
tables, as discussed in
Section 14.2.3.3, “
AUTO_INCREMENT
Handling in
InnoDB
”
.
For
MyISAM
and
BDB
tables, you can specify an
AUTO_INCREMENT
secondary column in a multiple-
column key. In this case, reuse of values deleted from the top of the sequence occurs even for
MyISAM
tables. See
Section 3.6.9, “Using
AUTO_INCREMENT
”
.
The
DELETE
statement supports the following modifiers:
• If you specify
LOW_PRIORITY
, the server delays execution of the
DELETE
until no other clients are
reading from the table. This affects only storage engines that use only table-level locking (such as
MyISAM
,
MEMORY
, and
MERGE
).
• For
MyISAM
tables, if you use the
QUICK
keyword, the storage engine does not merge index leaves
during delete, which may speed up some kinds of delete operations.
• The
IGNORE
keyword causes MySQL to ignore all errors during the process of deleting rows. (Errors
encountered during the parsing stage are processed in the usual manner.) Errors that are ignored
due to the use of
IGNORE
are returned as warnings.
The speed of delete operations may also be affected by factors discussed in
Section 8.3.2.3, “Speed of
DELETE
Statements”
.
In
MyISAM
tables, deleted rows are maintained in a linked list and subsequent
INSERT
operations
reuse old row positions. To reclaim unused space and reduce file sizes, use the
OPTIMIZE TABLE
Содержание 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 ...