如何在 SQL UPDATE 作業期間檢索舊列值？

使用純SQL 在更新期間檢索舊列值

簡介

更新資料庫中的行時，存取原始資料可能很有用更新列的值。雖然 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、新的processing_by 值和舊的processing_by 值。

避免並發寫入問題

雖然自連接解決方案在沒有並發寫入的情況下效果很好加載時，如果多個工作線程嘗試更新相同的行，它會引入並發問題。要解決此問題：

可序列化隔離等級

使用可序列化隔離等級序列化交易可確保在操作期間沒有其他交易可以存取資料。但是，這可能會影響效能。

明確鎖定

使用 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中文網其他相關文章！