MySQL - DELETE

概要

DELETE文は、MySQLのテーブルから行を削除するためのSQL文である。

WHERE 句を使用して削除対象の行を指定し、WHERE句を省略すると全行が削除される。
DELETE文はトランザクション内で実行可能であり、コミット前であればロールバックにより削除を取り消すことができる。

単一テーブルからの削除だけでなく、複数テーブルからの同時削除、サブクエリを使用した条件指定等、柔軟な削除操作が可能である。
ただし、削除操作は元に戻すことができないため、WHERE句の条件指定には十分な注意が必要である。

また、大量のデータを削除する場合は、パフォーマンスとロックの影響を考慮し、バッチ削除や TRUNCATE 文の使用を検討する必要がある。

外部キー制約が設定されている場合は、参照整合性を維持するために、削除時の動作を適切に設定することが重要である。

基本構文

単一テーブルのDELETE

単一テーブルからのDELETE文の構文は以下の通りである。

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

基本的な使用例を以下に示す。

 # 特定の行を削除
 DELETE FROM users WHERE id = 100;
 
 # 複数の条件を指定
 DELETE FROM logs WHERE created_at < '2024-01-01' AND level = 'DEBUG';
 
 # 全ての行を削除 (WHERE句なし)
 DELETE FROM temp_table;

修飾子

DELETE文では、下表に示す修飾子を使用して削除動作を制御できる。

DELETE文で使用可能な修飾子
修飾子	説明
LOW_PRIORITY	他のクライアントがテーブルを読み取っていない時に削除を実行する。 MyISAM、MEMORY、MERGEストレージエンジンで有効
QUICK	MyISAMストレージエンジンでインデックスリーフのマージをスキップし、削除を高速化する。
IGNORE	削除処理中にエラーが発生しても無視して処理を継続する。

使用例を以下に示す。

 # 低優先度で削除を実行
 DELETE LOW_PRIORITY FROM logs WHERE created_at < '2024-01-01';
 
 # エラーを無視して削除を継続
 DELETE IGNORE FROM users WHERE status = 'deleted';

PARTITION句

パーティション化されたテーブルでは、PARTITION 句を使用して特定のパーティションからのみ削除できる。

 # 特定のパーティションから削除
 DELETE FROM sales
    PARTITION (p_2023_q1, p_2023_q2)
    WHERE amount < 100;
 
 # 複数パーティションを指定
 DELETE FROM logs
    PARTITION (p0, p1, p2)
    WHERE level = 'DEBUG';

条件付き削除

WHERE句の基本

WHERE句を使用して、削除対象の行を指定する。
WHERE句を省略すると、テーブルの全行が削除される。

下表に、基本的な比較演算子を示す。

比較演算子一覧
演算子	説明
=	等しい
<>, !=	等しくない
<	より小さい
>	より大きい
<=	以下
>=	以上

使用例を以下に示す。

 # 等価条件
 DELETE FROM users WHERE id = 100;
 
 # 不等号条件
 DELETE FROM products WHERE price < 1000;
 
 # 不等価条件
 DELETE FROM orders WHERE status <> 'completed';

複数条件の指定

AND、OR、NOTを使用して、複数の条件を組み合わせることができる。

 # AND条件 (両方の条件を満たす行を削除)
 DELETE FROM logs
    WHERE created_at < '2024-01-01'
       AND level = 'DEBUG';
 
 # OR条件 (いずれかの条件を満たす行を削除)
 DELETE FROM users
    WHERE status = 'deleted'
       OR last_login < '2023-01-01';
 
 # NOT条件（条件を満たさない行を削除）
 DELETE FROM products
    WHERE NOT category = 'active';
 
 # IN句 (リストに含まれる値を持つ行を削除)
 DELETE FROM users
    WHERE id IN (1, 2, 3, 5, 8);
 
 # BETWEEN句 (範囲指定)
 DELETE FROM logs
    WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
 
 # LIKE句 (パターンマッチング)
 DELETE FROM users
    WHERE email LIKE '%@example.com';
 
 # IS NULL (NULL値の削除)
 DELETE FROM products
    WHERE description IS NULL;

ORDER BYとLIMITの組み合わせ

ORDER BY 句と LIMIT 句を組み合わせることで、特定の順序で限られた数の行を削除できる。

※注意
ORDER BY句とLIMIT句は、単一テーブルDELETEでのみ使用可能である。
複数テーブルDELETEでは使用できない。

 # 古いログから1000件を削除
 DELETE FROM logs
    ORDER BY created_at ASC
    LIMIT 1000;
 
 # 最も古い100件の注文を削除
 DELETE FROM orders
    WHERE status = 'completed'
    ORDER BY created_at ASC
    LIMIT 100;
 
 # 価格の低い商品から50件を削除
 DELETE FROM products
    WHERE stock = 0
    ORDER BY price ASC
    LIMIT 50;

この機能は、大量のデータを段階的に削除する際に有用である。
一度に大量の行を削除するとロックやリソース消費が問題になるため、LIMIT句を使用してバッチ削除を行うことが推奨される。

