Managing & Using MySQL, 2nd Edition

Name

DELETE

Syntax

DELETE [LOW_PRIORITY | QUICK]
FROM table [WHERE clause] [ORDER BY column, ...]
[LIMIT n]

DELETE [LOW_PRIORITY | QUICK]
table1[.*], table2[.*], ..., tablen[.*]
FROM tablex, tabley, ..., tablez [WHERE clause]

DELETE [LOW_PRIORITY | QUICK]
FROM table1[.*], table2[.*], ..., tablen[.*]
USING references
[WHERE clause]

Description

Deletes rows from a table. When used without a WHERE clause, this will erase the entire table and recreate it as an empty table. With a WHERE clause, it will delete the rows that match the condition of the clause. This statement returns the number of rows deleted.

As mentioned above, omitting the WHERE clause will erase this entire table. This is done by using an efficient method that is much faster than deleting each row individually. When using this method, MySQL returns 0 to the user because it has no way of knowing how many rows it deleted. In the current design, this method simply deletes all the files associated with the table except for the file that contains the actual table definition. Therefore, this is a handy method of zeroing out tables with unrecoverably corrupt data files. You will lose the data, but the table structure will still be in place. If you really wish to get a full count of all deleted tables, use a WHERE clause with an expression that always evaluates to true:

DELETE FROM TBL WHERE 1 = 1;

The LOW_PRIORITY modifier causes MySQL to wait until no clients are reading from the table before executing the delete. QUICK causes ...