• 6 Справочник по языку MySQL
  • 6.4 Обработка данных: SELECT, INSERT, UPDATE, DELETE
    • 6.4.1 Синтаксис оператора SELECT
    • 6.4.2 Синтаксис оператора HANDLER
    • 6.4.3 Синтаксис оператора INSERT
    • 6.4.4 Синтаксис оператора INSERT DELAYED
    • 6.4.5 Синтаксис оператора UPDATE
    • 6.4.6 Синтаксис оператора DELETE
    • 6.4.7 Синтаксис оператора TRUNCATE
    • 6.4.8 Синтаксис оператора REPLACE
    • 6.4.9 Синтаксис оператора LOAD DATA INFILE
    • 6.4.10 Синтаксис оператора DO

Additional languages

• English
• French
• German
• Japanese
• Portuguese

Buy this Reference Manual in softcover from Barnes & Noble!

Additional formats

• HTML, one page per chapter, tarball
• HTML, all on one page, tarball
• HTML, all on one page, ZIP

User Comments

In these section you don't say nothing about the parent - child restrictions (on InnoDB tables), because you cannot delete a parent row only. You have to delete the parent, and child row

When you delete a large portion of a MyISAM table, it may be necessary to run an optimize table job afterwards. When the data file becomes fragmented, or when the key distribution data comes out of sync with reality, then you will see large performance drops. Also, disk space will not be freed until you've run optimize.

These are not really bugs, but limitations that you should be aware of when you start deleting large amounts of data from your tables.

If you would like to delete multiple rows, then use this easy example:

DELETE FROM `table` WHERE `id` IN (142,156,168)

This will delete rows with `id` 142, 156 and 168.

I spent an hour or so working out how to delete rows matching a specific SELECT statement which was mildly complex:

SELECT A.* FROM table1 AS A, table1 AS B
WHERE A.username LIKE '%2'
AND A.ID = B.ID
AND A.username <> B.username

(Basically, I had accidentally created two usernames for each ID, the extra username ending in 2. But there were some valid usernames ending in 2 which I didn't want to delete.)

I tried several different approaches to crafting a delete statement to get rid of these, all to no avail. I tried DELETE...WHERE IN...SELECT and DELETE...WHERE...= ANY...SELECT, WHERE EXISTS, and several other variations, all of which looked like they should work according to the manual, but none of which did.

Finally -- hence this comment, so you don't have to jump through my hoops -- my DBA wife and I put together this solution:

CREATE TEMPORARY TABLE tmptable
SELECT A.* FROM table1 AS A, table1 AS B
WHERE A.username LIKE '%2'
AND A.ID = B.ID
AND A.username <> B.username;

DELETE table1 FROM table1
INNER JOIN tmptable
ON table1.username = tmptable.username;

Maybe this isn't the best way to do this, but it worked for me. Hope it helps someone else.

It seems that you cannot use an alias for a table when doing a delete. For example, the following won't work:

DELETE a.* FROM table AS a, table AS b WHERE a.parent=b.child

However, simply changing the alias 'a' fixes this query:

DELETE table.* FROM table, table AS b WHERE table.parent=b.child

It now works as expected. Therefore, it's not the self-join that's the problem, but the use of an alias. This doesn't seem to be explained above.

> If you issue a DELETE with no WHERE clause, all rows are
> deleted. If you do this in AUTOCOMMIT mode, this works as
> TRUNCATE. See section 13.1.9 TRUNCATE Syntax.

Note, however, that TRUNCATE will not work if you have a lock on the table, because a TRUNCATE involves dropping and re-creating the table.

If you do a DELETE with no WHERE clause on a locked table, it seems that MySQL is intelligent enough to realise this, because it still works, even though the equivalent TRUNCATE would not because of the lock. In this case MySQL must not be translating it to a TRUNCATE.

>If an ORDER BY clause is used (available from MySQL 4.0.0), >the rows will be deleted in that order. This is really useful >only in conjunction with LIMIT.

Actually that is not the only time it is useful. For one thing, you can use this to make sure that the deletes happened in an order which preserves referential integrity.

If you have a table that has rows which are parents of other rows, and you know that you have created them in such a way that the parent rows are always created before the child rows, you could use the order by in your delete to get the child rows first.


mysql> delete from account where 1>0;
ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails
mysql> select * from account;

+----+-----------+------+
| id | parent_id | name |
+----+-----------+------+
|  1 |      NULL | mike |
|  2 |         1 | reid |
|  3 |         1 | cole |
+----+-----------+------+

Add your own comment.