MySQL - UPDATEのソースを表示

== 概要 ==
UPDATE文は、MySQLのテーブルに格納された既存レコードを修正するためのSQL文である。<br>
<br>
<code>WHERE</code> 句を使用して更新対象の行を指定し、WHERE句を省略すると全行が更新される。<br>
UPDATE文はトランザクション内で実行可能であり、コミット前であればロールバックにより更新を取り消すことができる。<br>
<br>
単一テーブルの更新だけでなく、複数テーブルの同時更新、サブクエリを使用した条件指定、CASE文による条件分岐更新等、柔軟な更新操作が可能である。<br>
単一テーブル更新では <code>ORDER BY</code> 句と <code>LIMIT</code> 句を組み合わせることで、特定の順序で限られた数の行を更新できる。<br>
<br>
また、大量のデータを更新する場合は、パフォーマンスとロックの影響を考慮し、バッチ更新やインデックスの活用を検討する必要がある。<br>
更新操作では、SET句で指定したカラムとインデックスの両方が更新されるため、不要なインデックスは削除することで性能向上を図ることができる。<br>
<br>
外部キー制約が設定されている場合は、参照整合性を維持するために、更新時の動作 (CASCADE、SET NULL、RESTRICT) を適切に設定することが重要である。<br>
MySQLのセーフモード (sql_safe_updates) を有効にすることにより、WHERE句なし または キー列を使用しないUPDATE文を防止し、意図しないデータ更新を防ぐことができる。<br>
<br>
UPDATE文と類似の更新系文として、REPLACE文 (削除後に挿入)、INSERT ON DUPLICATE KEY UPDATE文 (重複時に更新) が存在するが、それぞれ動作が異なるため、用途に応じて適切に使い分けることが必要である。<br>
<br><br>

== 基本構文 ==
==== 単一テーブルのUPDATE ====
単一テーブルのUPDATE文の構文は以下の通りである。<br>
<br>
 <syntaxhighlight lang="mysql">
 UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
 </syntaxhighlight>
<br>
基本的な使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 特定の行を更新
 UPDATE products SET price = 100 WHERE id = 5;
 
 # 複数のカラムを同時に更新
 UPDATE users SET status = 'active', last_login = NOW() WHERE id = 100;
 
 # 全ての行を更新 (WHERE句なし)
 UPDATE temp_table SET processed = 1;
 </syntaxhighlight>
<br>
SET句では、複数のカラムを同時に更新できる。<br>
この場合、代入は左から右へ順に評価される。<br>
<br>
 <syntaxhighlight lang="mysql">
 # カラムの値を参照した更新
 UPDATE items SET col1 = col1 + 1, col2 = col1;
 
 # col1が増加された後、col2にはその新しい値が割り当てられる
 </syntaxhighlight>
<br>
<code>DEFAULT</code> キーワードを使用して、カラムのデフォルト値を設定できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # デフォルト値を設定
 UPDATE products SET price = DEFAULT WHERE id = 5;
 </syntaxhighlight>
<br>
==== 修飾子 ====
UPDATE文では、下表に示す修飾子を使用して更新動作を制御できる。<br>
<br>
<center>
{| class="wikitable"
|+ UPDATE文で使用可能な修飾子
! 修飾子 !! 説明
|-
| LOW_PRIORITY || テーブルを読み取り中の他のクライアントがいなくなるまで、<br>UPDATEの実行を遅延させる。<br>MyISAM、MEMORY、MERGEストレージエンジンで有効
|-
| IGNORE || 更新中にエラーが発生しても無視して処理を継続する。<br>ユニークキーの重複が発生した場合、その行は更新されない。
|}
</center>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 低優先度で更新を実行
 UPDATE LOW_PRIORITY products SET price = 100 WHERE category_id = 5;
 
 # エラーを無視して更新を継続
 UPDATE IGNORE users SET email = 'new@example.com' WHERE status = 'active';
 </syntaxhighlight>
