GNU Info

(mysql.info)DELETE

---

Next: TRUNCATE Prev: UPDATE Up: Data Manipulation

---

Enter node , (file) or (file)node

`DELETE' Syntax
---------------

     DELETE [LOW_PRIORITY] FROM tbl_name
         [WHERE where_definition]
         [LIMIT rows]

`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' with no `WHERE' clause, all rows are deleted.
If you do this in `AUTOCOMMIT' mode, this works as `TRUNCATE'. Note:
TRUNCATE. One problem with this is that `DELETE' will return zero as
the number of affected records, but this will be fixed in 4.0.

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 of this form:

     mysql> DELETE FROM tbl_name WHERE 1>0;

Note that this is MUCH slower than `DELETE FROM tbl_name' with no
`WHERE' clause, because it deletes rows one at a time.

If you specify the keyword `LOW_PRIORITY', execution of the `DELETE' is
delayed until no other clients are reading from the table.

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 Note: `OPTIMIZE TABLE'.


The MySQL-specific `LIMIT rows' 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' command
doesn't take too much time.  You can simply repeat the `DELETE' command
until the number of affected rows is less than the `LIMIT' value.