Rechercher dans le manuel MySQL
13.2.2 DELETE Syntax
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 13.2.13, “WITH Syntax (Common Table Expressions)”.
Single-Table Syntax
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 12.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 13.2.10, “SELECT Syntax”.
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
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 13.1.37, “TRUNCATE TABLE Syntax” and
Section 13.3.6, “LOCK TABLES and UNLOCK TABLES Syntax”.
The speed of delete operations may also be affected by factors discussed in Section 8.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 Tables
DELETE
supports explicit partition selection
using the PARTITION
option, 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
option 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 23.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 15.6.1.5, “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 3.6.9, “Using AUTO_INCREMENT”.
Modifiers
The DELETE
statement supports the
following modifiers:
If you specify
LOW_PRIORITY
, 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 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 Comparison of the IGNORE Keyword and Strict SQL Mode.
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:
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:
Use
RENAME TABLE
to atomically move the original table out of the way and rename the copy to the original name:Drop the original table:
No other sessions can access the tables involved while
RENAME TABLE
executes, so the
rename operation is not subject to concurrency problems. See
Section 13.1.36, “RENAME TABLE Syntax”.
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 13.7.3.4, “OPTIMIZE TABLE Syntax”, and Section 4.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 13.2.10.2, “JOIN Syntax”.
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:
Or:
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
:
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:
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:
Incorrect:
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-delete.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.