Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.1Mb
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


13.2.2 DELETE 構文

DELETE は、テーブルの行を削除する DML ステートメントです。

単一テーブル構文

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name,...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

DELETE ステートメントは、tbl_name の行を削除し、削除された行数を返します。削除された行数をチェックするには、セクション12.14「情報関数」で説明されているROW_COUNT() 関数を呼び出します。

メインの句

オプションの WHERE 句内の条件は、どの行を削除するかを識別します。WHERE 句がない場合は、すべての行が削除されます。

where_condition は、削除される各行に対して true に評価される式です。これは、セクション13.2.9「SELECT 構文」で説明されているように指定されます。

ORDER BY 句が指定されている場合は、指定されている順序で行が削除されます。LIMIT 句は、削除できる行数に制限を設定します。これらの句は単一テーブルの削除に適用されますが、複数テーブルの削除には適用されません。

複数テーブル構文

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

または:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

権限

テーブルから行を削除するには、そのテーブルに対する DELETE 権限が必要です。WHERE 句で指定されているカラムなどの、読み取られるだけのカラムに対しては、SELECT 権限のみが必要です。

パフォーマンス

削除された行数を知る必要がない場合、テーブルを空にするには、WHERE 句のない DELETE ステートメントより TRUNCATE TABLE ステートメントの方が高速です。DELETE とは異なり、TRUNCATE TABLE はトランザクション内で、またはそのテーブルがロックされている場合は使用できません。セクション13.1.33「TRUNCATE TABLE 構文」およびセクション13.3.5「LOCK TABLES および UNLOCK TABLES 構文」を参照してください。

削除操作の速度はまた、セクション8.2.2.3「DELETE ステートメントの速度」で説明されている要因によって影響を受ける可能性もあります。

特定の DELETE ステートメントに時間がかかりすぎないようにするために、DELETE の MySQL 固有の LIMIT row_count 句は、削除される行の最大数を指定します。削除する行数がこの制限を超えている場合は、影響を受ける行数が LIMIT 値を下回るまで DELETE ステートメントを繰り返します。

サブクエリー

現在、テーブルから削除し、さらにサブクエリーで同じテーブルから選択することはできません。

パーティション化されたテーブル

MySQL 5.6.2 から、DELETE は、削除される行を選択する 1 つ以上のパーティションまたはサブパーティション (またはその両方) の名前のカンマ区切りリストを含む PARTITION オプションを使用した明示的なパーティション選択をサポートしています。このリストに含まれていないパーティションは無視されます。p0 という名前のパーティションを含むパーティション化されたテーブル t がある場合、ステートメント DELETE FROM t PARTITION (p0) の実行には、このテーブルに対して ALTER TABLE t TRUNCATE PARTITION (p0) を実行するのと同じ効果があります。どちらの場合も、パーティション p0 内のすべての行が削除されます。

PARTITION は、WHERE 条件とともに使用できます。その場合、この条件は、リストされているパーティション内の行に対してのみテストされます。たとえば、DELETE FROM t PARTITION (p0) WHERE c < 5 は、条件 c < 5 が true であるパーティション p0 の行のみを削除します。ほかのパーティション内の行はチェックされないため、DELETE によって影響を受けません。

PARTITION オプションはまた、複数テーブルの DELETE ステートメントでも使用できます。このようなオプションを、FROM オプションで指定されているテーブルごとに最大 1 つ使用できます。

詳細および例については、セクション19.5「パーティション選択」を参照してください。

自動インクリメントカラム

AUTO_INCREMENT カラムに最大値を含む行を削除した場合、その値は、MyISAM または InnoDB テーブルには再利用されません。autocommit モードで DELETE FROM tbl_name (WHERE 句はなし) を使用してテーブル内のすべての行を削除した場合、そのシーケンスは、InnoDBMyISAM を除くすべてのストレージエンジンに対して開始されます。セクション14.6.5「InnoDB での AUTO_INCREMENT 処理」で説明されているように、InnoDB テーブルに対しては、この動作の例外がいくつかあります。

MyISAM テーブルの場合は、マルチカラムキー内の AUTO_INCREMENT セカンダリカラムを指定できます。この場合は、シーケンスの先頭から削除された値の再利用が MyISAM テーブルに対しても実行されます。セクション3.6.9「AUTO_INCREMENT の使用」を参照してください。

