DELETE
is a DML statement that
removes rows from a table.
A DELETE
statement can start with a
WITH
clause to define common table
expressions accessible within the
DELETE
. See Section 15.2.20, “WITH (Common Table Expressions)”.
Single-Table Syntax
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
The DELETE
statement deletes rows from
tbl_name
and returns the number of
deleted rows. To check the number of deleted rows, call the
ROW_COUNT()
function described in
Section 14.15, “Information Functions”.
Main Clauses
The conditions in the optional WHERE
clause
identify which rows to delete. With no WHERE
clause, all rows are deleted.
where_condition
is an expression that
evaluates to true for each row to be deleted. It is specified as
described in Section 15.2.13, “SELECT Statement”.
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. These clauses apply to single-table
deletes, but not multi-table deletes.
Multiple-Table Syntax
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*]] ...
USING table_references
[WHERE where_condition]
Privileges
You need the DELETE
privilege on a
table to delete rows from it. You need only the
SELECT
privilege for any columns
that are only read, such as those named in the
WHERE
clause.
Performance
When you do not need to know the number of deleted rows, the
TRUNCATE TABLE
statement is a
faster way to empty a table than a
DELETE
statement with no
WHERE
clause. Unlike
DELETE
,
TRUNCATE TABLE
cannot be used
within a transaction or if you have a lock on the table. See
Section 15.1.37, “TRUNCATE TABLE Statement” and
Section 15.3.6, “LOCK TABLES and UNLOCK TABLES Statements”.
The speed of delete operations may also be affected by factors discussed in Section 10.2.5.3, “Optimizing DELETE Statements”.
To ensure that a given DELETE
statement does not take too much time, the MySQL-specific
LIMIT
clause for row_count
DELETE
specifies the
maximum number of rows to be deleted. If the number of rows to
delete is larger than the limit, repeat the
DELETE
statement until the number of affected
rows is less than the LIMIT
value.
Subqueries
You cannot delete from a table and select from the same table in a subquery.
Partitioned Table Support
DELETE
supports explicit partition selection
using the PARTITION
clause, which takes a list
of the comma-separated names of one or more partitions or
subpartitions (or both) from which to select rows to be dropped.
Partitions not included in the list are ignored. Given a
partitioned table t
with a partition named
p0
, executing the statement DELETE
FROM t PARTITION (p0)
has the same effect on the table
as executing ALTER
TABLE t TRUNCATE PARTITION (p0)
; in both cases, all rows
in partition p0
are dropped.
PARTITION
can be used along with a
WHERE
condition, in which case the condition is
tested only on rows in the listed partitions. For example,
DELETE FROM t PARTITION (p0) WHERE c < 5
deletes rows only from partition p0
for which
the condition c < 5
is true; rows in any
other partitions are not checked and thus not affected by the
DELETE
.
The PARTITION
clause can also be used in
multiple-table DELETE
statements. You can use
up to one such option per table named in the
FROM
option.
For more information and examples, see Section 26.5, “Partition Selection”.
Auto-Increment Columns
If you delete the row containing the maximum value for an
AUTO_INCREMENT
column, the value is not reused
for a MyISAM
or InnoDB
table. If you delete all rows in the table with DELETE
FROM
(without a
tbl_name
WHERE
clause) in
autocommit
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 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.
For MyISAM
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 5.6.9, “Using AUTO_INCREMENT”.
Modifiers
The DELETE
statement supports the
following modifiers:
If you specify the
LOW_PRIORITY
modifier, the server delays execution of theDELETE
until no other clients are reading from the table. This affects only storage engines that use only table-level locking (such asMyISAM
,MEMORY
, andMERGE
).For
MyISAM
tables, if you use theQUICK
modifier, the storage engine does not merge index leaves during delete, which may speed up some kinds of delete operations.The
IGNORE
modifier causes MySQL to ignore ignorable 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 ofIGNORE
are returned as warnings. For more information, see The Effect of IGNORE on Statement Execution.
Order of Deletion
If the DELETE
statement includes an
ORDER BY
clause, rows are deleted in the order
specified by the clause. This is useful primarily in conjunction
with LIMIT
. For example, the following
statement finds rows matching the WHERE
clause,
sorts them by timestamp_column
, and deletes the
first (oldest) one:
DELETE FROM somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;
ORDER BY
also helps to delete rows in an order
required to avoid referential integrity violations.
InnoDB Tables
If you are deleting many rows from a large table, you may exceed
the lock table size for an InnoDB
table. To
avoid this problem, or simply to minimize the time that the table
remains locked, the following strategy (which does not use
DELETE
at all) might be helpful:
Select the rows not to be deleted into an empty table that has the same structure as the original table:
INSERT INTO t_copy SELECT * FROM t WHERE ... ;
Use
RENAME TABLE
to atomically move the original table out of the way and rename the copy to the original name:RENAME TABLE t TO t_old, t_copy TO t;
Drop the original table:
DROP TABLE t_old;
No other sessions can access the tables involved while
RENAME TABLE
executes, so the
rename operation is not subject to concurrency problems. See
Section 15.1.36, “RENAME TABLE Statement”.
MyISAM Tables
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
statement or the
myisamchk utility to reorganize tables.
OPTIMIZE TABLE
is easier to use,
but myisamchk is faster. See
Section 15.7.3.4, “OPTIMIZE TABLE Statement”, and Section 6.6.4, “myisamchk — MyISAM Table-Maintenance Utility”.
The QUICK
modifier affects whether index leaves
are merged for delete operations. DELETE QUICK
is most useful for applications where index values for deleted
rows are replaced by similar index values from rows inserted
later. In this case, the holes left by deleted values are reused.
DELETE QUICK
is not useful when deleted values
lead to underfilled index blocks spanning a range of index values
for which new inserts occur again. In this case, use of
QUICK
can lead to wasted space in the index
that remains unreclaimed. Here is an example of such a scenario:
Create a table that contains an indexed
AUTO_INCREMENT
column.Insert many rows into the table. Each insert results in an index value that is added to the high end of the index.
Delete a block of rows at the low end of the column range using
DELETE QUICK
.
In this scenario, the index blocks associated with the deleted
index values become underfilled but are not merged with other
index blocks due to the use of QUICK
. They
remain underfilled when new inserts occur, because new rows do not
have index values in the deleted range. Furthermore, they remain
underfilled even if you later use
DELETE
without
QUICK
, unless some of the deleted index values
happen to lie in index blocks within or adjacent to the
underfilled blocks. To reclaim unused index space under these
circumstances, use OPTIMIZE TABLE
.
If you are going to delete many rows from a table, it might be
faster to use DELETE QUICK
followed by
OPTIMIZE TABLE
. This rebuilds the
index rather than performing many index block merge operations.
Multi-Table Deletes
You can specify multiple tables in a
DELETE
statement to delete rows
from one or more tables depending on the condition in the
WHERE
clause. You cannot use ORDER
BY
or LIMIT
in a multiple-table
DELETE
. The
table_references
clause lists the
tables involved in the join, as described in
Section 15.2.13.2, “JOIN Clause”.
For the first multiple-table syntax, only matching rows from the
tables listed before the FROM
clause are
deleted. For the second multiple-table syntax, only matching rows
from the tables listed in the FROM
clause
(before the USING
clause) are deleted. The
effect is that you can delete rows from many tables at the same
time and have additional tables that are used only for searching:
DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
Or:
DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
These statements use all three tables when searching for rows to
delete, but delete matching rows only from tables
t1
and t2
.
The preceding examples use INNER JOIN
, but
multiple-table DELETE
statements
can use other types of join permitted in
SELECT
statements, such as
LEFT JOIN
. For example, to delete rows that
exist in t1
that have no match in
t2
, use a LEFT JOIN
:
DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
The syntax permits .*
after each
tbl_name
for compatibility with
Access.
If you use a multiple-table DELETE
statement involving InnoDB
tables for which
there are foreign key constraints, the MySQL optimizer might
process tables in an order that differs from that of their
parent/child relationship. In this case, the statement fails and
rolls back. Instead, you should delete from a single table and
rely on the ON DELETE
capabilities that
InnoDB
provides to cause the other tables to be
modified accordingly.
If you declare an alias for a table, you must use the alias when referring to the table:
DELETE t1 FROM test AS t1, test2 WHERE ...
Table aliases in a multiple-table
DELETE
should be declared only in
the table_references
part of the
statement. Elsewhere, alias references are permitted but not alias
declarations.
Correct:
DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;
DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;
Incorrect:
DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2
WHERE a1.id=a2.id;
DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2
WHERE a1.id=a2.id;
Table aliases are also supported for single-table
DELETE
statements beginning with MySQL 8.0.16.
(Bug #89410,Bug #27455809)