<br>
==== PARTITION句 ====
パーティション化されたテーブルでは、<code>PARTITION</code> 句を使用して特定のパーティションからのみ更新できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 特定のパーティションから更新
 UPDATE employees PARTITION (p0)
    SET store_id = 2
    WHERE fname = 'Jill';
 
 # 複数パーティションを指定
 UPDATE sales PARTITION (p_2023_q1, p_2023_q2)
    SET processed = 1
    WHERE amount > 1000;
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>指定されたパーティションが存在しない場合、ステートメントはエラーで失敗する。</u><br>
<br><br>

== 条件付き更新 ==
==== WHERE句の基本 ====
WHERE句を使用して、更新対象の行を指定する。<br>
WHERE句を省略すると、テーブルの全行が更新される。<br>
<br>
下表に、基本的な比較演算子を示す。<br>
<br>
<center>
{| class="wikitable"
|+ 比較演算子一覧
! 演算子 !! 説明
|-
| = || 等しい
|-
| <>, != || 等しくない
|-
| < || より小さい
|-
| > || より大きい
|-
| <= || 以下
|-
| >= || 以上
|}
</center>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 等価条件
 UPDATE products SET price = 100 WHERE id = 5;
 
 # 不等号条件
 UPDATE products SET discount = 0.1 WHERE price > 1000;
 
 # 不等価条件
 UPDATE orders SET status = 'pending' WHERE status <> 'completed';
 </syntaxhighlight>
<br>
==== 複数条件の指定 ====
AND、OR、NOTを使用して、複数の条件を組み合わせることができる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # AND条件 (両方の条件を満たす行を更新)
 UPDATE products
    SET discount = 0.1
    WHERE category = 'electronics' AND price > 1000;
 
 # OR条件 (いずれかの条件を満たす行を更新)
 UPDATE products
    SET status = 'inactive'
    WHERE last_purchase IS NULL
       OR last_purchase < DATE_SUB(NOW(), INTERVAL 1 YEAR);
 
 # NOT条件 (条件を満たさない行を更新)
 UPDATE products
    SET featured = 1
    WHERE NOT status = 'inactive';
 
 # IN句 (リストに含まれる値を持つ行を更新)
 UPDATE orders
    SET status = 'shipped'
    WHERE order_id IN (101, 102, 103, 104);
 
 # BETWEEN句 (範囲指定)
 UPDATE sales
    SET commission = salary * 0.15
    WHERE hire_date BETWEEN '2020-01-01' AND '2021-12-31';
 
 # LIKE句 (パターンマッチング)
 UPDATE customers
    SET notification_preference = 'email'
    WHERE email LIKE '%@gmail.com';
 
 # IS NULL (NULL値の更新)
 UPDATE products
    SET description = 'No description available'
    WHERE description IS NULL;
 
 # 複合条件の例
 UPDATE inventory
    SET alert_level = 'low'
    WHERE (quantity < 50 AND product_type = 'essential')
       OR (quantity < 10 AND product_type = 'standard');
 </syntaxhighlight>
<br><br>

== ORDER BYとLIMITの組み合わせ ==
<code>ORDER BY</code> 句 と <code>LIMIT</code> 句を組み合わせることで、特定の順序で限られた数の行を更新できる。<br>
<br>
<u>※注意</u><br>
<u>ORDER BY句とLIMIT句は、単一テーブルUPDATEでのみ使用可能である。</u><br>
<u>複数テーブルのUPDATEでは使用できない。</u><br>
<br>
 <syntaxhighlight lang="mysql">
 # 最も最近雇用された5名の給与を5%引き上げる
 UPDATE employees
    SET salary = salary * 1.05
    ORDER BY hire_date DESC
    LIMIT 5;
 
 # 優先度の高いタスクから10件を完了状態にする
 UPDATE tasks
    SET status = 'completed'
    WHERE priority = 'high'
    ORDER BY created_date DESC
    LIMIT 10;
 
 # 古いデータから1000件ずつ処理
 UPDATE archive_data
    SET processed = 1
    ORDER BY created_date ASC
    LIMIT 1000;
 </syntaxhighlight>
