SQL UPDATE操作中に古い列の値を取得するにはどうすればよいですか?

Pure SQL を使用した UPDATE 中の古い列値の取得

概要

データベース内の行を更新する場合、元の列値にアクセスすると便利です更新された列の値。 SQL には更新後の値を取得するための RETURNING 句が用意されていますが、古い列の値を直接フェッチすることはサポートされていません。この記事では、UPDATE 操作中に新しい列の値と古い列の値の両方を取得する回避策について説明します。

自己結合を使用した解決策

RETURNING 句の制限にもかかわらず、元の列にアクセスすることは可能です。テーブルをそれ自体に結合することで値を取得します。この手法により、トリガーやストアド プロシージャが不要になります。

UPDATE my_table
SET processing_by = our_id_info  -- unique to this worker
FROM my_table AS old_my_table
WHERE trans_nbr IN (
  SELECT trans_nbr
  FROM my_table
  GROUP BY trans_nbr
  HAVING COUNT(*) > 1
  LIMIT our_limit_to_have_single_process_grab
)
AND my_table.row_id = old_my_table.row_id
RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by AS old_processing_by;

このクエリは次の手順を実行します。

1. サブクエリを使用して、更新する行をフィルタリングして識別します。
2. エイリアス (old_my_table) を使用してテーブルをそれ自体に結合し、元の列にアクセスします
3. processing_by を our_id_info に設定し、一致する行 ID でのみ結合します。
4. 行 ID、新しい process_by 値、および古い process_by 値を返します。

回避同時書き込みの問題

自己結合ソリューションは同時書き込みなしでも正常に機能しますが、ロード時に複数のワーカーが同じ行を更新しようとすると、同時実行の問題が発生します。これに対処するには:

Serializable Isolation Level

SERIALIZABLE 分離レベルでトランザクションをシリアル化すると、操作中に他のトランザクションがデータにアクセスできなくなります。ただし、これはパフォーマンスに影響を与える可能性があります。

明示的ロック

FOR UPDATE 句を使用して更新する特定の行をロックすると、操作が完了するまで他のトランザクションがその行を変更できなくなります。これにより、パフォーマンスを維持しながらデータの整合性が確保されます。

UPDATE tbl x
SET tbl_id = 23
, name = 'New Guy'
FROM (SELECT tbl_id, name FROM tbl WHERE tbl_id = 3 FOR UPDATE) y
WHERE x.tbl_id = y.tbl_id
RETURNING y.tbl_id AS old_id, y.name AS old_name
, x.tbl_id, x.name;

結論

自己結合を使用し、同時実行制御メカニズムを適用することで、UPDATE 中に古い列の値と新しい列の値の両方を取得することができます。純粋な SQL を使用した操作。このアプローチにより、データベース システムでのデータ変更の処理に柔軟性と制御が提供されます。

以上がSQL UPDATE操作中に古い列の値を取得するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。