MySQL - DELETEのソースを表示

== 概要 ==
DELETE文は、MySQLのテーブルから行を削除するためのSQL文である。<br>
<br>
<code>WHERE</code> 句を使用して削除対象の行を指定し、WHERE句を省略すると全行が削除される。<br>
DELETE文はトランザクション内で実行可能であり、コミット前であればロールバックにより削除を取り消すことができる。<br>
<br>
単一テーブルからの削除だけでなく、複数テーブルからの同時削除、サブクエリを使用した条件指定等、柔軟な削除操作が可能である。<br>
ただし、削除操作は元に戻すことができないため、WHERE句の条件指定には十分な注意が必要である。<br>
<br>
また、大量のデータを削除する場合は、パフォーマンスとロックの影響を考慮し、バッチ削除や <code>TRUNCATE</code> 文の使用を検討する必要がある。<br>
<br>
外部キー制約が設定されている場合は、参照整合性を維持するために、削除時の動作を適切に設定することが重要である。<br>
<br><br>

== 基本構文 ==
==== 単一テーブルのDELETE ====
単一テーブルからのDELETE文の構文は以下の通りである。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
 </syntaxhighlight>
<br>
基本的な使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 特定の行を削除
 DELETE FROM users WHERE id = 100;
 
 # 複数の条件を指定
 DELETE FROM logs WHERE created_at < '2024-01-01' AND level = 'DEBUG';
 
 # 全ての行を削除 (WHERE句なし)
 DELETE FROM temp_table;
 </syntaxhighlight>
<br>
==== 修飾子 ====
DELETE文では、下表に示す修飾子を使用して削除動作を制御できる。<br>
<br>
<center>
{| class="wikitable"
|+ DELETE文で使用可能な修飾子
! 修飾子 !! 説明
|-
| LOW_PRIORITY || 他のクライアントがテーブルを読み取っていない時に削除を実行する。<br>MyISAM、MEMORY、MERGEストレージエンジンで有効
|-
| QUICK || MyISAMストレージエンジンでインデックスリーフのマージをスキップし、<br>削除を高速化する。
|-
| IGNORE || 削除処理中にエラーが発生しても無視して処理を継続する。
|}
</center>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 低優先度で削除を実行
 DELETE LOW_PRIORITY FROM logs WHERE created_at < '2024-01-01';
 
 # エラーを無視して削除を継続
 DELETE IGNORE FROM users WHERE status = 'deleted';
 </syntaxhighlight>
<br>
==== PARTITION句 ====
パーティション化されたテーブルでは、<code>PARTITION</code> 句を使用して特定のパーティションからのみ削除できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 特定のパーティションから削除
 DELETE FROM sales
    PARTITION (p_2023_q1, p_2023_q2)
    WHERE amount < 100;
 
 # 複数パーティションを指定
 DELETE FROM logs
    PARTITION (p0, p1, p2)
    WHERE level = 'DEBUG';
 </syntaxhighlight>
<br><br>

== 条件付き削除 ==
==== WHERE句の基本 ====
WHERE句を使用して、削除対象の行を指定する。<br>
<u>WHERE句を省略すると、テーブルの全行が削除される。</u><br>
<br>
下表に、基本的な比較演算子を示す。<br>
<br>
<center>
{| class="wikitable"
|+ 比較演算子一覧
! 演算子 !! 説明
|-
| = || 等しい
|-
| <>, != || 等しくない
|-
| < || より小さい
|-
| > || より大きい
|-
| <= || 以下
|-
| >= || 以上
|}
</center>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 等価条件
 DELETE FROM users WHERE id = 100;
 
 # 不等号条件
 DELETE FROM products WHERE price < 1000;
 
 # 不等価条件
 DELETE FROM orders WHERE status <> 'completed';
 </syntaxhighlight>
<br>
==== 複数条件の指定 ====
AND、OR、NOTを使用して、複数の条件を組み合わせることができる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 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;
 </syntaxhighlight>
<br><br>

== ORDER BYとLIMITの組み合わせ ==
<code>ORDER BY</code> 句 と <code>LIMIT</code> 句を組み合わせることで、特定の順序で限られた数の行を削除できる。<br>
<br>
<u>※注意</u><br>
<u>ORDER BY句とLIMIT句は、単一テーブルDELETEでのみ使用可能である。</u><br>
<u>複数テーブルDELETEでは使用できない。</u><br>
<br>
 <syntaxhighlight lang="mysql">
 # 古いログから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;
 </syntaxhighlight>