<br>
この機能は、大量のデータを段階的に更新する際に有用である。<br>
一度に大量の行を更新するとロックやリソース消費が問題になるため、LIMIT句を使用してバッチ更新を行うことが推奨される。<br>
<br><br>

== 複数テーブルの更新 ==
MySQLでは、複数のテーブルを同時に更新することができる。<br>
これは、JOIN句を使用して実現される。<br>
<br>
==== INNER JOINを使用した更新 ====
<code>INNER JOIN</code> 句を使用して、関連するテーブルを同時に更新する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 複数テーブルUPDATEの基本構文
 UPDATE t1
    INNER JOIN t2 ON t1.id = t2.t1_id
    SET t1.col1 = value1, t2.col2 = value2
    WHERE <条件>;
 
 # 使用例：商品のカテゴリ名を更新
 UPDATE products p
    INNER JOIN categories c ON p.category_id = c.id
    SET p.category_name = c.name
    WHERE c.status = 'active';
 
 # 複数テーブルの同時更新
 UPDATE t1, t2
    SET t1.col1 = t2.col1, t2.col2 = expr
    WHERE t1.id = t2.id;
 </syntaxhighlight>
<br>
==== LEFT JOINを使用した更新 ====
<code>LEFT JOIN</code> 句を使用して、左テーブルの全行を評価しながら更新できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 顧客の最終注文日を更新
 UPDATE customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    SET c.last_order_date = o.order_date
    WHERE o.order_date IS NOT NULL
       OR c.last_order_date IS NULL;
 
 # 左テーブルの全行が評価される
 # 一致しない場合、右テーブルのカラムはNULLになる
 </syntaxhighlight>
<br>
JOINの型と動作の違いを下表に示す。<br>
<br>
<center>
{| class="wikitable"
|+ JOIN句を使用したUPDATEの比較
! JOIN型 !! 説明 !! 対象行
|-
| INNER JOIN || 両テーブルでマッチするレコードのみ || マッチするレコードのみ更新
|-
| LEFT JOIN || 左テーブルの全レコード || 左テーブル全行を評価、右テーブルはマッチしない場合NULL
|}
</center>
<br><br>

== サブクエリを使用した更新 ==
サブクエリを使用して、別のテーブルの値に基づいて更新を行うことができる。<br>
<br>
==== SET句でのサブクエリ使用 ====
SET句でサブクエリを使用することで、他のテーブルの値を参照して更新できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 各従業員の給与を部門の平均給与に設定
 UPDATE employees
    SET salary = (
       SELECT AVG(salary)
       FROM salary_benchmarks
       WHERE department_id = employees.department_id
    )
    WHERE hire_date >= '2020-01-01';
 
 # 複数カラムの更新
 UPDATE sales_summary
    SET (total_amount, num_transactions) = (
       SELECT SUM(amount), COUNT(*)
       FROM transactions
       WHERE month = sales_summary.month
    )
    WHERE year = 2024;
 </syntaxhighlight>
<br>
==== WHERE句でのサブクエリ使用 ====
WHERE句でサブクエリを使用して、更新対象の行を動的に決定できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # IN演算子を使用
 UPDATE orders
    SET status = 'verified'
    WHERE customer_id IN (
       SELECT id
       FROM customers
       WHERE credit_score >= 750
    );
 
 # NOT IN演算子を使用
 UPDATE products
    SET discount = 0.2
    WHERE id NOT IN (
       SELECT product_id
       FROM top_sellers
       WHERE year = YEAR(NOW())
    );
 
 # EXISTS演算子を使用
 UPDATE customers
    SET loyalty_tier = 'gold'
    WHERE EXISTS (
       SELECT 1
       FROM orders
       WHERE customer_id = customers.id
          AND order_date >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
          AND total_amount > 10000
    );
 </syntaxhighlight>