修飾子

DELETE ステートメントは、次の修飾子をサポートします。

  • LOW_PRIORITY を指定した場合、サーバーは、ほかのどのクライアントもそのテーブルから読み取らなくなるまで DELETE の実行を遅延させます。これは、テーブルレベルロックのみを使用するストレージエンジン (MyISAMMEMORY、および MERGE) にのみ影響を与えます。

  • MyISAM テーブルでは、QUICK キーワードを使用した場合、ストレージエンジンは削除中にインデックスリーフをマージしません。これにより、一部の種類の削除操作が高速化される可能性があります。

  • IGNORE キーワードを指定すると、MySQL は行削除プロセス中のエラーを無視します。(解析の段階で検出されたエラーは、通常の方法で処理されます。)IGNORE の使用のために無視されたエラーは、警告として返されます。

削除の順序

DELETE ステートメントに ORDER BY 句が含まれている場合は、この句で指定されている順序で行が削除されます。これは、主に LIMIT と組み合わせて使用した場合に有効です。たとえば、次のステートメントは WHERE 句に一致する行を見つけ、それらを timestamp_column でソートしたあと、最初の (もっとも古い) 行を削除します。

DELETE FROM somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;

ORDER BY はまた、参照整合性の違反を回避するために必要な順序で行を削除する場合も役立ちます。

InnoDB テーブル

大きなテーブルから多数の行を削除する場合は、InnoDB テーブルに対するロックテーブルのサイズを超える可能性があります。この問題を回避するために、または単にテーブルがロックされたままになる時間を最小限に抑えるために、DELETE をまったく使用しない次の方法が有効な場合があります。

  1. 削除されない行を選択して、元のテーブルと同じ構造を持つ空のテーブルに格納します。

    INSERT INTO t_copy SELECT * FROM t WHERE ... ;
    
  2. RENAME TABLE を使用して元のテーブルを原子的に移動したあと、コピーの名前を元の名前に変更します。

    RENAME TABLE t TO t_old, t_copy TO t;
    
  3. 元のテーブルを削除します。

    DROP TABLE t_old;
    

RENAME TABLE が実行されている間、関連するテーブルにはほかのどのセッションからもアクセスできないため、名前変更の操作は並列性の問題に制約されません。セクション13.1.32「RENAME TABLE 構文」を参照してください。

MyISAM テーブル

MyISAM テーブルでは、削除された行はリンクリスト内に保持され、以降の INSERT 操作は古い行の位置を再利用します。未使用領域を再利用し、ファイルサイズを減らすには、OPTIMIZE TABLE ステートメントまたは myisamchk ユーティリティーを使用してテーブルを再編成します。OPTIMIZE TABLE の方が使い方は簡単ですが、myisamchk の方が高速です。セクション13.7.2.4「OPTIMIZE TABLE 構文」およびセクション4.6.3「myisamchk — MyISAM テーブルメンテナンスユーティリティー」を参照してください。

QUICK 修飾子は、削除操作でインデックスリーフがマージされるかどうかに影響を与えます。DELETE QUICK は、削除された行のインデックス値が、あとで挿入された行の同様のインデックス値に置き換えられるアプリケーションで、特に役立ちます。この場合、削除された値によって残された穴は再利用されます。

DELETE QUICK は、削除された値によって、新しい挿入が再度発生するインデックス値の範囲全体にわたって空きのあるインデックスブロックが残される場合には役立ちません。この場合は、QUICK を使用すると、再利用されないままのインデックスで領域が浪費される可能性があります。このようなシナリオの例を次に示します。

  1. インデックス付き AUTO_INCREMENT カラムを含むテーブルを作成します。

  2. このテーブルに多数の行を挿入します。各挿入によって、インデックスの先頭に追加されるインデックス値が生成されます。

  3. DELETE QUICK を使用して、カラムの範囲の最後にある行のブロックを削除します。