複数テーブルからの削除

MySQLでは、複数のテーブルから同時に行を削除することができる。
これは、JOIN句を使用して実現される。

INNER JOINを使用した削除

INNER JOIN 句を使用して、関連するテーブルから同時に削除を行う。

 # 複数テーブルDELETEの基本構文
 DELETE t1, t2 FROM t1
    INNER JOIN t2 ON t1.id = t2.t1_id
    WHERE 条件;
 
 # 使用例：ユーザとその注文を同時に削除
 DELETE users, orders FROM users
    INNER JOIN orders ON users.id = orders.user_id
    WHERE users.status = 'deleted';
 
 # 3つのテーブルから削除
 DELETE users, orders, order_items FROM users
    INNER JOIN orders ON users.id = orders.user_id
    INNER JOIN order_items ON orders.id = order_items.order_id
    WHERE users.email LIKE '%@spam.com';

LEFT JOINを使用した削除

LEFT JOIN 句を使用して、他のテーブルに関連レコードが存在しない行を削除できる。

 # 注文のないユーザを削除
 DELETE users FROM users
    LEFT JOIN orders ON users.id = orders.user_id
    WHERE orders.user_id IS NULL;
 
 # 商品がないカテゴリを削除
 DELETE categories FROM categories
    LEFT JOIN products ON categories.id = products.category_id
    WHERE products.category_id IS NULL;

USING句を使用した構文

USING 句を使用した複数テーブルDELETEの別の構文を以下に示す。

 # USING句を使用した構文
 DELETE FROM t1 USING t1
    INNER JOIN t2 ON t1.id = t2.t1_id
    WHERE 条件;
 
 # 使用例
 DELETE FROM users USING users
    INNER JOIN orders ON users.id = orders.user_id
    WHERE orders.status = 'cancelled';

サブクエリを使用した削除

サブクエリを使用して、別のテーブルの値に基づいて削除を行うことができる。

 # サブクエリで条件を指定
 DELETE FROM users
    WHERE id IN (
       SELECT user_id FROM orders
          WHERE status = 'cancelled'
    );
 
 # NOT INを使用
 DELETE FROM products
    WHERE id NOT IN (
       SELECT DISTINCT product_id FROM order_items
    );
 
 # EXISTSを使用
 DELETE FROM users
    WHERE EXISTS (
       SELECT 1 FROM orders
          WHERE orders.user_id = users.id
             AND orders.status = 'fraud'
    );

※制限事項
同じテーブルからSELECTして削除することは直接できない。

 # エラーになる例
 DELETE FROM users
    WHERE id IN (
       SELECT id FROM users WHERE status = 'deleted'
    );

回避策として、派生テーブル (サブクエリを1度包む) を使用する。

 # 派生テーブルを使用した回避策
 DELETE FROM users
    WHERE id IN (
       SELECT id FROM (
          SELECT id FROM users WHERE status = 'deleted'
       ) AS tmp
    );

外部キー制約と削除

外部キー制約が設定されている場合、削除時の動作を制御するアクションを指定できる。

ON DELETE CASCADE

親レコード削除時に、子レコードも自動的に削除される。

 # 外部キー制約の定義
 CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
       ON DELETE CASCADE
 );
 
 # ユーザを削除すると、そのユーザの注文も自動削除される
 DELETE FROM users WHERE id = 100;

ON DELETE SET NULL

親レコード削除時に、子レコードの外部キー列が NULL に設定される。

 # 外部キー制約の定義
 CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
       ON DELETE SET NULL
 );
 
 # ユーザを削除すると、注文のuser_idがNULLになる
 DELETE FROM users WHERE id = 100;

※注意
外部キー列がNOT NULL制約を持つ場合、SET NULLは使用できない。

外部キー制約の一時的な無効化

データの一括削除等で外部キーチェックを一時的に無効化する場合、以下に示すコマンドを使用する。

 # 外部キーチェックを無効化
 SET FOREIGN_KEY_CHECKS = 0;
 
 # 削除操作を実行
 DELETE FROM parent_table WHERE condition;
 DELETE FROM child_table WHERE condition;
 
 # 外部キーチェックを再度有効化
 SET FOREIGN_KEY_CHECKS = 1;

※警告
外部キーチェックを無効にすると、データの整合性が失われる可能性があるため、使用には十分注意が必要である。

セーフモード

MySQLのセーフモード（sql_safe_updates）は、WHERE句なしまたはキー列を使用しないDELETE文やUPDATE文を防止する。

セーフモードが有効な場合、以下のような削除はエラー（エラーコード1175）になる。

 # WHERE句なしの削除（エラーになる）
 DELETE FROM users;
 
 # キー列を使用しない削除（エラーになる）
 DELETE FROM users WHERE email = 'test@example.com';

