13.2.2 DELETE ステートメント

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

DELETE ステートメントは、WITH 句で始まり、DELETE 内でアクセス可能な共通テーブル式を定義できます。セクション13.2.15「WITH (共通テーブル式)」を参照してください。

単一テーブル構文

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

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

メインの句

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

where_condition は、削除される各行に対して true に評価される式です。これは、セクション13.2.10「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.37「TRUNCATE TABLE ステートメント」およびセクション13.3.6「LOCK TABLES および UNLOCK TABLES ステートメント」を参照してください。

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

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

サブクエリー

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

パーティションテーブルのサポート

DELETE では、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 つ使用できます。

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

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

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

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

修飾子

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

LOW_PRIORITY 修飾子を指定すると、他のクライアントがテーブルから読み取ることがなくなるまで、サーバーは DELETE の実行を遅延します。これは、テーブルレベルロックのみを使用するストレージエンジン (MyISAM、MEMORY、および MERGE) にのみ影響を与えます。
MyISAM テーブルの場合、QUICK 修飾子を使用すると、ストレージエンジンは削除中にインデックスリーフをマージしないため、一部の種類の削除操作が高速になる可能性があります。
IGNORE 修飾子を使用すると、MySQL は行の削除処理中に無視できるエラーを無視します。 (解析の段階で検出されたエラーは、通常の方法で処理されます。) IGNORE の使用のために無視されたエラーは、警告として返されます。詳細は、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 をまったく使用しない次の方法が有効な場合があります。

削除されない行を選択して、元のテーブルと同じ構造を持つ空のテーブルに格納します。
```
INSERT INTO t_copy SELECT * FROM t WHERE ... ;
```
RENAME TABLE を使用して元のテーブルを原子的に移動したあと、コピーの名前を元の名前に変更します。
```
RENAME TABLE t TO t_old, t_copy TO t;
```
元のテーブルを削除します。
```
DROP TABLE t_old;
```

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

MyISAM テーブル

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

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

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

インデックス付き AUTO_INCREMENT カラムを含むテーブルを作成します。
このテーブルに多数の行を挿入します。各挿入によって、インデックスの先頭に追加されるインデックス値が生成されます。
DELETE QUICK を使用して、カラムの範囲の最後にある行のブロックを削除します。

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

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

複数テーブルの削除

WHERE 句内の条件に応じて 1 つ以上のテーブルから行を削除するには、DELETE ステートメントで複数のテーブルを指定できます。複数テーブルの DELETE では、ORDER BY または LIMIT を使用できません。セクション13.2.10.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 つのすべてのテーブルを使用しますが、テーブル t1 と t2 の一致する行のみを削除します。

前の例では 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;

テーブルのエイリアスは、MySQL 8.0.16 以降の単一テーブルの DELETE ステートメントでもサポートされています。 (Bug #89410、Bug #27455809)