Movatterモバイル変換

DELETE is a DML statement that removes rows from a table.

ADELETE statement can start with aWITH clause to define common table expressions accessible within theDELETE. SeeSection 15.2.20, “WITH (Common Table Expressions)”.

Single-Table Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROMtbl_name [[AS]tbl_alias]    [PARTITION (partition_name [,partition_name] ...)]    [WHEREwhere_condition]    [ORDER BY ...]    [LIMITrow_count]

TheDELETE statement deletes rows fromtbl_name and returns the number of deleted rows. To check the number of deleted rows, call theROW_COUNT() function described inSection 14.15, “Information Functions”.

Main Clauses

The conditions in the optionalWHERE clause identify which rows to delete. With noWHERE 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 inSection 15.2.13, “SELECT Statement”.

If theORDER BY clause is specified, the rows are deleted in the order that is specified. TheLIMIT 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[.*]] ...    FROMtable_references    [WHEREwhere_condition]DELETE [LOW_PRIORITY] [QUICK] [IGNORE]    FROMtbl_name[.*] [,tbl_name[.*]] ...    USINGtable_references    [WHEREwhere_condition]

Privileges

You need theDELETE privilege on a table to delete rows from it. You need only theSELECT privilege for any columns that are only read, such as those named in theWHERE clause.

Performance

When you do not need to know the number of deleted rows, theTRUNCATE TABLE statement is a faster way to empty a table than aDELETE statement with noWHERE clause. UnlikeDELETE,TRUNCATE TABLE cannot be used within a transaction or if you have a lock on the table. SeeSection 15.1.37, “TRUNCATE TABLE Statement” andSection 15.3.6, “LOCK TABLES and UNLOCK TABLES Statements”.

The speed of delete operations may also be affected by factors discussed inSection 10.2.5.3, “Optimizing DELETE Statements”.

To ensure that a givenDELETE statement does not take too much time, the MySQL-specificLIMITrow_count clause forDELETE specifies the maximum number of rows to be deleted. If the number of rows to delete is larger than the limit, repeat theDELETE statement until the number of affected rows is less than theLIMIT 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 thePARTITION 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 tablet with a partition namedp0, executing the statementDELETE FROM t PARTITION (p0) has the same effect on the table as executingALTER TABLE t TRUNCATE PARTITION (p0); in both cases, all rows in partitionp0 are dropped.

PARTITION can be used along with aWHERE 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 partitionp0 for which the conditionc < 5 is true; rows in any other partitions are not checked and thus not affected by theDELETE.

ThePARTITION clause can also be used in multiple-tableDELETE statements. You can use up to one such option per table named in theFROM option.

For more information and examples, seeSection 26.5, “Partition Selection”.

Auto-Increment Columns

If you delete the row containing the maximum value for anAUTO_INCREMENT column, the value is not reused for aMyISAM orInnoDB table. If you delete all rows in the table withDELETE FROMtbl_name (without aWHERE clause) inautocommit mode, the sequence starts over for all storage engines exceptInnoDB andMyISAM. There are some exceptions to this behavior forInnoDB tables, as discussed inSection 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.

ForMyISAM tables, you can specify anAUTO_INCREMENT secondary column in a multiple-column key. In this case, reuse of values deleted from the top of the sequence occurs even forMyISAM tables. SeeSection 5.6.9, “Using AUTO_INCREMENT”.

Modifiers

TheDELETE statement supports the following modifiers:

Order of Deletion

If theDELETE statement includes anORDER BY clause, rows are deleted in the order specified by the clause. This is useful primarily in conjunction withLIMIT. For example, the following statement finds rows matching theWHERE clause, sorts them bytimestamp_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 anInnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not useDELETE at all) might be helpful:

No other sessions can access the tables involved whileRENAME TABLE executes, so the rename operation is not subject to concurrency problems. SeeSection 15.1.36, “RENAME TABLE Statement”.

MyISAM Tables

InMyISAM tables, deleted rows are maintained in a linked list and subsequentINSERT operations reuse old row positions. To reclaim unused space and reduce file sizes, use theOPTIMIZE TABLE statement or themyisamchk utility to reorganize tables.OPTIMIZE TABLE is easier to use, butmyisamchk is faster. SeeSection 15.7.3.4, “OPTIMIZE TABLE Statement”, andSection 6.6.4, “myisamchk — MyISAM Table-Maintenance Utility”.

TheQUICK 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 ofQUICK can lead to wasted space in the index that remains unreclaimed. Here is an example of such a scenario:

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 ofQUICK. 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 useDELETE withoutQUICK, 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, useOPTIMIZE TABLE.

If you are going to delete many rows from a table, it might be faster to useDELETE QUICK followed byOPTIMIZE TABLE. This rebuilds the index rather than performing many index block merge operations.

Multi-Table Deletes

You can specify multiple tables in aDELETE statement to delete rows from one or more tables depending on the condition in theWHERE clause. You cannot useORDER BY orLIMIT in a multiple-tableDELETE. Thetable_references clause lists the tables involved in the join, as described inSection 15.2.13.2, “JOIN Clause”.

For the first multiple-table syntax, only matching rows from the tables listed before theFROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in theFROM clause (before theUSING 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 t3WHERE t1.id=t2.id AND t2.id=t3.id;

Or:

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3WHERE 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 tablest1 andt2.

The preceding examples useINNER JOIN, but multiple-tableDELETE statements can use other types of join permitted inSELECT statements, such asLEFT JOIN. For example, to delete rows that exist int1 that have no match int2, use aLEFT JOIN:

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

The syntax permits.* after eachtbl_name for compatibility withAccess.

If you use a multiple-tableDELETE statement involvingInnoDB 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 theON DELETE capabilities thatInnoDB provides to cause the other tables to be modified accordingly.

DELETE t1 FROM test AS t1, test2 WHERE ...

Table aliases in a multiple-tableDELETE should be declared only in thetable_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 a2WHERE a1.id=a2.id;DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2WHERE a1.id=a2.id;

Incorrect:

DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2WHERE a1.id=a2.id;DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2WHERE a1.id=a2.id;

Table aliases are also supported for single-tableDELETE statements beginning with MySQL 8.0.16. (Bug #89410,Bug #27455809)