セーフモードの設定を変更する方法を以下に示す。

 # セーフモードを無効化
 SET SQL_SAFE_UPDATES = 0;
 
 # 削除を実行
 DELETE FROM users WHERE email = 'test@example.com';
 
 # セーフモードを再度有効化
 SET SQL_SAFE_UPDATES = 1;

※注意
セーフモードは、意図しないデータ削除を防ぐための重要な機能である。
一時的に無効化する場合は、操作後に必ず再度有効化することが推奨される。

削除結果の確認

DELETE文の実行結果は、影響を受けた行数として表示される。

 DELETE FROM users WHERE status = 'deleted';

# 出力例：

Query OK, 5 rows affected (0.01 sec)

ROW_COUNT 関数を使用して、直前のDELETE文で削除された行数を取得できる。

 DELETE FROM logs WHERE created_at < '2024-01-01';
 SELECT ROW_COUNT();

# 出力例

# +--------------+
# | ROW_COUNT()  |
# +--------------+
# |         1234 |
# +--------------+

※注意
ROW_COUNT関数は、直後のSELECT文で実行する必要がある。
他のSQL文を実行すると、値がリセットされる。

パフォーマンス最適化

インデックスの活用

DELETE文のパフォーマンスを向上させるために、WHERE句で使用する列にインデックスを作成する。

 # インデックスが存在しない場合、全行スキャンが発生する
 DELETE FROM logs WHERE created_at < '2024-01-01';
 
 # インデックスを作成
 CREATE INDEX idx_created_at ON logs(created_at);
 
 # インデックスを使用した高速削除
 DELETE FROM logs WHERE created_at < '2024-01-01';

EXPLAIN 文を使用して、DELETE文の実行計画を確認できる。

 # 実行計画を確認
 
 EXPLAIN DELETE FROM logs WHERE created_at < '2024-01-01';

大量データの削除

大量のデータを1度に削除すると、以下に示す問題が発生する可能性がある。

テーブルロックによる他のクエリの待機
トランザクションログの肥大化
長時間のロック保持によるデッドロック
メモリとCPUリソースの大量消費

推奨される大量データ削除の方法を以下に示す。

バッチ削除 (チャンク削除)

LIMIT 句を使用して、少しずつ削除を行う。

 # 1000件ずつ削除を繰り返す
 DELETE FROM logs
    WHERE created_at < '2024-01-01'
    ORDER BY created_at ASC
    LIMIT 1000;
 
 # 削除対象がなくなるまで繰り返す

スクリプトでのバッチ削除の例を以下に示す。

 #!/usr/bin/env sh
 
 while true; do
    affected=$(mysql -u user -p'password' -D database -N -B -e \
       "DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 1000; \
        SELECT ROW_COUNT();")
 
    echo "Deleted $affected rows"
 
    if [ "$affected" -eq 0 ]; then
       break
    fi
 
    sleep 1
 done

トランザクションの制御

大量削除時は、トランザクションを短く保つことが重要である。

 # 自動コミットを有効にする (各DELETE文が自動的にコミットされる)
 SET AUTOCOMMIT = 1;
 
 # バッチ削除を実行
 DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 1000;

削除後の最適化

大量のデータを削除した後は、OPTIMIZE TABLE 文でテーブルを最適化する。

 # テーブルの最適化 (断片化の解消、領域の再利用)
 
 OPTIMIZE TABLE logs;

パーティション削除

パーティション化されたテーブルの場合、ALTER TABLE文でパーティション全体を削除する方が高速である。

 # パーティションを削除 (DELETE文より高速)
 
 ALTER TABLE logs DROP PARTITION p_2023_q1, p_2023_q2;

DELETEとTRUNCATEの違い

DELETE文と TRUNCATE TABLE文の主な違いを以下に示す。

DELETE文とTRUNCATE TABLE文の比較
項目	DELETE	TRUNCATE
削除単位	行単位で削除	テーブル全体を一括削除
速度	低速（行ごとに処理）	高速（テーブルを再作成）
WHERE句	使用可能	使用不可
トランザクションログ	全削除でも各行を記録	最小限の記録
ロールバック	可能	不可（暗黙的なコミット）
AUTO_INCREMENT	リセットされない	初期値にリセット
トリガー	DELETEトリガーが起動する	トリガーは起動しない
外部キー制約	制約に従う	子テーブルがある場合はエラー
権限	DELETE権限が必要	DROP権限が必要

使用例を以下に示す。

 # DELETE文 (条件指定可能、ロールバック可能)
 DELETE FROM temp_data WHERE created_at < '2024-01-01';
 
 # TRUNCATE文 (全行削除、高速)
 TRUNCATE TABLE temp_data;

使い分けの指針を以下に示す。

条件付き削除が必要な場合
DELETE文を使用する。
テーブルの全データを削除する場合
TRUNCATE文が高速である。
ロールバックが必要な場合
DELETE文を使用する。
外部キー制約がある場合
DELETE文を使用する。(TRUNCATEはエラーになる)
AUTO_INCREMENTをリセットしたい場合
TRUNCATE文を使用する。