(mysql.info.gz) DELETE
Info Catalog
(mysql.info.gz) Data Manipulation
(mysql.info.gz) Data Manipulation
(mysql.info.gz) DO
13.1.1 `DELETE' Syntax
----------------------
Single-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM TBL_NAME
[WHERE WHERE_DEFINITION]
[ORDER BY ...]
[LIMIT ROW_COUNT]
Multiple-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
TBL_NAME[.*] [, TBL_NAME[.*] ...]
FROM TABLE_REFERENCES
[WHERE WHERE_DEFINITION]
Or:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM TBL_NAME[.*] [, TBL_NAME[.*] ...]
USING TABLE_REFERENCES
[WHERE WHERE_DEFINITION]
`DELETE' deletes rows from TBL_NAME that satisfy the condition given by
WHERE_DEFINITION, and returns the number of records deleted.
If you issue a `DELETE' statement with no `WHERE' clause, all rows are
deleted. A faster way to do this, when you don't want to know the
number of deleted rows, is to use `TRUNCATE TABLE'. `TRUNCATE'
TRUNCATE.
In MySQL 3.23, `DELETE' without a `WHERE' clause returns zero as the
number of affected records.
In MySQL 3.23, if you really want to know how many records are deleted
when you are deleting all rows, and are willing to suffer a speed
penalty, you can use a `DELETE' statement that includes a `WHERE'
clause with an expression that is true for every row. For example:
mysql> DELETE FROM TBL_NAME WHERE 1>0;
This is much slower than `TRUNCATE TBL_NAME', because it deletes rows
one at a time.
If you delete the row containing the maximum value for an
`AUTO_INCREMENT' column, the value will be reused for an `ISAM' or
`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')
in `AUTOCOMMIT' mode, the sequence starts over for all table types
except for `InnoDB' and (as of MySQL 4.0) `MyISAM'. There are some
exceptions to this behavior for `InnoDB' tables, discussed in
`InnoDB' auto-increment column InnoDB auto-increment column.
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. example-`AUTO_INCREMENT' example-AUTO_INCREMENT.
The `DELETE' statement supports the following modifiers:
* If you specify the `LOW_PRIORITY' keyword, execution of the
`DELETE' is delayed until no other clients are reading from the
table.
* For `MyISAM' tables, if you specify the `QUICK' keyword, the
storage engine does not merge index leaves during delete, which
may speed up certain kind of deletes.
* 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 this option are returned as warnings. This
option first appeared in MySQL 4.1.1.
The speed of delete operations may also be affected by factors
discussed in Delete speed.
In `MyISAM' tables, deleted records are maintained in a linked list and
subsequent `INSERT' operations reuse old record 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, but `myisamchk' is faster. See `OPTIMIZE TABLE'
OPTIMIZE TABLE. and Optimization.
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 will be replaced by similar index values from
rows inserted later. In this case, the holes left by deleted values
will be reused.
`DELETE QUICK' is not useful when deleted values lead to underfilled
index blocks spanning a range of index values for which new inserts will
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:
1. Create a table that contains an indexed `AUTO_INCREMENT' column.
2. Insert many records into the table. Each insert results in an
index values that is added to the high end of the index.
3. Delete a block of records 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 will remain underfilled when new inserts
occur, because new records will not have index values in the deleted
range. Furthermore, they will 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, you can 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.
The MySQL-specific `LIMIT ROW_COUNT' option to `DELETE' tells the
server the maximum number of rows to be deleted before control is
returned to the client. This can be used to ensure that a specific
`DELETE' statement doesn't take too much time. You can simply repeat
the `DELETE' statement until the number of affected rows is less than
the `LIMIT' value.
If the `DELETE' statement includes an `ORDER BY' clause, the rows are
deleted in the order specified by the clause. This is really useful only
in conjunction with `LIMIT'. For example, the following statement
finds rows matching the `WHERE' clause, sorts them in `timestamp'
order, and deletes the first (oldest) one:
DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp
LIMIT 1
`ORDER BY' can be used with `DELETE' beginning with MySQL 4.0.0.
From MySQL 4.0, you can specify multiple tables in the `DELETE'
statement to delete rows from one or more tables depending on a
particular condition in multiple tables. However, you cannot use `ORDER
BY' or `LIMIT' in a multiple-table `DELETE'.
The first multiple-table `DELETE' syntax is supported starting from
MySQL 4.0.0. The second is supported starting from MySQL 4.0.2. The
`table_references' part lists the tables involved in the join. Its
syntax is described in `JOIN' JOIN.
For the first syntax, only matching rows from the tables listed before
the `FROM' clause are deleted. For the second 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 also have additional tables that are used for
searching:
DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
Or:
DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
These statements use all three files when searching for rows to delete,
but delete matching rows only from tables `t1' and `t2'.
The examples show inner joins using the comma operator, but
multiple-table `DELETE' statements can use any type of join allowed in
`SELECT' statements, such as `LEFT JOIN'.
The syntax allows `.*' after the table names 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, delete from a single table and rely on the `ON DELETE'
capabilities that `InnoDB' provides to cause the other tables to be
modified accordingly.
* In MySQL 4.0, you should refer to the table names to be deleted
with the true table name. In MySQL 4.1, you must use the alias (if one
was given) when referring to a table name:
In MySQL 4.0:
DELETE test FROM test AS t1, test2 WHERE ...
In MySQL 4.1:
DELETE t1 FROM test AS t1, test2 WHERE ...
The reason we didn't make this change in 4.0 is that we didn't want to
break any old 4.0 applications that were using the old syntax.
Currently, you cannot delete from a table and select from the same
table in a subquery.
Info Catalog
(mysql.info.gz) Data Manipulation
(mysql.info.gz) Data Manipulation
(mysql.info.gz) DO
automatically generated byinfo2html