<br>
この機能は、大量のデータを段階的に削除する際に有用である。<br>
一度に大量の行を削除するとロックやリソース消費が問題になるため、LIMIT句を使用してバッチ削除を行うことが推奨される。<br>
<br><br>

== 複数テーブルからの削除 ==
MySQLでは、複数のテーブルから同時に行を削除することができる。<br>
これは、JOIN句を使用して実現される。<br>
<br>
==== INNER JOINを使用した削除 ====
<code>INNER JOIN</code> 句を使用して、関連するテーブルから同時に削除を行う。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 複数テーブル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';
 </syntaxhighlight>
<br>
==== LEFT JOINを使用した削除 ====
<code>LEFT JOIN</code> 句を使用して、他のテーブルに関連レコードが存在しない行を削除できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 注文のないユーザを削除
 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;
 </syntaxhighlight>
<br>
==== USING句を使用した構文 ====
<code>USING</code> 句を使用した複数テーブルDELETEの別の構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 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';
 </syntaxhighlight>
<br><br>

== サブクエリを使用した削除 ==
サブクエリを使用して、別のテーブルの値に基づいて削除を行うことができる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # サブクエリで条件を指定
 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'
    );
 </syntaxhighlight>
<br>
<u>※制限事項</u><br>
<u>同じテーブルからSELECTして削除することは直接できない。</u><br>
<br>
 <syntaxhighlight lang="mysql">
 # エラーになる例
 DELETE FROM users
    WHERE id IN (
       SELECT id FROM users WHERE status = 'deleted'
    );
 </syntaxhighlight>
<br>
回避策として、派生テーブル (サブクエリを1度包む) を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 派生テーブルを使用した回避策
 DELETE FROM users
    WHERE id IN (
       SELECT id FROM (
          SELECT id FROM users WHERE status = 'deleted'
       ) AS tmp
    );
 </syntaxhighlight>
<br><br>

== 外部キー制約と削除 ==
外部キー制約が設定されている場合、削除時の動作を制御するアクションを指定できる。<br>
<br>
==== ON DELETE CASCADE ====
親レコード削除時に、子レコードも自動的に削除される。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 外部キー制約の定義
 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;
 </syntaxhighlight>
<br>
==== ON DELETE SET NULL ====
親レコード削除時に、子レコードの外部キー列が <code>NULL</code> に設定される。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 外部キー制約の定義
 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;
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>外部キー列がNOT NULL制約を持つ場合、SET NULLは使用できない。</u><br>
<br>
==== 外部キー制約の一時的な無効化 ====
データの一括削除等で外部キーチェックを一時的に無効化する場合、以下に示すコマンドを使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 外部キーチェックを無効化
 SET FOREIGN_KEY_CHECKS = 0;
 
 # 削除操作を実行
 DELETE FROM parent_table WHERE condition;
 DELETE FROM child_table WHERE condition;
 
 # 外部キーチェックを再度有効化
 SET FOREIGN_KEY_CHECKS = 1;
 </syntaxhighlight>
<br>
<u>※警告</u><br>
<u>外部キーチェックを無効にすると、データの整合性が失われる可能性があるため、使用には十分注意が必要である。</u><br>
<br><br>

== セーフモード ==
MySQLのセーフモード（sql_safe_updates）は、WHERE句なし または キー列を使用しないDELETE文やUPDATE文を防止する。<br>
<br>
セーフモードが有効な場合、以下のような削除はエラー（エラーコード1175）になる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # WHERE句なしの削除（エラーになる）
 DELETE FROM users;
 
 # キー列を使用しない削除（エラーになる）
 DELETE FROM users WHERE email = 'test@example.com';
 </syntaxhighlight>
<br>
セーフモードの設定を変更する方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # セーフモードを無効化
 SET SQL_SAFE_UPDATES = 0;
 
 # 削除を実行
 DELETE FROM users WHERE email = 'test@example.com';
 
 # セーフモードを再度有効化
 SET SQL_SAFE_UPDATES = 1;
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>セーフモードは、意図しないデータ削除を防ぐための重要な機能である。</u><br>
<u>一時的に無効化する場合は、操作後に必ず再度有効化することが推奨される。</u><br>
<br><br>

== 削除結果の確認 ==
DELETE文の実行結果は、影響を受けた行数として表示される。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELETE FROM users WHERE status = 'deleted';
 </syntaxhighlight>
<br>
 # 出力例：
 
 Query OK, 5 rows affected (0.01 sec)
<br>
<code>ROW_COUNT</code> 関数を使用して、直前のDELETE文で削除された行数を取得できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELETE FROM logs WHERE created_at < '2024-01-01';
 SELECT ROW_COUNT();
 </syntaxhighlight>