<br>
==== 同一テーブルを参照する場合の制限と回避策 ====
一般的に、同じテーブルを更新しながら、その同じテーブルをサブクエリで参照することはできない。<br>
<br>
 <syntaxhighlight lang="mysql">
 # エラーになる例
 UPDATE products
    SET price = price * 1.1
    WHERE id IN (
       SELECT id FROM products WHERE sales > 100
    );
 
 # エラーメッセージ:
 # ERROR 1093 (HY000): You can't specify the target table for update in the FROM clause
 </syntaxhighlight>
<br>
回避策として、派生テーブルを使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 派生テーブルを使用した回避策 (推奨)
 UPDATE products p,
    (SELECT id FROM products WHERE sales > 100) AS top_sellers
    SET p.price = p.price * 1.1
    WHERE p.id = top_sellers.id;
 
 # 一時テーブルを使用した回避策
 CREATE TEMPORARY TABLE temp_ids AS
    SELECT id FROM products WHERE sales > 100;
 
 UPDATE products
    SET price = price * 1.1
    WHERE id IN (SELECT id FROM temp_ids);
 
 DROP TEMPORARY TABLE temp_ids;
 
 # セルフジョインを使用した回避策
 UPDATE products p1
    INNER JOIN products p2 ON p1.id = p2.id
    SET p1.price = p1.price * 1.1
    WHERE p2.sales > 100;
 </syntaxhighlight>
<br><br>

== CASE文を使用した条件分岐更新 ==
CASE式を使用することで、条件に応じて異なる値を設定できる。<br>
<br>
==== 単一カラムの条件分岐更新 ====
CASE式の基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 UPDATE table_name
    SET column = CASE
       WHEN condition1 THEN value1
       WHEN condition2 THEN value2
       ...
       ELSE default_value
    END
    WHERE where_condition;
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 成績に応じてグレードを設定
 UPDATE students
    SET grade = CASE
       WHEN total_marks >= 450 THEN 'A'
       WHEN total_marks >= 350 AND total_marks < 450 THEN 'B'
       WHEN total_marks >= 300 AND total_marks < 350 THEN 'C'
       WHEN total_marks >= 200 THEN 'D'
       ELSE 'F'
    END
    WHERE exam_date = '2024-01-15';
 </syntaxhighlight>
<br>
==== 複数カラムの同時条件分岐更新 ====
複数のカラムに対して、それぞれ異なるCASE式を適用できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 給与とボーナスを同時に更新
 UPDATE employees
    SET salary = CASE
          WHEN department = 'Engineering' THEN salary * 1.15
          WHEN department = 'Sales' THEN salary * 1.10
          WHEN department = 'HR' THEN salary * 1.05
          ELSE salary
       END,
       bonus = CASE
          WHEN performance_rating >= 4.5 THEN salary * 0.20
          WHEN performance_rating >= 3.5 THEN salary * 0.10
          ELSE salary * 0.05
       END
    WHERE hire_date <= DATE_SUB(NOW(), INTERVAL 1 YEAR);
 </syntaxhighlight>
<br>
推奨事項を以下に示す。<br>
<br>
* 常にELSE句を含める
*: 予期しないケースやNULL値に対応する。
* 最も可能性の高い条件を最初に記述
*: クエリパフォーマンスを向上させる。
* CASE文が複雑になりすぎる場合
*: プログラムロジックで処理を検討する。
* CASE式内の条件は相互排他的に設計
*: 条件が重複しないようにする。
<br><br>

== 外部キー制約と更新 ==
外部キー制約が設定されている場合、更新時の動作を制御するアクションを指定できる。<br>
<br>
==== ON UPDATE CASCADE ====
親レコード更新時に、子レコードも自動的に更新される。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 外部キー制約の定義
 CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
 ) ENGINE=INNODB;
 
 CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
       REFERENCES parent(id)
       ON UPDATE CASCADE
 ) ENGINE=INNODB;
 
 # 親レコードを更新すると、子レコードも自動更新される
 UPDATE parent SET id = 100 WHERE id = 1;
 
 # 結果:
 # child テーブルの parent_id が 1 の全ての行が、自動的に parent_id = 100 に更新される
 </syntaxhighlight>