このシナリオでは、削除されたインデックス値に関連付けられたインデックスブロックに空きができますが、QUICK が使用されているため、ほかのインデックスブロックにはマージされません。新しい挿入が発生したとき、新しい行には削除された範囲内のインデックス値が含まれていないため、これらのインデックスブロックは空きがあるままになります。さらに、削除された一部のインデックス値が偶然に空きのあるブロック内か、またはその隣のインデックスブロックに含まれていないかぎり、あとで QUICK なしで DELETE を使用した場合でも空きがあるままになります。これらの状況で未使用のインデックス領域を再利用するには、OPTIMIZE TABLE を使用します。

テーブルから多数の行を削除しようとしている場合は、DELETE QUICK に続けて OPTIMIZE TABLE を使用した方が高速になることがあります。これにより、インデックスブロックの多数のマージ操作が実行されるのではなく、インデックスが再構築されます。

複数テーブルの削除

WHERE 句内の条件に応じて 1 つ以上のテーブルから行を削除するには、DELETE ステートメントで複数のテーブルを指定できます。複数テーブルの DELETE では、ORDER BY または LIMIT を使用できません。セクション13.2.9.2「JOIN 構文」で説明されているように、table_references 句は、結合に含まれるテーブルをリストします。

最初の複数テーブル構文では、FROM 句の前にリストされているテーブルの一致する行のみが削除されます。2 番目の複数テーブル構文では、USING 句の前にある FROM 句にリストされているテーブルの一致する行のみが削除されます。その効果は、多数のテーブルの行を同時に削除し、さらに検索にのみ使用される追加のテーブルを指定できることです。

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

または:

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

これらのステートメントは、削除する行を検索するときに 3 つのすべてのテーブルを使用しますが、テーブル t1t2 の一致する行のみを削除します。

前の例では INNER JOIN を使用していますが、複数テーブルの DELETE ステートメントは、SELECT ステートメント内で許可されているほかの型の結合 (LEFT JOIN など) を使用できます。たとえば、t1 内に存在する行で t2 内に一致するものがない行を削除するには、LEFT JOIN を使用します。

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

この構文では、Access との互換性のために、各 tbl_name のあとに .* が許可されます。

外部キー制約が存在する InnoDB テーブルを含む、複数テーブルの DELETE ステートメントを使用した場合は、MySQL オプティマイザが、それらの親子関係の順序とは異なる順序でテーブルを処理する可能性があります。この場合、このステートメントは失敗し、ロールバックされます。代わりに、1 つのテーブルから削除したあと、InnoDB が提供する ON DELETE 機能を使用して、ほかのテーブルがそれに応じて変更されるようにしてください。

注記

テーブルのエイリアスを宣言した場合は、テーブルを参照するときにそのエイリアスを使用する必要があります。

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

複数テーブルの DELETE 内のテーブルエイリアスは、そのステートメントの table_references 部分でのみ宣言するようにしてください。それ以外の場所では、エイリアス参照が許可されますが、エイリアス宣言は許可されません。

正しい:

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

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

正しくない:

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

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

User Comments
  Posted by Chris Rywalt on January 29, 2004
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.
  Posted by Kevin Nelson on April 18, 2008
- Deleting Duplicate Entries -

I had a many-to-many relational table that joined users and events. Some users might save the same event more than once...so I wanted to know a way to delete duplicate entries. The table has a primary key "ueventID" (auto-increment) and two foreign keys "userID" and "eventID". In order to delete duplicate entries, I found that this solution worked quite well for me.

DELETE t1 FROM tbl_name t1, tbl_name t2 WHERE t1.userID=t2.userID AND t1.eventID=t2.eventID AND t1.ueventID < t2.ueventID

This will delete all but the very last entry of the duplicates. If there are any better ways to do this, feel free to let me know. I'll try to remember to check back later.

Honestly, though, while I wanted to know how to do this...officially, I just check to see if it's a duplicate entry BEFORE I insert it so that I don't have to hassle with this :-P
  Posted by Linus Lövholm on September 1, 2005
Regarding deleting duplicate entries:

I have found two other much more robust ways of doing this, which will accomplish the task even for rows that are complete duplicates.

1) SELECT DISTINCT INTO ...
Perform a select distinct into a new table. Drop the old table. Rename the new table if you want to.

