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
句はなし) を使用してテーブル内のすべての行を削除した場合、そのシーケンスは、InnoDB
と MyISAM
を除くすべてのストレージエンジンに対して開始されます。セクション14.6.5「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
の使用のために無視されたエラーは、警告として返されます。
削除の順序
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.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
を使用すると、再利用されないままのインデックスで領域が浪費される可能性があります。このようなシナリオの例を次に示します。
インデックス付き
AUTO_INCREMENT
カラムを含むテーブルを作成します。このテーブルに多数の行を挿入します。各挿入によって、インデックスの先頭に追加されるインデックス値が生成されます。
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 つのすべてのテーブルを使用しますが、テーブル 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;