<br>
 # 出力例
 
 # +--------------+
 # | ROW_COUNT()  |
 # +--------------+
 # |         1234 |
 # +--------------+
<br>
<u>※注意</u><br>
<u>ROW_COUNT関数は、直後のSELECT文で実行する必要がある。</u><br>
<u>他のSQL文を実行すると、値がリセットされる。</u><br>
<br><br>

== パフォーマンス最適化 ==
==== インデックスの活用 ====
DELETE文のパフォーマンスを向上させるために、WHERE句で使用する列にインデックスを作成する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # インデックスが存在しない場合、全行スキャンが発生する
 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';
 </syntaxhighlight>
<br>
<code>EXPLAIN</code> 文を使用して、DELETE文の実行計画を確認できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 実行計画を確認
 
 EXPLAIN DELETE FROM logs WHERE created_at < '2024-01-01';
 </syntaxhighlight>
<br>
==== 大量データの削除 ====
大量のデータを1度に削除すると、以下に示す問題が発生する可能性がある。<br>
<br>
* テーブルロックによる他のクエリの待機
* トランザクションログの肥大化
* 長時間のロック保持によるデッドロック
* メモリとCPUリソースの大量消費
<br>
推奨される大量データ削除の方法を以下に示す。<br>
<br>
===== バッチ削除 (チャンク削除) =====
<code>LIMIT</code> 句を使用して、少しずつ削除を行う。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 1000件ずつ削除を繰り返す
 DELETE FROM logs
    WHERE created_at < '2024-01-01'
    ORDER BY created_at ASC
    LIMIT 1000;
 
 # 削除対象がなくなるまで繰り返す
 </syntaxhighlight>
<br>
スクリプトでのバッチ削除の例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sh">
 #!/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
 </syntaxhighlight>
<br>
===== トランザクションの制御 =====
大量削除時は、トランザクションを短く保つことが重要である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 自動コミットを有効にする (各DELETE文が自動的にコミットされる)
 SET AUTOCOMMIT = 1;
 
 # バッチ削除を実行
 DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 1000;
 </syntaxhighlight>
<br>
===== 削除後の最適化 =====
大量のデータを削除した後は、<code>OPTIMIZE TABLE</code> 文でテーブルを最適化する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # テーブルの最適化 (断片化の解消、領域の再利用)
 
 OPTIMIZE TABLE logs;
 </syntaxhighlight>
<br>
===== パーティション削除 =====
パーティション化されたテーブルの場合、ALTER TABLE文でパーティション全体を削除する方が高速である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # パーティションを削除 (DELETE文より高速)
 
 ALTER TABLE logs DROP PARTITION p_2023_q1, p_2023_q2;
 </syntaxhighlight>
<br><br>

== DELETEとTRUNCATEの違い ==
DELETE文 と TRUNCATE TABLE文の主な違いを以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ DELETE文とTRUNCATE TABLE文の比較
! 項目 !! DELETE !! TRUNCATE
|-
| 削除単位 || 行単位で削除 || テーブル全体を一括削除
|-
| 速度 || 低速（行ごとに処理） || 高速（テーブルを再作成）
|-
| WHERE句 || 使用可能 || 使用不可
|-
| トランザクションログ || 全削除でも各行を記録 || 最小限の記録
|-
| ロールバック || 可能 || 不可（暗黙的なコミット）
|-
| AUTO_INCREMENT || リセットされない || 初期値にリセット
|-
| トリガー || DELETEトリガーが起動する || トリガーは起動しない
|-
| 外部キー制約 || 制約に従う || 子テーブルがある場合はエラー
|-
| 権限 || DELETE権限が必要 || DROP権限が必要
|}
</center>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # DELETE文 (条件指定可能、ロールバック可能)
 DELETE FROM temp_data WHERE created_at < '2024-01-01';
 
 # TRUNCATE文 (全行削除、高速)
 TRUNCATE TABLE temp_data;
 </syntaxhighlight>
<br>
使い分けの指針を以下に示す。<br>
<br>
* 条件付き削除が必要な場合
*: DELETE文を使用する。
* テーブルの全データを削除する場合
*: TRUNCATE文が高速である。
* ロールバックが必要な場合
*: DELETE文を使用する。
* 外部キー制約がある場合
*: DELETE文を使用する。(TRUNCATEはエラーになる)
* AUTO_INCREMENTをリセットしたい場合
*: TRUNCATE文を使用する。
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - 電子回路とSUSE Linuxに関する情報 | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:MySQL]]