2) Use ALTER IGNORE TABLE and add an index for the duplicate column(s). Given this table (without primary key):
+---+
| a |
+---+
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
+---+
Do this:

ALTER IGNORE TABLE table1 ADD PRIMARY KEY(a);

Naturally, you can use a UNIQUE index instead of a primary key.
  Posted by on December 1, 2005
While it is documented in these pages, it takes a bit of hunting to confirm this incompatible change in v3.23 to v4.1:

If you delete all rows from a table with DELETE FROM tablename, then add some new rows with INSERT INTO tablename, an AUTO_INCREMENT field would start again from 1 using MySQL v3.23.

However, with MyISAM tables with MySQL v4.1, the auto increment counter isn't reset back to 1 - even if you do OPTIMIZE tablename. You have to do TRUNCATE tablename to delete all rows in order to reset the auto increment counter.

This can cause problems because your auto increment counter gets higher and higher each time you do a DELETE all/INSERT new data cycle.
  Posted by Radek Maciaszek on August 21, 2006
It's probably worth to mention that DELETE FROM doesn't use the same isolation level in transaction as SELECT. Even if you set isolation level as REPEATABLE READ it doesn't change DELETE behaviour which work as READ COMMITTED. (it affects InnoDB engine in MySQL4 and MySQL5)

Here is an example:

|               User A                 User B
|
| SET AUTOCOMMIT=0; SET AUTOCOMMIT=0;
|
| SELECT * FROM t;
| empty set
| INSERT INTO t VALUES (1, 2);
|
| SELECT * FROM t;
| empty set
| COMMIT;
|
| SELECT * FROM t;
| empty set
|
| SELECT * FROM t;
| ---------------------
| | 1 | 2 |
| ---------------------
| 1 row in set
|
| DELETE FROM t;
| Query OK,
| 1 row affected
|// ^ it delets rows from
|// outside it's transaction
|
| COMMIT;
|
| SELECT * FROM t;
| empty set

  Posted by Luciano Fantuzzi on April 16, 2007
Keywords: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails...

I think this is a good practice to do when you're designing a database that has lots of foreign keys. If you have tables with ON DELETE CASCADE option which are linked with other field to other tables, the delete cascade option will fail (because mysql could not delete in the same order you create the tables) with the "ERROR 1452 (23000)". A solution for this case is to declare a clause ON DELETE SET NULL in the others foreign keys. An example:

