如何在 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中文网其他相关文章！