选择受更新影响的行 [英] select the rows affected by an update

问题描述

如何获取MySQL中受SQL UPDATE语句影响的确切行?

How can you get the exact rows affected by an SQL UPDATE statement in MySQL?

我在许多计算机上有许多客户端，这些客户端可以随时基于WHERE子句更新同一表中的行，并且每个客户端都需要在另一个系统中为它们影响的每一行做一些事情，因此获取列表受影响的物品必须是准确的，并且不容易受到种族条件的影响.

I have many clients on many computers that can be updating rows in the same table based on WHERE clauses at any time, and each client needs to do something in another system for each row that they affect, so getting the list of affected items must be accurate and not vulnerable to race-conditions.

某些数据库支持UPDATE ... OUTPUT UPDATED.id WHERE ...，例如 SQL Server .

Some databases support UPDATE ... OUTPUT UPDATED.id WHERE ... e.g. SQL Server.

如何在MySQL中执行此原子UPDATE/SELECT?

How can you do this atomic UPDATE/SELECT in MySQL?

(我已经看到了建议先做SELECT然后在UPDATE中使用ID作为IN子句的建议.但是另一个客户端可能会运行相同的SELECT并检索相同的行，而第一个客户端正在排队其UPDATE等?)

(I've seen suggestions of doing the SELECT first and then using the IDs as an IN clause in the UPDATE. But another client might run the same SELECT and retrieve the same rows whilst the first client is queuing its UPDATE etc?)

推荐答案

使用表锁定或事务(如果存储引擎支持)以防止出现竞争情况.

Use table locking or transactions (if supported by storage engine) to prevent race conditions.

LOCK TABLES tablename;
SELECT * FROM tablename WHERE x.
do something else
UPDATE tablename SET y WHERE x.
UNLOCK TABLES

这篇关于选择受更新影响的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！