|-------------------------------------------------------
| mysql> CREATE TABLE a(
| -> id INT AUTO_INCREMENT, user VARCHAR(20), PRIMARY KEY(id)) ENGINE=InnoDB;
| Query OK, 0 rows affected (0.08 sec)
|
| mysql> CREATE TABLE b(
| -> id INT AUTO_INCREMENT, id_a INT, name VARCHAR(20), PRIMARY KEY(id),
| -> FOREIGN KEY(id_a) REFERENCES a(id) ON DELETE CASCADE) ENGINE=InnoDB;
| Query OK, 0 rows affected (0.08 sec)
|
| mysql> CREATE TABLE c(
| -> id INT AUTO_INCREMENT, id_a INT, id_b INT, lastName VARCHAR(20), PRIMARY KEY(id),
| -> FOREIGN KEY(id_a) REFERENCES a(id) ON DELETE CASCADE,
| -> FOREIGN KEY(id_b) REFERENCES b(id)) ENGINE=InnoDB;
| Query OK, 0 rows affected (0.08 sec)
|
| mysql> INSERT INTO a(user) VALUES('zerocool');
| Query OK, 1 row affected (0.06 sec)
|
| mysql> INSERT INTO b(id_a,name) VALUES(1,'carl');
| Query OK, 1 row affected (0.06 sec)
|
| mysql> INSERT INTO c(id_a,id_b,lastName) VALUES(1,1,'anderson');
| Query OK, 1 row affected (0.06 sec)
|
| mysql> DELETE FROM a WHERE user='zerocool';
| ERROR 1451 (23000): Cannot delete or update a parent row:
| a foreign key constraint fails (`apk_zupca/c`, CONSTRAINT
| `c_ibfk_2` FOREIGN KEY (`id_b`) REFERENCES `b` (`id`))
At this point, the ON DELETE CASCADE is failing because the child table (b) has another FOREIGN KEY (c is linked with b, so row in b can't be deleted). We have created the tables in the correct order, but mysql is trying to delete rows in the order we've created the tables and it's the wrong way. A solution could be the ON DELETE SET NULL. We should add this clause during the creation of the table (or ALTER, if the table is already created):
|  mysql> CREATE TABLE c(
| -> id INT AUTO_INCREMENT, id_a INT, id_b INT, lastName VARCHAR(20), PRIMARY KEY(id),
| -> FOREIGN KEY(id_a) REFERENCES a(id) ON DELETE CASCADE,
| -> FOREIGN KEY(id_b) REFERENCES b(id) ON DELETE SET NULL) ENGINE=InnoDB;
| Query OK, 0 rows affected (0.08 sec)
And repeating last steps...
|  mysql> INSERT INTO c(id_a,id_b,lastName) VALUES(1,1,'anderson');
| Query OK, 1 row affected (0.06 sec)
|
| mysql> DELETE FROM a WHERE user='zerocool';
| Query OK, 1 row affected (0.06 sec)
|-------------------------------------------------------

Hope be helpful
  Posted by Martin Kobele on July 4, 2008
I found a fast way to delete a small subset of rows in a very big table (hundreds of thousands or millions):

You will need the to be deleted IDs in a temporary table which you might already have and you want to delete only those IDs:

A naive way would be to do

DELETE FROM LargeTable WHERE ID IN (SELECT ID FROM TemporarySmallTable);

Given that LargeTable contains maybe 300,000-500,000 and
TemporarySmallTable ca 3,000-6,000 rows, this can take ca 300ms.

Instead, try this:

DELETE FROM LargeTable USING LargeTable INNER JOIN TemporarySmallTable ON LargeTable.ID = TemporarySmallTable.ID;

This DELETE takes on the same database 1ms.

The trick is, that INNER JOIN will 'shrink' the LargeTable down to the size of the TemporarySmallTable and the delete will operate on that smaller set only, since USING will reference to the joined table.
  Posted by Omer Faruk EREN on March 10, 2009
I experienced a similiar situation today. I tried this statement:

delete m from members m where membersid in
(
select m.membersid from users u, members m, groups g
WHERE m.usersid=u.usersid AND m.groupsid=g.groupsid and g.groupsname='PARTI' and exists
( SELECT m2.membersid FROM users u2, members m2, groups g2
WHERE m2.usersid=u2.usersid AND m2.groupsid=g2.groupsid and g2.groupsname='MATRAX' and u.usersid=u2.usersid)
);

The Error code was 1093 and explanation was "You can't specify target table 'm' for update in FROM clause". The problem was that members(alias m) table is both the table that i wanted to delete and exists in inner statement. I fund the solution with the temporary table.
  Posted by Deepu vs on January 18, 2010
Delete all values in a table including auto increment values using following example

mysql>truncate tablename;

by

Deepu Surendran VS
OCS Technopark

  Posted by Jim Leek on September 8, 2010
Deleting an individual duplicate entry.
===========================
Say you have a table with two identical entries:
+----------------+---------------------------+
| project_number | description |
+----------------+---------------------------+
| 06/XC/083 | Membrane Bioreactors |
| 06/XC/083 | Membrane bioreactors |
+----------------+---------------------------+

Obviously the following SQL will delete both entries:

DELETE FROM tbl_projects WHERE project_number = '06/XC/083' AND description = 'Membrane Bioreactors';

Instead, to just delete a single entry and leave the other use the LIMIT clause:

DELETE FROM tbl_projects WHERE project_number = '06/XC/083' AND description = 'Membrane Bioreactors' LIMIT 1;
  Posted by Josh Mellicker on April 23, 2011
This worked for me, thanks to Omer above:

DELETE FROM sessionEventLog WHERE sessionEventLog.sessionID IN
(SELECT sessions.sessionID FROM sessions WHERE sessions.sourceID = 6);
  Posted by Aleksey Midenkov on November 1, 2012
"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 IGNORE are returned as warnings."

That's not true for ERROR 1451. On foreign key constraint DELETE IGNORE blocks (in 5.0).
Sign Up Login You must be logged in to post a comment.