<br>
==== ON UPDATE SET NULL ====
親レコード更新時に、子レコードの外部キー列が <code>NULL</code> に設定される。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 外部キー制約の定義
 CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
       REFERENCES parent(id)
       ON UPDATE SET NULL
 ) ENGINE=INNODB;
 
 # 親レコードを更新すると、子レコードの外部キーがNULLになる
 UPDATE parent SET id = 100 WHERE id = 1;
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>外部キー列がNOT NULL制約を持つ場合、SET NULLは使用できない。</u><br>
<br>
* 正しい定義を以下に示す。
*: <syntaxhighlight lang="mysql">
 parent_id INT,
 </syntaxhighlight>
*: <br>
* 誤った定義 (エラーが発生)
*: <syntaxhighlight lang="mysql">
 parent_id INT NOT NULL,
 </syntaxhighlight>
<br>
==== ON UPDATE RESTRICT / NO ACTION ====
親テーブルの更新操作を拒否する。<br>
子テーブルに一致するレコードが存在する場合、エラーが発生する。<br>
<br>
 <syntaxhighlight lang="mysql">
 FOREIGN KEY (parent_id)
    REFERENCES parent(id)
    ON UPDATE RESTRICT
 </syntaxhighlight>
<br>
デフォルト動作として、ON UPDATEが指定されない場合、デフォルトは NO ACTION (RESTRICT相当) である。<br>
<br>
==== 外部キー制約の一時的な無効化 ====
データの一括更新等で外部キーチェックを一時的に無効化する場合、以下に示すコマンドを使用する。<br>
<br>
外部キーチェックを無効にすると、データの整合性が失われる可能性があるため、使用には十分注意が必要である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 外部キーチェックを無効化
 SET FOREIGN_KEY_CHECKS = 0;
 
 # 更新操作を実行
 UPDATE parent_table SET column = value WHERE condition;
 UPDATE child_table SET column = value WHERE condition;
 
 # 外部キーチェックを再度有効化
 SET FOREIGN_KEY_CHECKS = 1;
 </syntaxhighlight>
<br><br>

== セーフモード ==
MySQLのセーフモード (sql_safe_updates) は、WHERE句なし または キー列を使用しないUPDATE文を防止する。<br>
<br>
セーフモードが有効な場合、以下のような更新はエラー (エラーコード1175) になる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # WHERE句なしの更新 (エラーになる)
 UPDATE products SET price = 100;
 
 # キー列を使用しない更新 (エラーになる)
 UPDATE products SET price = 100 WHERE category = 'electronics';
 </syntaxhighlight>
<br>
セーフモードの設定を変更する方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # セーフモードを無効化
 SET SQL_SAFE_UPDATES = 0;
 
 # 更新を実行
 UPDATE products SET price = 100 WHERE category = 'electronics';
 
 # セーフモードを再度有効化
 SET SQL_SAFE_UPDATES = 1;
 </syntaxhighlight>
<br>
成功する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # PRIMARY KEYのidカラムを使用
 UPDATE products SET price = 100 WHERE id = 5;
 
 # LIMIT句を使用
 UPDATE products SET price = 100 LIMIT 10;
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>セーフモードは、意図しないデータ更新を防ぐための重要な機能である。</u><br>
<u>一時的に無効化する場合は、操作後に必ず再度有効化することが推奨される。</u><br>
<br><br>

== 更新結果の確認 ==
UPDATE文の実行結果は、影響を受けた行数として表示される。<br>
<br>
 <syntaxhighlight lang="mysql">
 UPDATE products SET price = 100 WHERE category_id = 5;
 </syntaxhighlight>
