SQL DELETEとTRUNCATEの主な違い

SQL DELETEとTRUNCATEの主な違い

DELETE と TRUNCATE はどちらもテーブルから行を削除しますが、根本的に異なる仕組みで動作し、それぞれ異なるシナリオに適しています。

DELETE

DELETE FROM orders
WHERE status = 'cancelled' AND order_date < '2024-01-01';

特徴:

• 行を1つずつ削除する（行レベルの操作）
• WHERE句による選択的な削除が可能
• 各行でDELETEトリガーが発火する
• 完全にログに記録される（削除された各行がトランザクションログに記録）
• トランザクション内でロールバックが可能
• 自動採番カウンターはリセットされない
• 大量削除では低速

TRUNCATE

TRUNCATE TABLE temp_import_data;

特徴:

• データページの割り当てを解除してすべての行を削除する（ページレベルの操作）
• WHERE句は使用不可（すべてを削除する）
• ほとんどのデータベースでDELETEトリガーが発火しない
• 最小限のログ記録（ページの割り当て解除のみ記録）
• ロールバックの動作はデータベースにより異なる（PostgreSQLではサポート、MySQLでは非サポート）
• 自動採番カウンターが初期値にリセットされる
• すべての行の削除ではかなり高速

比較表

機能	DELETE	TRUNCATE
WHERE句	あり	なし
トリガー	発火する	発火しない
ログ記録	完全	最小限
ロールバック	可能	データベースに依存
速度（全行）	低速	高速
自動採番	維持	リセット
外部キー	行ごとにチェック	参照されている場合はブロック

DELETEを使用する場合

• 条件に基づいて特定の行を削除する場合
• 監査証跡のためにトリガーが発火する必要がある場合
• 確実なロールバック機能が必要な場合
• 外部キー参照があるテーブルから削除する場合

TRUNCATEを使用する場合

• ステージングテーブルや一時テーブルのクリア
• テスト実行間のテストデータのリセット
• トリガーが不要な場合の全データ削除
• 数百万行をクリアする必要がありパフォーマンスが重要な場合

安全な削除パターン

DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 10000;

バッチ削除により長時間実行されるトランザクションを回避します。影響行数がゼロになるまで繰り返します。

DELETEとTRUNCATEの使い分けを理解することは、データの整合性と監査要件を維持しながら効率的にデータライフサイクルを管理するための鍵です。