<br>
 # 出力例：
 
 Query OK, 15 rows affected (0.01 sec)
<br>
<code>ROW_COUNT</code> 関数を使用して、直前のUPDATE文で更新された行数を取得できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 UPDATE employees SET salary = salary * 1.05 WHERE department = 'Sales';
 SELECT ROW_COUNT();
 </syntaxhighlight>
<br>
 # 出力例
 
 # +--------------+
 # | ROW_COUNT()  |
 # +--------------+
 # |           15 |
 # +--------------+
<br>
<u>※注意</u><br>
* <u>ROW_COUNT関数は、直後のSELECT文で実行する必要がある。</u>
*: 他のSQL文を実行すると、値がリセットされる。
* <u>UPDATEでは、新しい値が古い値と同じ場合、その行は影響を受けたものとしてカウントされない。</u>
*: 実際に変更された行数のみがカウントされる。
* <u>CLIENT_FOUND_ROWSフラグを使用すると、マッチした行数を返す。</u>
*: アプリケーションレベルで接続フラグを設定する必要がある。
<br><br>

== パフォーマンス最適化 ==
==== インデックスの活用 ====
UPDATE文のパフォーマンスを向上させるために、WHERE句で使用する列にインデックスを作成する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # インデックスが存在しない場合、全行スキャンが発生する
 UPDATE products SET price = 100 WHERE category_id = 5;
 
 # インデックスを作成
 CREATE INDEX idx_category_id ON products(category_id);
 
 # インデックスを使用した高速更新
 UPDATE products SET price = 100 WHERE category_id = 5;
 </syntaxhighlight>
<br>
<code>EXPLAIN</code> 文を使用して、UPDATE文の実行計画を確認できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 実行計画を確認
 
 EXPLAIN UPDATE products SET price = 100 WHERE category_id = 5;
 </syntaxhighlight>
<br>
インデックスの効果と注意点を以下に示す。<br>
<br>
正の効果として、以下が挙げられる。<br>
* WHERE句で指定されるカラムがインデックスされていると、必要な行をすぐに特定できる。
*: 更新対象の行が限定されている場合、大幅にパフォーマンスを向上させる。
<br>
負の効果として、以下が挙げられる。<br>
* 更新対象のカラムがインデックス付きの場合、そのインデックスも更新される必要がある。
*: インデックスが多いと、UPDATE操作のオーバーヘッドが増加する。
* 不要なインデックスは削除することで、パフォーマンス向上を期待できる。
<br>
==== 大量データの更新 ====
大量のデータを1度に更新すると、以下に示す問題が発生する可能性がある。<br>
<br>
* テーブルロックによる他のクエリの待機
* トランザクションログの肥大化
* 長時間のロック保持によるデッドロック
* メモリとCPUリソースの大量消費
<br>
推奨される大量データ更新の方法を以下に示す。<br>
<br>
===== バッチ更新 (チャンク更新) =====
<code>LIMIT</code> 句を使用して、少しずつ更新を行う。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 1000件ずつ更新を繰り返す
 UPDATE large_table
    SET status = 'processed'
    WHERE status = 'pending'
    ORDER BY id 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 \
       "UPDATE orders SET status = 'processed' \
        WHERE status = 'pending' LIMIT 1000; \
        SELECT ROW_COUNT();")
 
    echo "Updated $affected rows"

    if [ "$affected" -eq 0 ]; then
       break
    fi
 
    sleep 1
 done
 </syntaxhighlight>
<br>
バッチ更新のメリットを以下に示す。<br>
* ロック期間を短縮できるため、他のトランザクションへの影響を最小化
* メモリ使用量を削減
* 処理の進捗を監視しやすい
<br>
===== トランザクションの制御 =====
大量更新時は、トランザクションを短く保つことが重要である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 自動コミットを有効にする (各UPDATE文が自動的にコミットされる)
 SET AUTOCOMMIT = 1;
 
 # バッチ更新を実行
 UPDATE large_table SET status = 'processed' WHERE id <= 1000;
 </syntaxhighlight>
<br>
===== 更新後の最適化 =====
大量のデータを更新した後は、<code>OPTIMIZE TABLE</code> 文でテーブルを最適化する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # テーブルの最適化 (断片化の解消、領域の再利用)
 
 OPTIMIZE TABLE products;
 </syntaxhighlight>
<br><br>

== UPDATEと他の更新系の違い ==
==== REPLACE文との違い ====
REPLACE文は、既存の行を削除してから新しい行を挿入する。<br>
<br>
 <syntaxhighlight lang="mysql">
 REPLACE INTO products (id, name, price)
    VALUES (1, 'New Product', 100);
 </syntaxhighlight>
<br>
REPLACE文とUPDATE文の比較を下表に示す。<br>
<br>
<center>
{| class="wikitable"
|+ REPLACE文とUPDATE文の比較
! 項目 !! REPLACE !! UPDATE
|-
| 操作内容 || 削除 → 挿入 || 現地修正
|-
| 自動増分 || 新しいIDを割り当て || 変更なし
|-
| 外部キー || カスケード削除が発生 || 対象行のみ修正
|-
| インデックス || 再構築される || 既存のまま
|-
| パフォーマンス || 遅い (削除+挿入) || 速い (現地修正)
|}
</center>
<br>
REPLACE使用時の注意点を以下に示す。<br>
* 指定しないカラムはデフォルト値に設定される
*: デフォルト値がない場合はエラーになる。
* 外部キー制約によるカスケード削除が発生する可能性
*: ON DELETE CASCADEが設定されている場合、子レコードも削除される。
* トリガーが複数回呼び出される
*: 削除と挿入それぞれでトリガーが実行される。
<br>
==== INSERT ON DUPLICATE KEY UPDATEとの違い ====
INSERT ON DUPLICATE KEY UPDATE は、UNIQUEキーまたはPRIMARY KEYで重複が発生した場合、UPDATEを実行する。<br>
<br>
 <syntaxhighlight lang="mysql">
 INSERT INTO products (id, name, price)
    VALUES (1, 'Product', 100)
    ON DUPLICATE KEY UPDATE
       price = VALUES(price),
       updated_at = NOW();
 </syntaxhighlight>
<br>
3つの更新方法の比較を下表に示す。<br>
<br>
<center>
{| class="wikitable"
|+ 3つの更新方法の比較
! 項目 !! UPDATE !! REPLACE !! INSERT ON DUPLICATE KEY UPDATE
|-
| 行が存在しない場合 || 何もしない || 挿入 || 挿入
|-
| 行が存在する場合 || 更新 || 削除 → 挿入 || 更新
|-
| 自動増分の扱い || 変更なし || 新IDを割り当て || INSERTパスで新ID、UPDATE時は変更なし
|-
| パフォーマンス || 速い || 遅い || 速い
|-
| トリガー発火 || 1回 (UPDATE) || 2回 (DELETE、INSERT) || 1から2回
|-
| 外部キー影響 || 最小限 || カスケード削除可能 || 最小限
|}
</center>
<br>
各方法の使い分けを以下に示す。<br>
<br>
* UPDATE
*: 既存行を確実に更新したい場合に使用する。
* REPLACE
*: 古いデータを完全に削除して置き換えたい場合に使用する (非推奨、INSERT ON DUPLICATE KEY UPDATE推奨)。
* INSERT ON DUPLICATE KEY UPDATE
*: 存在しなければ挿入、存在すれば更新 (upsert操作) に使用する。
<br>
実装例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # ユーザーが初めてログインしたら行を挿入、
 # 2回目以降はログイン情報を更新
 INSERT INTO user_profiles (user_id, last_login, login_count)
    VALUES (123, NOW(), 1)
    ON DUPLICATE KEY UPDATE
       last_login = NOW(),
       login_count = login_count + 1;
 </syntaxhighlight>
<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]]